Re: 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