I'm running this command while connected to pg cluster DB1:
When I examine pg_locks, I see the session is waiting on virtualxid and blocked and blocking sessions are on two different DBs.
SELECT * FROM pg_create_logical_replication_slot('test_slot_99', 'test_decoding');
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
Thank you
