Search code examples
postgresqllogical-decoding

PostgresQL: Is an entire transaction always written to a replication slot?

I have created a replication slot:

SELECT * FROM pg_create_logical_replication_slot('boxoffice_slot', 'test_decoding');

Each step in a transaction has its own row in a replication slot. Here is an example:

     lsn     |   xid   |     data
-------------+---------+---------------
 34A/7000028 | 1311904 | BEGIN 1311904
 34A/70020E0 | 1311904 | table cad.purchases: INSERT: id[integer]:754862
 34A/70020E1 | 1311904 | table cad.purchases: INSERT: id[integer]:754863
 34A/7000028 | 1311904 | COMMIT 1311904

Questions:
At what point in the transaction lifecycle do transaction steps start getting written to the replication slot?

Is it possible that transaction steps are written to a replication slot before the transaction is committed?

In other words, is it possible that only half a transaction is written to a replication slot at any given time like so:

     lsn     |   xid   |     data
-------------+---------+---------------
 34A/7000028 | 1311904 | BEGIN 1311904
 34A/70020E0 | 1311904 | table cad.purchases: INSERT: id[integer]:754862

Thanks very much for an insight on this.

Solution

  • Based on the following test I conclude that a transaction is inserted into a replication slot only after it is committed. 

    testing=# BEGIN;
BEGIN
testing=# SELECT * FROM pg_logical_slot_get_changes('testing_slot', NULL, NULL);
 lsn | xid | data
-----+-----+------
(0 rows)

testing=# insert into person values (generate_series(1,10));
INSERT 0 10
testing=# SELECT * FROM pg_logical_slot_get_changes('testing_slot', NULL, NULL);
 lsn | xid | data
-----+-----+------
(0 rows)

testing=# COMMIT;
COMMIT
testing=# SELECT * FROM pg_logical_slot_get_changes('testing_slot', NULL, NULL);
    lsn     |   xid    |                           data
------------+----------+-----------------------------------------------------------
 D/23426BC0 | 16171153 | BEGIN 16171153
 D/23426BC0 | 16171153 | table public.person: INSERT: name[character varying]:'1'
 D/23427078 | 16171153 | table public.person: INSERT: name[character varying]:'2'
 D/234270B8 | 16171153 | table public.person: INSERT: name[character varying]:'3'
 D/234270F8 | 16171153 | table public.person: INSERT: name[character varying]:'4'
 D/23427138 | 16171153 | table public.person: INSERT: name[character varying]:'5'
 D/23427178 | 16171153 | table public.person: INSERT: name[character varying]:'6'
 D/234271B8 | 16171153 | table public.person: INSERT: name[character varying]:'7'
 D/234271F8 | 16171153 | table public.person: INSERT: name[character varying]:'8'
 D/23427238 | 16171153 | table public.person: INSERT: name[character varying]:'9'
 D/23427278 | 16171153 | table public.person: INSERT: name[character varying]:'10'
 D/23427320 | 16171153 | COMMIT 16171153
(12 rows)