pg_create_logical_replication_slot in DB1 is blocked by a session in DB2

From: Fred Habash <fmhabash(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_create_logical_replication_slot in DB1 is blocked by a session in DB2
Date: 2022-05-25 15:01:43
Message-ID: CADpeV5yB_b+UNRL9E9Zt3N2xiuODgh8yJZVvz7o8sRv4wWLxfA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-05-25 15:05:11 Re: Connect to specific cluster on command line
Previous Message Tom Lane 2022-05-25 14:58:15 Re: Connect to specific cluster on command line