Re: logical decoding and replication of sequences, take 2

On 3/20/23 18:03, Tomas Vondra wrote:
>
> ...
>>
>> I don't know if that is a good idea but are you imagining a special
>> interface/mechanism just for logical replication because as far as I
>> can see you have used SELECT to fetch the sequence values?
>>
>
> Not sure what would the special mechanism be? I don't think it could
> read the sequence from somewhere else, and due the lack of MVCC we'd
> just read same sequence data from the current relfilenode. Or what else
> would it do?
>

I was thinking about alternative ways to do this, but I couldn't think
of anything. The non-MVCC behavior of sequences means it's not really
possible to do this based on snapshots / slots or stuff like that ...

> The one thing we can't quite do at the moment is locking the sequence,
> because LOCK is only supported for tables. So we could either provide a
> function to lock a sequence, or locks it and then returns the current
> state (as if we did a SELECT).
>

... so I took a stab at doing it like this. I didn't feel relaxing LOCK
restrictions to also allow locking sequences would be the right choice,
so I added a new function pg_sequence_lock_for_sync(). I wonder if we
could/should restrict this to logical replication use, somehow.

The interlock happens right after creating the slot - I was thinking
about doing it even before the slot gets created, but that's not
possible, because that installs a snapshot (so it has to be the first
command in the transaction). It acquires RowExclusiveLock, which is
enough to conflict with ALTER SEQUENCE, but allows nextval().

AFAICS this does the trick - if there's ALTER SEQUENCE, we'll wait for
it to complete. And copy_sequence() will read the resulting state, even
though this is REPEATABLE READ - remember, sequences are not subject to
that consistency.

The once anomaly I can think of is the sequence might seem to go
"backwards" for a little bit during the sync. Imagine this sequence of
operations:

1) tablesync creates slot
2) S1 does ALTER SEQUENCE ... RESTART WITH 20 (gets lock)
3) S2 tries ALTER SEQUENCE ... RESTART WITH 100 (waits for lock)
4) tablesync requests lock
5) S1 does the thing, commits
6) S2 acquires lock, does the thing, commits
7) tablesync gets lock, reads current sequence state
8) tablesync decodes changes from S1 and S2, applies them

But I think this is fine - it's part of the catchup, and until that's
done the sync is not considered completed.

I merged the earlier "fixup" patches into the relevant parts, and left
two patches with new tweaks (deducing the corrent "WAL" state from the
current state read by copy_sequence), and the interlock discussed here.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company