From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: pmg-devel@lists.proxmox.com
Subject: [pmg-devel] [PATCH pmg-api 2/2] postgresql compat: cast result from EXTRACT to INTEGER
Date: Mon, 26 Jun 2023 22:45:10 +0200 [thread overview]
Message-ID: <20230626204510.34783-3-s.ivanov@proxmox.com> (raw)
In-Reply-To: <20230626204510.34783-1-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
next prev parent reply other threads:[~2023-06-26 20:45 UTC|newest]
Thread overview: 4+ messages / expand[flat|nested] mbox.gz Atom feed top
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 [this message]
2023-06-27 8:29 ` [pmg-devel] applied: [PATCH pmg-api 0/2] cluster changes for bookworm Dominik Csapak
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=20230626204510.34783-3-s.ivanov@proxmox.com \
--to=s.ivanov@proxmox.com \
--cc=pmg-devel@lists.proxmox.com \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox