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 8/22/25 09:29, Laurenz Albe wrote:
> On Fri, 2025-08-22 at 09:16 +0100, Achilleas Mantzios wrote:
>> we had the same problem today again.
>>
>> postgres(at)[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(at)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