Thread: BUG #15305: PREPARE TRANSACTION andpg_create_logical_replication_slot()

The following bug has been logged on the website:

Bug reference:      15305
Logged by:          Toshi Harada
Email address:      harada.toshi@po.ntt-tx.co.jp
PostgreSQL version: 10.4
Operating system:   CentOS Linux release 7.4.1708 (Core)
Description:

Execution of pg_create_logical_replication_slot() is blocked after execution
of PREPARE TRANSACTION.

Steps to Reproduce

1.Execute the PREPARE TRANSACTION command to put it in the state before
two-phase commit.

pgbench_db=# PREPARE TRANSACTION 'foo';
PREPARE TRANSACTION
pgbench_db=#

2. Execution of the pg_create_logical_replication_slot () function in the
state before two-phase commit is blocked.

pgbench_db=# SELECT pg_create_logical_replication_slot('my_slot',
'test_decoding');

3. When the two-phase commit state is released by executing the ROLLBACK
PREPARE command from another terminal, pg_create_logical_replication_slot ()
is executed and a logical slot is generated.

 pg_create_logical_replication_slot
------------------------------------
 (my_slot,4/60CAA460)
(1 row)


[Question]
Is it specifications that are blocked when trying to create logical slots in
the state before two-phase commit?
If that is a spec, it is better to add notes to the PostgreSQL document.

Hi,

On 2018-07-31 00:26:39 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15305
> Logged by:          Toshi Harada
> Email address:      harada.toshi@po.ntt-tx.co.jp
> PostgreSQL version: 10.4
> Operating system:   CentOS Linux release 7.4.1708 (Core)
> Description:        
> 
> Execution of pg_create_logical_replication_slot() is blocked after execution
> of PREPARE TRANSACTION.
> 
> Steps to Reproduce
> 
> 1.Execute the PREPARE TRANSACTION command to put it in the state before
> two-phase commit.
> 
> pgbench_db=# PREPARE TRANSACTION 'foo';
> PREPARE TRANSACTION
> pgbench_db=#
> 
> 2. Execution of the pg_create_logical_replication_slot () function in the
> state before two-phase commit is blocked.
> 
> pgbench_db=# SELECT pg_create_logical_replication_slot('my_slot',
> 'test_decoding');
> 
> 3. When the two-phase commit state is released by executing the ROLLBACK
> PREPARE command from another terminal, pg_create_logical_replication_slot ()
> is executed and a logical slot is generated.
> 
>  pg_create_logical_replication_slot
> ------------------------------------
>  (my_slot,4/60CAA460)
> (1 row)
> 
> 
> [Question]
> Is it specifications that are blocked when trying to create logical slots in
> the state before two-phase commit?

Yes, that's expected. It has to wait for old transactions to
finish. There's otherwise no guarantee that it could decode them
completely (WAL might be gone, xmin horizon not old enough etc) once the
slot is created. And the expected situation is that all transactions
after the slot is created can be replayed.  And that's the same for 2PC
as it is for "plain" transactions.

Makes sense?


> If that is a spec, it is better to add notes to the PostgreSQL document.

Hm. Care to propose a doc patch? Note that I don't think it makes sense
to explicitly refer to 2pc here, as the issue is more general.

Greetings,

Andres Freund

Hi.

Thanks for your answer.

I understood.
Not limited to 2PC, it is blocked even when long transaction remains.
We close this bug report.

Andres Freund <andres@anarazel.de> wrote:
> Hi,
> 
> On 2018-07-31 00:26:39 +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> > 
> > Bug reference:      15305
> > Logged by:          Toshi Harada
> > Email address:      harada.toshi@po.ntt-tx.co.jp
> > PostgreSQL version: 10.4
> > Operating system:   CentOS Linux release 7.4.1708 (Core)
> > Description:        
> > 
> > Execution of pg_create_logical_replication_slot() is blocked after execution
> > of PREPARE TRANSACTION.
> > 
> > Steps to Reproduce
> > 
> > 1.Execute the PREPARE TRANSACTION command to put it in the state before
> > two-phase commit.
> > 
> > pgbench_db=# PREPARE TRANSACTION 'foo';
> > PREPARE TRANSACTION
> > pgbench_db=#
> > 
> > 2. Execution of the pg_create_logical_replication_slot () function in the
> > state before two-phase commit is blocked.
> > 
> > pgbench_db=# SELECT pg_create_logical_replication_slot('my_slot',
> > 'test_decoding');
> > 
> > 3. When the two-phase commit state is released by executing the ROLLBACK
> > PREPARE command from another terminal, pg_create_logical_replication_slot ()
> > is executed and a logical slot is generated.
> > 
> >  pg_create_logical_replication_slot
> > ------------------------------------
> >  (my_slot,4/60CAA460)
> > (1 row)
> > 
> > 
> > [Question]
> > Is it specifications that are blocked when trying to create logical slots in
> > the state before two-phase commit?
> 
> Yes, that's expected. It has to wait for old transactions to
> finish. There's otherwise no guarantee that it could decode them
> completely (WAL might be gone, xmin horizon not old enough etc) once the
> slot is created. And the expected situation is that all transactions
> after the slot is created can be replayed.  And that's the same for 2PC
> as it is for "plain" transactions.
> 
> Makes sense?
> 
> 
> > If that is a spec, it is better to add notes to the PostgreSQL document.
> 
> Hm. Care to propose a doc patch? Note that I don't think it makes sense
> to explicitly refer to 2pc here, as the issue is more general.
> 
> Greetings,
> 
> Andres Freund