public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios <[email protected]>
To: Laurenz Albe <[email protected]>
To: [email protected]
Subject: Re: The same again with 16.9 : was Re: PostgreSQL 16.6 , query stuck with STAT Ssl, wait_event_type : IPC , wait_event : ParallelFinish
Date: Fri, 22 Aug 2025 09:54:51 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>


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@[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

Attachments:

  [application/x-perl] AsyncMirrorWithSpecialTablesFastIIEsc.pl (27.8K, 3-AsyncMirrorWithSpecialTablesFastIIEsc.pl)
  download

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: The same again with 16.9 : was Re: PostgreSQL 16.6 , query stuck with STAT Ssl, wait_event_type : IPC , wait_event : ParallelFinish
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox