Re: The same again with 16.9 : was Re: PostgreSQL 16.6 , query stuck with STAT Ssl, wait_event_type : IPC , wait_event : ParallelFinish

On Fri, 2025-08-22 at 09:16 +0100, Achilleas Mantzios wrote:

we had the same problem today again.

postgres@[local]/dynacom=# select * from pg_stat_activity where application_name~*'dbmirr'; 
 -[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------
[...]
 pid              | 1821681 
[...]
 wait_event_type  | IPC 
 wait_event       | ParallelFinish 
 state            | active 

postgres@smadb:~$ ps -u -p 1821681 
 USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND 
 postgres 1821681  0.5  4.8 37111844 3177260 ?    Ssl  03:58   2:25 postgres: postgres dynacom 10.9.0.10(45051) SELECT 

Did you check whether the Perl function you mentioned before starts threads,
like Tom suggested?  That would be the probable cause, and the solution is
not to start any threads in a PostgreSQL function.

Hi not att all , here is the code :

Main();

sub Main() {
 my $batchTxMode = 0;
  
#run the configuration file.
 #if ($#ARGV != 0) {
 if ($#ARGV < 0) {
   die "usage: DBMirror.pl configFile\n";
 }
 elsif ($#ARGV == 1) {
   if ($ARGV[1] eq "batch") { ##run scp at the end of dbmirror.pl main loop
     $batchTxMode = 1;
   }
   elsif ($ARGV[1] eq "batchall") { ##run scp at the end of dbmirror.sh wrapper script
     $batchTxMode = 2;
   }
   else {
     die "usage: DBMirror.pl configFile [batch|batchall]\n";
   }
 }
 if( ! defined do $ARGV[0]) {
   logErrorMessage("Invalid Configuration file $ARGV[0]");
   die;
 }

 
 #my $connectString = "host=$::masterHost dbname=$::masterDb user=$::masterUser password=$::masterPassword";
 my $connectString = "host=localhost port=6432 dbname=$::masterDb user=$::masterUser password=$::masterPassword";
  
 $masterConn = Pg::connectdb($connectString);
  
 unless($masterConn->status == PGRES_CONNECTION_OK) {
   logErrorMessage("Can't connect to master database\n" .
                   $masterConn->errorMessage);
   die;
 }
    
 my $setQuery;
 $setQuery = "SET search_path = public; SET application_name = 'DBMIRROR'";
 my $setResult = $masterConn->exec($setQuery);
 if($setResult->resultStatus!=PGRES_COMMAND_OK) {  
   logErrorMessage($masterConn->errorMessage . "\n" .  
                   $setQuery);
   die;
 }
    
 my $setQuery2;
 $setQuery2 = "BEGIN";
 my $setResult2 = $masterConn->exec($setQuery2);
 if($setResult2->resultStatus!=PGRES_COMMAND_OK) {  
   logErrorMessage($masterConn->errorMessage . "\n" .  
                   $setQuery2);
   die;
 }
    
    
   setupSlave($::slaveInfo);
#print $::slaveInfo->{"uucpnode"} . "\n";
#LOCK CODE!!!!
   my $pendingLockQuery = "SELECT 1 FROM dbmirror_Pending pd";
   $pendingLockQuery .= " LEFT JOIN dbmirror_MirroredTransaction mt INNER JOIN";
   $pendingLockQuery .= " dbmirror_MirrorHost mh ON mt.MirrorHostId = ";
   $pendingLockQuery .= " mh.MirrorHostId AND mh.HostName=";
   $pendingLockQuery .= " '$::slaveInfo->{\"slaveHost\"}' ";  
   $pendingLockQuery .= " ON pd.XID";
   $pendingLockQuery .= " = mt.XID WHERE mt.XID is null and (pd.slaveid is null or pd.slaveid = '$::slaveInfo->{\"MirrorHostId\"}') ";
   $pendingLockQuery .= " FOR UPDATE OF pd ";
    
   my $pendingLockResults = $masterConn->exec($pendingLockQuery);
   unless($pendingLockResults->resultStatus==PGRES_TUPLES_OK) {
     logErrorMessage("Can't query pending table\n" . $masterConn->errorMessage);
     die;
   }
#END LOCK CODE!!!!
   
   #Obtain a list of pending transactions using ordering by our approximation
   #to the commit time.  The commit time approximation is taken to be the
   #SeqId of the last row edit in the transaction.
   my $pendingTransQuery = "SELECT pd.XID,MAX(SeqId) FROM dbmirror_Pending pd";
   $pendingTransQuery .= " LEFT JOIN dbmirror_MirroredTransaction mt INNER JOIN";
   $pendingTransQuery .= " dbmirror_MirrorHost mh ON mt.MirrorHostId = ";
   $pendingTransQuery .= " mh.MirrorHostId AND mh.HostName=";
   $pendingTransQuery .= " '$::slaveInfo->{\"slaveHost\"}' ";  
   $pendingTransQuery .= " ON pd.XID";
   $pendingTransQuery .= " = mt.XID WHERE mt.XID is null and (pd.slaveid is null or pd.slaveid = '$::slaveInfo->{\"MirrorHostId\"}') ";
   $pendingTransQuery .= " GROUP BY pd.XID ";
   $pendingTransQuery .= " ORDER BY MAX(pd.SeqId)";
   
It got stuck inside the second query, after the FOR UPDATE locking  .

I attach the client program, just for completeness. It just queries the three tables :

- dbmirror_pending

- dbmirror_mirroredtransaction

- dbmirror_mirrorhost

first it tries do lock via FOR UPDATE , then queries the tables again.

Yours,
Laurenz Albe