At Wed, 25 May 2022 11:01:43 -0400, Fred Habash 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 1 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 1 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
