From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from firstgate.proxmox.com (firstgate.proxmox.com [212.224.123.68]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by lists.proxmox.com (Postfix) with ESMTPS id E2A019D6D for ; Mon, 26 Jun 2023 22:45:15 +0200 (CEST) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id BFE292D6EA for ; Mon, 26 Jun 2023 22:45:15 +0200 (CEST) Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com [94.136.29.106]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS for ; Mon, 26 Jun 2023 22:45:14 +0200 (CEST) Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1]) by proxmox-new.maurer-it.com (Proxmox) with ESMTP id DB60A44B92 for ; Mon, 26 Jun 2023 22:45:13 +0200 (CEST) From: Stoiko Ivanov To: pmg-devel@lists.proxmox.com Date: Mon, 26 Jun 2023 22:45:10 +0200 Message-Id: <20230626204510.34783-3-s.ivanov@proxmox.com> X-Mailer: git-send-email 2.30.2 In-Reply-To: <20230626204510.34783-1-s.ivanov@proxmox.com> References: <20230626204510.34783-1-s.ivanov@proxmox.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.097 Adjusted score from AWL reputation of From: address BAYES_00 -1.9 Bayes spam probability is 0 to 1% DMARC_MISSING 0.1 Missing DMARC policy KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict Alignment SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record T_SCC_BODY_TEXT_LINE -0.01 - Subject: [pmg-devel] [PATCH pmg-api 2/2] postgresql compat: cast result from EXTRACT to INTEGER X-BeenThere: pmg-devel@lists.proxmox.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Proxmox Mail Gateway development discussion List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 26 Jun 2023 20:45:15 -0000 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 --- 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