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).
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.
mats=# start transaction;
START TRANSACTION
mats=*# select * from pg_create_logical_replication_slot('my_slot',
'test_decoding', false, true);
slot_name | lsn
-----------+------------
my_slot | 0/9F92C840
(1 row)
mats=*# select txid_current();
txid_current
--------------
406253
(1 row)
mats=*# insert into foo values (txid_current());
INSERT 0 1
mats=*# rollback;
ROLLBACK
mats=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+------------------------------
slot_name | my_slot
plugin | test_decoding
slot_type | logical
datoid | 16389
database | mats
temporary | f
active | f
active_pid | [NULL]
xmin | [NULL]
catalog_xmin | 406253
restart_lsn | 0/9F92C808
confirmed_flush_lsn | 0/9F92C840
wal_status | reserved
safe_wal_size | [NULL]
two_phase | t
inactive_since | 2025-06-17 10:20:49.825019+02
conflicting | f
invalidation_reason | [NULL]
failover | f
synced | f
So, I am not sure why this would be OK, but not the first case. Actually, I
am not sure why the first case would be a problem either, if it was allowed.
Could somebody enlighten me?
--
Best wishes,
Mats Kindahl, Timescale
