Horton

Re: logical decoding and replication of sequences, take 2

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: John Naylor <john.naylor@enterprisedb.com>
Cc: vignesh C <vignesh21@gmail.com>, Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: 2023-03-15T12:51:32Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Migrate logical slots to the new node during an upgrade.

  2. Make test_decoding ddl.out shorter

  3. Fix snapshot handling in logicalmsg_decode

  4. doc: Adjust a few more references to "postmaster"

  5. Revert "Logical decoding of sequences"

Attachments


On 3/14/23 08:30, John Naylor wrote:
> I tried a couple toy examples with various combinations of use styles.
> 
> Three with "automatic" reading from sequences:
> 
> create table test(i serial);
> create table test(i int GENERATED BY DEFAULT AS IDENTITY);
> create table test(i int default nextval('s1'));
> 
> ...where s1 has some non-default parameters:
> 
> CREATE SEQUENCE s1 START 100 MAXVALUE 100 INCREMENT BY -1;
> 
> ...and then two with explicit use of s1, one inserting the 'nextval'
> into a table with no default, and one with no table at all, just
> selecting from the sequence.
> 
> The last two seem to work similarly to the first three, so it seems like
> FOR ALL TABLES adds all sequences as well. Is that expected?

Yeah, that's a bug - we shouldn't replicate the sequence changes, unless
the sequence is actually added to the publication. I tracked this down
to a thinko in get_rel_sync_entry() which failed to check the object
type when puballtables or puballsequences was set.

Attached is a patch fixing this.

> The documentation for CREATE PUBLICATION mentions sequence options,
> but doesn't really say how these options should be used.
Good point. The idea is that we handle tables and sequences the same
way, i.e. if you specify 'sequence' then we'll replicate increments for
sequences explicitly added to the publication.

If this is not clear, the docs may need some improvements.


regards

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