Hi all,
*My top-level query is*: I'm using logical replication under pg 9.6 to do a
kind of change data capture and I'm seeing occasional extended periods of
significant lag. I'm not sure what conceptual model I'm missing in order to
understand why this happens.
*The details:*
I'm running Postgres 9.6.19 from the postgres debian apt repos & the
wal2json extension.
I have a custom client application which essentially executes
pg_logical_slot_get_changes() for some manually-created logical replication
slot on a loop.
I'm monitoring replication lag defined as pg_current_xlog_location() -
confirmed_flush_lsn for that slot.
What I'm observing is - very occasionally - an extended period (hours long)
wherein:
* The normal database write load continues or slightly increases
* calls to pg_logical_slot_get_changes() return no rows and
confirmed_flush_lsn doesn't move
* the duration of a call to pg_logical_slot_get_changes() rises linearly
over time
I understand from the docs and research that this is usually caused by a
long-running write transaction, but I notice I'm still confused.
* I'm not 100% sure - I'm still confirming - but I'm fairly confident that
I don't have any egregiously long write transactions (at least on that
scale of hours). Are there any other common scenarios that can result in a
similar 'blockage'? e.g some categories of long read-only transactions, or
advisory locks, or other kinds of database activity like a vacuum?
* Conversely, from experimenting, it seems as if not all long-running write
transactions cause pg_logical_slot_get_changes() to be unable to advance.
In fact, I'm not able so far to produce a minimal set of simple queries
which show that behaviour.
Given the following sequence of queries I see changes emitted:
-- session 1
begin;
insert into foo(bar,baz) values (1, 1);
-- session 2
begin;
insert into foo(bar,baz) values (2,2);
commit;
-- session 3
select data from
pg_logical_slot_get_changes('example-slot', NULL, NULL, 'format-version',
'2');
Session 3 is able to return the row from session 2 despite session 1's
ongoing transaction starting first and not yet committing. Can you help me
understand (or better yet point me to a resource which explains) the
underlying logic defining how logical decoding does in fact get blocked by
in-flight transactions?
Thanks,
Patrick
