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(at)gmail(dot)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