Changeset 103

Timestamp:

09/24/25 13:00:12 (38 hours ago)

Author:

Kris Deugau

Message:

/trunk/dnsbl

Refine DB transaction behaviour around addblock() based on glitches found
using it in earnest in updated production.

File:

• trunk/dnsbl/DNSBL.pm (modified) (5 diffs)

trunk/dnsbl/DNSBL.pm

@@ -188 +188 @@
 # JIC someone wants to close the db but not finish the script
 sub dbclose {
-  $dbh->rollback;
   $dbh->disconnect;
 }
@@ -196 +195 @@
 sub connect {
   my $self = shift;
-  ## want to NOT autocommit everything, it's unlikely we'll step on our own toes but...
+  # after jumping a HUUUGE number of PG versions, AutoCommit => 0 produced some bizarre bugs
   $dbh = DBI->connect("DBI:Pg:host=$self->{dbhost};dbname=$self->{dbname}", $self->{dbuser}, $self->{dbpass}, {
-        AutoCommit => 0,
-        PrintError => 1
+        AutoCommit => 1,
+        PrintError => 0
         })
         or die "database inaccessible: ".$DBI::errstr;
@@ -250 +249 @@
   my $sth;
   my $rows = 0;
+
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
+
   if ($rep =~ /^[\d.]+$/) {
     # weesa gonna ASS-U-ME IP addresses are sanely formatted.
@@ -363 +366 @@
   return "$blockin not a single CIDR range" if !$block;
 
-#  local $dbh->{AutoCommit} = 1;        # force autocommit
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
 
   my $sth;
@@ -373 +377 @@
       ($parent) = $sth->fetchrow_array;
     }
-    $sth = $dbh->prepare("INSERT INTO blocks (block,orgid,level,parent,exclude,comments,ipcount) VALUES (?,?,?,?,?,?,".
-        "(SELECT count(*) FROM iplist i JOIN blocks b ON b.block=i.parent WHERE i.ip << ? AND i.exclude='n' AND b.exclude='n'))");
-    $sth->execute("$block",$orgid,$level,$parent,$exclude,$comment,"$block");
-    $sth = $dbh->prepare("UPDATE iplist SET parent=? WHERE parent=? AND ip << ?");
-    $sth->execute("$block",$parent,"$block");
+    $dbh->do("INSERT INTO blocks (block,orgid,level,parent,exclude,comments,ipcount) VALUES (?,?,?,?,?,?,".
+        "(SELECT count(*) FROM iplist i JOIN blocks b ON b.block=i.parent WHERE i.ip << ? AND i.exclude='n' AND b.exclude='n'))",
+        undef, "$block",$orgid,$level,$parent,$exclude,$comment,"$block");
+    $dbh->do("UPDATE iplist SET parent=? WHERE parent=? AND ip << ?", undef, "$block", $parent, "$block");
     $dbh->commit;
   };