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) SELECTDid 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
Attachment:
AsyncMirrorWithSpecialTablesFastIIEsc.pl
Description: Perl program
