[pmg-devel] [PATCH pmg-api 2/2] postgresql compat: cast result from EXTRACT to INTEGER

[Stoiko Ivanov <s.ivanov@proxmox.com>]

Postgresql has changed the return type of the EXTRACT function to
numeric from float8 [0] in version 14, and I strongly assume that this
change is the reason why:
`SELECT EXTRACT (EPOCH FROM now());`
now returns a floating point instead of an integer value, which in
turn is not accepted in the prepared statements throughout our
codebase.

[0] https://www.postgresql.org/docs/release/14.0/

Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
---
 src/PMG/DBTools.pm    |  4 ++--
 src/PMG/Quarantine.pm |  4 ++--
 src/PMG/Statistic.pm  | 16 ++++++++--------
 src/bin/pmgpolicy     |  2 +-
 4 files changed, 13 insertions(+), 13 deletions(-)

diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm
index a437eb6..9e133bc 100644
--- a/src/PMG/DBTools.pm
+++ b/src/PMG/DBTools.pm
@@ -893,7 +893,7 @@ sub init_ruledb {
 sub get_remote_time {
     my ($rdb) = @_;
 
-    my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
+    my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now')::INTEGER as ctime;");
     $sth->execute();
     my $ctinfo = $sth->fetchrow_hashref();
     $sth->finish ();
@@ -1100,7 +1100,7 @@ sub update_master_clusterinfo {
 
     foreach my $table (@mt) {
 	$dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
-		  "EXTRACT(EPOCH FROM now())");
+		  "EXTRACT(EPOCH FROM now())::INTEGER");
     }
 }
 
diff --git a/src/PMG/Quarantine.pm b/src/PMG/Quarantine.pm
index b80a5a3..d3d0640 100644
--- a/src/PMG/Quarantine.pm
+++ b/src/PMG/Quarantine.pm
@@ -74,12 +74,12 @@ sub add_to_blackwhite {
 	if (scalar(keys %{$list->{WL}})) {
 	    $queries .=
 	    "INSERT INTO UserPrefs (PMail, Name, Data, MTime) " .
-	    "VALUES ($qu, 'WL', $wlist, EXTRACT (EPOCH FROM now()));";
+	    "VALUES ($qu, 'WL', $wlist, EXTRACT (EPOCH FROM now())::INTEGER);";
 	}
 	if (scalar(keys %{$list->{BL}})) {
 	    $queries .=
 	    "INSERT INTO UserPrefs (PMail, Name, Data, MTime) " .
-	    "VALUES ($qu, 'BL', $blist, EXTRACT (EPOCH FROM now()));";
+	    "VALUES ($qu, 'BL', $blist, EXTRACT (EPOCH FROM now())::INTEGER);";
 	}
 	$dbh->do($queries);
     }
diff --git a/src/PMG/Statistic.pm b/src/PMG/Statistic.pm
index 99f96cf..e53a401 100644
--- a/src/PMG/Statistic.pm
+++ b/src/PMG/Statistic.pm
@@ -170,7 +170,7 @@ sub update_stats_dailystat  {
 	push @values, "GreylistCount = GreylistCount + $ref->{glcount}" if $ref->{glcount};
 	push @values, "SPFCount = SPFCount + $ref->{spfcount}" if $ref->{spfcount};
 	push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum};
