Re: logical decoding and replication of sequences

On Wed, Feb 23, 2022 at 4:54 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> 7) This updates psql to do roughly the same thing as for tables, so \dRp
> now list sequences added either directly or through schema, so you might
> get footer like this:
>
> \dRp+ testpub_mix
> ...
> Tables:
> "public.testpub_tbl1"
> Tables from schemas:
> "pub_test"
> Sequences:
> "public.testpub_seq1"
> Sequences from schemas:
> "pub_test"
>
> Maybe it's a bit too verbose, though. It also addes "All sequences" and
> "Sequences" columns into the publication description, but I don't think
> that can be done much differently.
>
> FWIW I had to switch the describeOneTableDetails() chunk dealing with
> sequences from printQuery() to printTable() in order to handle dynamic
> footers.
>
> There's also a change in \dn+ because a schema may be included in one
> publication as "FOR ALL SEQUENCES IN SCHEMA" and in another publication
> with "FOR ALL TABLES IN SCHEMA". So I modified the output to
>
> \dn+ pub_test1
> ...
> Publications:
> "testpub_schemas" (sequences)
> "testpub_schemas" (tables)
>
> But maybe it'd be better to aggregate this into a single line like
>
> \dn+ pub_test1
> ...
> Publications:
> "testpub_schemas" (tables, sequences)
>
> Opinions?
>

I think the second one (aggregated) might be slightly better as that
will lead to a lesser number of lines when there are multiple such
publications but it should be okay if you and others prefer first.

> 8) There's a shortcoming in the current grammar, because you can specify
> either
>
> CREATE PUBLICATION p FOR ALL TABLES;
>
> or
>
> CREATE PUBLICATION p FOR ALL SEQUENCES;
>
> but it's not possible to do
>
> CREATE PUBLICATION p FOR ALL TABLES AND FOR ALL SEQUENCES;
>
> which seems like a fairly reasonable thing users might want to do.
>

Isn't it better to support this with a syntax as indicated by Tom in
one of his earlier emails on this topic [1]? IIUC, it would be as
follows:

CREATE PUBLICATION p FOR ALL TABLES, ALL SEQUENCES;

> The problem is that "FOR ALL TABLES" (and same for sequences) is
> hard-coded in the grammar, not defined as PublicationObjSpec. This also
> means you can't do
>
> ALTER PUBLICATION p ADD ALL TABLES;
>
> AFAICS there are two ways to fix this - adding the combinations into the
> definition of CreatePublicationStmt, or adding FOR ALL TABLES (and
> sequences) to PublicationObjSpec.
>

I can imagine that adding to PublicationObjSpec will look compatible
with existing code but maybe another way will also be okay.

[1] - https://www.postgresql.org/message-id/877603.1629120678%40sss.pgh.pa.us

--
With Regards,
Amit Kapila.