Re: Logical Replication of sequences

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?

--
Regards,
Dilip Kumar
Google