[pmg-devel] [PATCH pmg-api 0/2] cluster changes for bookworm

[pmg-devel] [PATCH pmg-api 0/2] cluster changes for bookworm

[Stoiko Ivanov]

with the initial tests for the upgrade of the pmg packages to bookworm I
noticed 2 small issues when running PMG in a cluster.

the first patch addresses the same issue we had in pve-cluster with the
deprecated rsync syntax.

the second patch addresses a change in postgresql's response to
```
EXTRACT(EPOCH FROM now())
```

with both patches applied a cluster setup with one being upgraded from
bullseye (with a restored backup from another bullseye system) and one
installed directly from a test ISO remain with both nodes in active state.

Stoiko Ivanov (2):
  cluster: fix rsync invocation
  postgresql compat: cast result from EXTRACT to INTEGER

 src/PMG/Cluster.pm    |  4 +++-
 src/PMG/DBTools.pm    |  4 ++--
 src/PMG/Quarantine.pm |  4 ++--
 src/PMG/Statistic.pm  | 16 ++++++++--------
 src/bin/pmgpolicy     |  2 +-
 5 files changed, 16 insertions(+), 14 deletions(-)

-- 
2.30.2

[pmg-devel] [PATCH pmg-api 1/2] cluster: fix rsync invocation

[Stoiko Ivanov]

Since rsync 3.2.4, the syntax to give multiple files in one parameter
does not work anymore, so list them explicitly

Inspired by  commit 9697997575e25e188a0993a0e4fc7f33f6602928
in pve-cluster

Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
---
 src/PMG/Cluster.pm | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

diff --git a/src/PMG/Cluster.pm b/src/PMG/Cluster.pm
index 7622a88..e9a6054 100644
--- a/src/PMG/Cluster.pm
+++ b/src/PMG/Cluster.pm
@@ -418,7 +418,9 @@ sub sync_config_from_master {
 
     my $cmd = $rsync_command->(
 	$master_name, '-aq',
-	"[${master_ip}]:$cfgdir/* ${sa_conf_dir}/${sa_custom_cf} ${sa_conf_dir}/${sa_rules_cf}",
+	"[${master_ip}]:$cfgdir/*",
+	"[${master_ip}]:${sa_conf_dir}/${sa_custom_cf}",
+	"[${master_ip}]:${sa_conf_dir}/${sa_rules_cf}",
 	"$syncdir/",
 	'--exclude', 'master/',
 	'--exclude', '*~',
-- 
2.30.2

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

[Stoiko Ivanov]

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

[pmg-devel] applied: [PATCH pmg-api 0/2] cluster changes for bookworm

[Dominik Csapak]

applied series, with the fixup squashed in like we talked off-list
thanks

On 6/26/23 22:45, Stoiko Ivanov wrote:
> with the initial tests for the upgrade of the pmg packages to bookworm I
> noticed 2 small issues when running PMG in a cluster.
> 
> the first patch addresses the same issue we had in pve-cluster with the
> deprecated rsync syntax.
> 
> the second patch addresses a change in postgresql's response to
> ```
> EXTRACT(EPOCH FROM now())
> ```
> 
> with both patches applied a cluster setup with one being upgraded from
> bullseye (with a restored backup from another bullseye system) and one
> installed directly from a test ISO remain with both nodes in active state.
> 
> Stoiko Ivanov (2):
>    cluster: fix rsync invocation
>    postgresql compat: cast result from EXTRACT to INTEGER
> 
>   src/PMG/Cluster.pm    |  4 +++-
>   src/PMG/DBTools.pm    |  4 ++--
>   src/PMG/Quarantine.pm |  4 ++--
>   src/PMG/Statistic.pm  | 16 ++++++++--------
>   src/bin/pmgpolicy     |  2 +-
>   5 files changed, 16 insertions(+), 14 deletions(-)
>