pg_create_logical_replication_slot in DB1 is blocked by a session in DB2

At Wed, 25 May 2022 11:01:43 -0400, Fred Habash <fmhabash@gmail.com> wrote in

I'm running this command while connected to pg cluster DB1:

SELECT * FROM pg_create_logical_replication_slot('test_slot_99',
'test_decoding');

When I examine pg_locks, I see the session is waiting on virtualxid and
blocked and blocking sessions are on two different DBs.

After doing some research, it looks like locks across DB can happen in
postgres if the session queries rely on 'shared tables'. Not sure if this
applies here.

How can this be explained?

The "blocked_pid" you showed is of PID=14305 but the reportedly
"blocked" session is of PID=13405. So the 8602 doesn't seem to be
involved the "trouble". You might need to reinspect the situation.

This is the session issuing the create slot command
########################################################
datid|datname |pid |leader_pid|usesysid|usename
|application_name|client_addr |client_hostname|client_port|backend_start
|xact_start |query_start |state_change
|wait_event_type|wait_event|state |backend_xid|backend_xmin|query

|backend_type |
-----|---------|-----|----------|--------|---------|----------------|------------|---------------|-----------|-------------------|-------------------|-------------------|-------------------|---------------|----------|------|-----------|------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|
16408|db1 |13405| | 16394|test99 |
|xx.xxx.xxx.x| | 53398|2022-05-25 09:12:41|2022-05-25
09:12:42|2022-05-25 09:12:42|2022-05-25 09:12:42|Lock
|virtualxid|active| |171577399 |BEGIN;declare
"SQL_CUR0x14680c0bace0" cursor with hold for SELECT lsn FROM
pg_create_logical_replication_slot('qitx6iolfhy5zfkl_00016408_66eb6ba3_1fe1_4ccd_95ed_fd3d2d5d4ad8',
'test_decoding');fetch 10000 in "SQL_CUR0x14680c0bace0"|client backend|

Session above is blocked by pid 8602
########################################################
blocked_pid|blocked_user|blocking_pid|blocking_user |blocked_statement

|current_statement_in_blocking_process

|
-----------|------------|------------|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
14305|pq_devops | 8602|service_con |BEGIN;declare
"SQL_CUR0x1464680d6a60" cursor with hold for SELECT lsn FROM
pg_create_logical_replication_slot('4iipu5a2hnuyfp3u_00016408_036cac77_3854_4320_b329_e7209b4cccf9',
'test_decoding');fetch 10000 in "SQL_CUR0x1464680d6a60"|¶ SELECT ******
|

The blocked and blocking sessions are on two different DBs
########################################################
datid|datname |pid |leader_pid|usesysid|usename |application_name
|cl
-----|---------|-----|----------|--------|---------------|----------------------|--
16408|db1 |13405| | 16394|test99 |
|10
16407|db2 | 8602| |29429933|service_con_9 |PostgreSQL JDBC
Driver|10
----------------------------------------
Thank you

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center