Re: Logical Replication of sequences

On Mon, 21 Jul 2025 at 15:24, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Mon, Jul 21, 2025 at 2:36 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Mon, 21 Jul 2025 at 11:15, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > >
> > > On Mon, Jul 21, 2025 at 10:36 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > I was just trying a different test, so I realized that ALTER
> > > > PUBLICATION ADD SEQUENCE is not supported, any reason for the same?
> > > >
> > > > postgres[154731]=# ALTER PUBLICATION pub ADD sequence s1;
> > > > ERROR: 42601: invalid publication object list
> > > > LINE 1: ALTER PUBLICATION pub ADD sequence s1;
> > > > DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a
> > > > standalone table or schema name.
> > > > LOCATION: preprocess_pubobj_list, gram.y:19685
> > > >
> > > Also I noticed that
> > > 1. We don't allow creating publication with individual sequences (e.g.
> > > CREATE PUBLICATION pub FOR SEQUENCE s1;). Is it because the main
> > > purpose of this sync is major version upgrade and we do not have
> > > scenarios for replicating a few sequences or there are some technical
> > > difficulties in achieving that or both.
> >
> > There are no technical difficulties here. The main goal was to support
> > all sequences necessary for the upgrade scenario. Once that is
> > complete, the implementation can be extended based on additional use
> > cases.
> >
> > > 2. This syntax works (CREATE PUBLICATION pub FOR ALL TABLES,
> > > SEQUENCES;) but tab completion doesn't suggest this
> >
> > Nisha had analysed this and shared this earlier at [1]:
> > Tab-completion is not supported after a comma (,) in any other cases.
> > For example, the following commands are valid, but tab-completion does
> > not work after the comma:
> > CREATE PUBLICATION pub7 FOR TABLE t1, TABLES IN SCHEMA public;
> > CREATE PUBLICATION pub7 FOR TABLES IN SCHEMA public, TABLES IN SCHEMA schema2;
> >
> > > 3. Some of the syntaxes works for sequence which doesn't make sense to
> > > me, as listed below, I think there are more
> > >
> > > postgres[154731]=# CREATE PUBLICATION insert_only FOR ALL SEQUENCES
> > > WITH (publish = 'insert');
> > > CREATE PUBLICATION
> > >
> > > postgres[154731]=# CREATE PUBLICATION pub FOR ALL SEQUENCES WITH (
> > > PUBLISH_VIA_PARTITION_ROOT );
> > > CREATE PUBLICATION
> >
> > There is a documentation for this at sql-createpublication.html:
> > WITH ( publication_parameter [= value] [, ... ] )
> > This clause specifies optional parameters for a publication when
> > publishing tables. This clause is not applicable for sequences.
> >
> > I felt it was enough, should we do anything more here?
> >
> > [1] - https://www.postgresql.org/message-id/CABdArM5axwoTorZnJww5rE79SNzvnnXCfWkv7XJex1Rkz%3DJDog%40mail.gmail.com
>
> I meant the PUBLISH_VIA_PARTITION_ROOT and other options inside the
> WITH() that are not applicable for SEQUENCES, so shall we throw an
> error if we are only publishing sequences and using these options?

This is handled in the v20250723 version patch attached.

Also the comment from [1] is addressed.
[1] - https://www.postgresql.org/message-id/CAJpy0uB0HmmiqrE5DrvH-jZPgSM7iiObGuTbg5JeOrEPT_5xPw%40mail.gmail.com

Regards,
Vignesh