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?
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