-	push @values, "MTime = EXTRACT(EPOCH FROM now())";
+	push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER";
 
 	if (scalar (@values)) {
 	    $sql .= "UPDATE dailystat SET ";
@@ -192,7 +192,7 @@ sub update_stats_dailystat  {
 	    ($ref->{spam_in} || 0) . ',' . ($ref->{spam_out} || 0) . ',' .
 	    ($ref->{bounces_in} || 0) . ',' . ($ref->{bounces_out} || 0) . ',' .
 	    ($ref->{glcount} || 0) . ',' . ($ref->{spfcount} || 0) . ',0,' . ($ref->{ptimesum} || 0) .
-	    ",EXTRACT(EPOCH FROM now()));";
+	    ",EXTRACT(EPOCH FROM now())::INTEGER);";
 
 	return $sql;
     };
@@ -236,7 +236,7 @@ sub update_stats_domainstat_in  {
 	push @values, "SpamIn = SpamIn + $ref->{spam_in}" if $ref->{spam_in};
 	push @values, "BouncesIn = BouncesIn + $ref->{bounces_in}" if $ref->{bounces_in};
 	push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum};
-	push @values, "MTime = EXTRACT(EPOCH FROM now())";
+	push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER";
 
 	if (scalar (@values)) {
 	    $sql .= "UPDATE domainstat SET ";
@@ -256,7 +256,7 @@ sub update_stats_domainstat_in  {
 		    ($ref->{spam_in} || 0) . ',0,' .
 		    ($ref->{bounces_in} || 0) . ',0,' .
 		    ($ref->{ptimesum} || 0) .
-		    ",EXTRACT(EPOCH FROM now()));";
+		    ", FROM now())::INTEGER);";
 
 	return $sql;
     };
@@ -294,7 +294,7 @@ sub update_stats_domainstat_out  {
 	push @values, "SpamOut = SpamOut + $ref->{spam_out}" if $ref->{spam_out};
 	push @values, "BouncesOut = BouncesOut + $ref->{bounces_out}" if $ref->{bounces_out};
 	push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum};
-	push @values, "MTime = EXTRACT(EPOCH FROM now())";
+	push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER";
 
 	if (scalar (@values)) {
 	    $sql .= "UPDATE domainstat SET ";
@@ -314,7 +314,7 @@ sub update_stats_domainstat_out  {
 	    ',0,' . ($ref->{spam_out} || 0) .
 	    ',0,' . ($ref->{bounces_out} || 0) .
 	    ','. ($ref->{ptimesum} || 0) .
-	    ",EXTRACT(EPOCH FROM now()));";
+	    ",EXTRACT(EPOCH FROM now())::INTEGER);";
 
 	return $sql;
     };
@@ -342,7 +342,7 @@ sub update_stats_virusinfo  {
 	my $sql = '';
 
 	push @values, "Count = Count + $ref->{count}" if $ref->{count};
-	push @values, "MTime = EXTRACT(EPOCH FROM now())";
+	push @values, "MTime = EXTRACT(EPOCH FROM now()::INTEGER)";
 
 	if (scalar (@values)) {
 	    $sql .= "UPDATE VirusInfo SET ";
@@ -357,7 +357,7 @@ sub update_stats_virusinfo  {
 
 	my $sql .= "INSERT INTO VirusInfo values ($ref->{day}, " .  $dbh->quote($ref->{name}) .
 	    ',' . ($ref->{count} || 0) .
-	    ",EXTRACT(EPOCH FROM now()));";
+	    ",EXTRACT(EPOCH FROM now())::INTEGER);";
 
 	return $sql;
     };
diff --git a/src/bin/pmgpolicy b/src/bin/pmgpolicy
index 2fd2983..f5335de 100755
--- a/src/bin/pmgpolicy
+++ b/src/bin/pmgpolicy
@@ -116,7 +116,7 @@ sub update_rbl_stats {
 
     my $sth = $dbh->prepare(
 	'INSERT INTO LocalStat (Time, RBLCount, PregreetCount, CID, MTime) ' .
-	'VALUES (?, ?, ?, ?, EXTRACT(EPOCH FROM now())) ' .
+	'VALUES (?, ?, ?, ?, EXTRACT(EPOCH FROM now())::INTEGER) ' .
 	'ON CONFLICT (Time, CID) DO UPDATE SET ' .
 	'RBLCount = LocalStat.RBLCount + excluded.RBLCount, ' .
 	'PregreetCount = LocalStat.PregreetCount + excluded.PregreetCount, ' .
-- 
2.30.2