Re: logical decoding and replication of sequences, take 2

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