Re: Transactional behavior of pg_create_logical_replication_slot

Hi,

On 2025-06-17 10:30:34 +0200, Mats Kindahl wrote:
> Hi PostgreSQL Hackers,
> 
> If you try to create a logical replication slot inside a transaction where
> you have issued writes, it will abort with an error message:
> 
> mats=# start transaction;
> START TRANSACTION
> mats=*# select txid_current();
> txid_current
> --------------
>       406252
> (1 row)
> 
> mats=*# insert into foo values (txid_current());
> INSERT 0 1
> mats=*# select txid_current();
> txid_current
> --------------
>       406252
> (1 row)
> 
> mats=*# select * from pg_create_logical_replication_slot('my_slot',
> 'test_decoding', false, true);
> ERROR:  cannot create logical replication slot in transaction that has
> performed writes
> mats=!# rollback;
> ROLLBACK
> 
> 
> I'm slightly unsure why, but I assume that it is because if the transaction
> is rolled back, it will have the XID of a transaction that does not "exist"
> (quotes because the XID is still assigned and incremented, even if the
> transaction is rolled back).

Logical slot creation basically waits for concurrent transactions to end, that
wouldn't work if itself had a transaction open (it'd be a self deadlock).


> If that is the case, then having the call first in the transaction will
> still work fine, since no writes are done yet, but if that transaction is
> rolled back, the slot will point to a "non-existing" XID similar to the
> first case.

That's not the / a  problem.

Greetings,

Andres Freund