* [pmg-devel] [PATCH pmg-api 2/2] postgresql compat: cast result from EXTRACT to INTEGER
2023-06-26 20:45 [pmg-devel] [PATCH pmg-api 0/2] cluster changes for bookworm Stoiko Ivanov
2023-06-26 20:45 ` [pmg-devel] [PATCH pmg-api 1/2] cluster: fix rsync invocation Stoiko Ivanov
@ 2023-06-26 20:45 ` Stoiko Ivanov
2023-06-27 8:29 ` [pmg-devel] applied: [PATCH pmg-api 0/2] cluster changes for bookworm Dominik Csapak
2 siblings, 0 replies; 4+ messages in thread
From: Stoiko Ivanov @ 2023-06-26 20:45 UTC (permalink / raw)
To: pmg-devel
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
^ permalink raw reply [flat|nested] 4+ messages in thread