Re: Logical decoding of sequence advances, part II

On 22 August 2016 at 11:13, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> So yeah. I think extending SeqTableData and xl_seq_rec with xid is the way
> to go. Objections?
>

Prototyping this shows that re-using SeqTableData to store the creator xid
won't work out. We can't just store the toplevel xid because TRUNCATE ...
RESTART IDENTITY is transactional, using a new relfilenode and new sequence
"timeline". If we just store and xlog the toplevel xid as the sequence's
creator/restarter we'll fail to correctly handle it if a subxact that did
TRUNCATE ... RESTART IDENTITY rolls back, e.g.

CREATE TABLE x(id serial not null);
SELECT nextval('x_id_seq'); => 1
BEGIN;
SELECT nextval('x_id_seq'); => 2
SAVEPOINT sp1;
SELECT nextval('x_id_seq'); => 3
TRUNCATE TABLE x RESTART IDENTITY;
SELECT nextval('x_id_seq'); => 1
ROLLBACK TO SAVEPOINT sp1;
SELECT nextval('x_id_seq'); => 4

sequence.c:init_sequence() detects this by noticing that the relfilenode
has changed and discarding cached values, resuming at last_value. Knowledge
of whether we created the sequence relfilenode is not retained so we can't
do something similar.

Unless anyone has any clever (or obvious but not to me) solutions to this,
I'll probably need to maintain a separate map of sequence relfilenodes we
created and which xid we created them in, so we can test whether that xid
is still in progress when logging a change. It's still pretty much free
when wal_level < logical or the current xact hasn't created any sequences.

Otherwise I could store a List of xids in the SeqTableData for the sequence
and check that for in-progress xids. It'd usually be NIL. If not, it'll
almost always be a 1-item List, the creating / resetting xid. If subxacts
are involved it'll become a stack. We walk down the stack checking whether
xacts are in progress and popping them if not until we find an in-progress
entry or run out of stack and set it to NIL.

Either will produce the same desired result: the correct subxact xid for
the innermost in-progress xact that created or reset this sequence, if any.

(I initially planned to just punt on TRUNCATE and let event triggers handle
it, but the need to roll back sequence advances if a TRUNCATE ... RESTART
IDENTITY is rolled back means sequence decoding must pay attention to it).

I'm also having trouble working out how to get a historical snapshot for
the most recent committed xact in a decoding session so the sequence's name
can be looked up by oid in the relcache during decoding. Advice would be
welcome if anyone can spare a moment.

I'll keep working on this concurrent with some higher priority work.
Suggestions, advice, or screams of horror welcomed. I think we really,
really need logical decoding of sequence advances...

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services