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

Question

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.

Answer 1

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)

Answer 2

Everything in the transaction is written to the replication slot while it is occurring. If the transaction is rolled back, then a rollback is entered into the WAL. If the stream is broken in the middle of a transaction, then the transaction is not committed on the subscriber end, at least not until the connection is re-established.

One way to test this is by creating a replication slot, and then in another terminal, view the changes with pg_recvlogical:

pg_recvlogical -d postgres --slot <slot_name> --start --verbose -f -

If you BEGIN a transaction, create a table and insert a million rows into it, you'll see that the write up to and flush to values are increasing, even though the transaction did not get committed/rolled back. This tells us that the changes are getting propagated across to the receiver end:

# pg_recvlogical -d postgres --slot regression_slot --start --verbose -f -
pg_recvlogical: starting log streaming at 0/0 (slot regression_slot)
pg_recvlogical: streaming initiated
pg_recvlogical: confirming write up to 0/0, flush to 0/0 (slot regression_slot)
pg_recvlogical: confirming write up to 0/DF5E620, flush to 0/DF5E620 (slot regression_slot)
pg_recvlogical: confirming write up to 0/DF5E620, flush to 0/DF5E620 (slot regression_slot)
pg_recvlogical: confirming write up to 0/12E01AB8, flush to 0/12E01AB8 (slot regression_slot)
pg_recvlogical: confirming write up to 0/12E01AF0, flush to 0/12E01AF0 (slot regression_slot)

Also, if you view pg_current_wal_lsn() in your terminal before and after the transaction ends, you will see that the LSN increases, even though you rolled back:

 pg_current_wal_lsn 
--------------------
 0/DF5E620
(1 row)

postgres=# begin;
BEGIN
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/DF5E620
(1 row)

postgres=# insert into abc values (generate_series(1,1000000),'foobar');
INSERT 0 1000000
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/1243C000
(1 row)

postgres=# rollback;
ROLLBACK
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/12E01AB8
(1 row)

Answer 3

Transactions are not written to replication slots at all. They are written to WAL. It can certainly happen that only part of a transaction is written to WAL, for example if processing is interrupted.

Partly written transactions will automatically be marked as rolled back when the session ends (or during recovery if the server crashed).

Replication slots don't hold more data than LSNs (log sequence numbers). They are persistent data structures that mark a position in the WAL. This is so that the primary server does not discard any WAL that is still needed by a consumer. As the consumer processes WAL, it advances the replication slot's LSN, so that the primary can discard already consumed WAL.