Re: Transactional behavior of pg_create_logical_replication_slot

On Tue, Jun 17, 2025 at 1:22 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> 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).
>

Thanks Andres, that explains it.

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

--
Best wishes,
Mats Kindahl, Timescale