Replication slots and isolation levels

Hi all.

I’m wondering why do I get conflicts with recovery on hot standby using replication slots and read commited isolation level? And if I start repeatable read transaction I don’t get any errors. Below is some diagnostics.

I’m using 9.4.4 (upgrade is planned) from yum.postgresql.org <http://yum.postgresql.org/> packages on both master and standby. Configs are the same on both master and standby:

rpopdb01d/postgres M # SELECT name, setting FROM pg_settings
WHERE category LIKE 'Replication%' or category LIKE 'Write-Ahead Log';
name | setting
------------------------------+---------
hot_standby | on
hot_standby_feedback | on
max_replication_slots | 1
max_standby_archive_delay | 30000
max_standby_streaming_delay | 30000
max_wal_senders | 10
synchronous_standby_names |
vacuum_defer_cleanup_age | 200000
wal_keep_segments | 64
wal_receiver_status_interval | 1
wal_receiver_timeout | 60000
wal_sender_timeout | 3000
(12 rows)

Time: 1.583 ms
rpopdb01d/postgres M #

On the master I’ve created a physical replication slot and attached standby to it, I do see changing xmin and restart_lsn fields in pg_replication_slots view.

rpopdb01d/postgres M # select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
----------------------+--------+-----------+--------+----------+--------+------------+--------------+---------------
rpopdb01e_domain_com | [null] | physical | [null] | [null] | t | 2127399287 | [null] | 960B/415C79C8
(1 row)

Time: 0.463 ms
rpopdb01d/postgres M #

When I start a read commited transaction on standby (or use autocommit mode, doesn’t matter) I still see that xmin in pg_replication_slots view on master increases. If I do run a heavy SELECT statement, at some point of time (presumably after vacuum_defer_cleanup_age expires) standby starts to lag replication apply and when it hits max_standby_streaming_delay I get 40001 sql code, either ERROR or FATAL:

rpopdb01e/rpopdb R # SHOW transaction_isolation ;
transaction_isolation
-----------------------
read committed
(1 row)

Time: 0.324 ms
rpopdb01e/rpopdb R # SELECT count(*) FROM big_table;
ERROR: 40001: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
LOCATION: ProcessInterrupts, postgres.c:2990
Time: 199791.339 ms
rpopdb01e/rpopdb R #

rpopdb01e/rpopdb R # SHOW transaction_isolation ;
transaction_isolation
-----------------------
read committed
(1 row)

Time: 0.258 ms
rpopdb01e/rpopdb R # BEGIN;
BEGIN
Time: 0.067 ms
rpopdb01e/rpopdb R # SELECT count(*) FROM big_table;
FATAL: 40001: terminating connection due to conflict with recovery
DETAIL: User was holding a relation lock for too long.
LOCATION: ProcessInterrupts, postgres.c:2857
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Time: 307864.830 ms
rpopdb01e/rpopdb R #

The behavior is the same as expected to be without using replication slots.

But when I start repeatable read transaction xmin field in pg_replication_slots view on master freezes (while restart_lsn is still increasing) and I don’t get any replication lag and conflicts with recovery. When I end this transaction, xmin starts increasing again.

rpopdb01e/rpopdb R # begin transaction isolation level repeatable read;
BEGIN
Time: 0.118 ms
rpopdb01e/rpopdb R # SELECT count(*) FROM big_table;
count
------------
3106222429
(1 row)

Time: 411944.889 ms
rpopdb01e/rpopdb R # ROLLBACK;
ROLLBACK
Time: 0.269 ms
rpopdb01e/rpopdb R #

And that is what I expect. Am I missing something or is it expected behavior in read commited mode?

Thanks in advance.

--
May the force be with you…
https://simply.name