Re: Skipping schema changes in publication

On Fri, Jun 27, 2025 at 3:44 PM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> On Thu, 26 Jun 2025 at 15:27, shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Tue, Jun 24, 2025 at 9:48 AM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
> > >
> > >  I have included the changes for
> > > it in v14-0003 patch.
> > >
> > Thanks for the patches. I have reviewed patch001 alone, please find
> > few comments:
> >
> > 1)
> > +  <para>
> > +   The <literal>RESET</literal> clause will reset the publication to the
> > +   default state which includes resetting the publication parameters, setting
> > +   <literal>ALL TABLES</literal> flag to <literal>false</literal> and
> > +   dropping all relations and schemas that are associated with the
> > +   publication.
> >    </para>
> >
> > It is misleading, as far as I have understood, we do not drop the
> > tables or schemas associated with the pub; we just remove those from
> > the publication's object list. See previous doc:
> > "The ADD and DROP clauses will add and remove one or more
> > tables/schemas from the publication"
> >
> > Perhaps we want to say the same thing when we speak about the 'drop'
> > aspect of RESET.
> I have updated the document.
>
> > 2)
> > AlterPublicationReset():
> >
> > + if (!OidIsValid(prid))
> > + ereport(ERROR,
> > + (errcode(ERRCODE_UNDEFINED_OBJECT),
> > + errmsg("relation \"%s\" is not part of the publication",
> > + get_rel_name(relid))));
> >
> > Can you please help me understand which scenario will give this error?
> >
> > Another question is do we really need this error? IIUC, we generally
> > give errors if a user has explicitly called out a name of an object
> > and that object is not found. Example:
> >
> > postgres=# alter publication pubnew drop table t1,tab2;
> > ERROR:  relation "t1" is not part of the publication
> >
> > While in a few other cases, we pass missing_okay as true and do not
> > give errors. Please see other callers of performDeletion in
> > publicationcmds.c itself. There we have usage of missing_okay=true. I
> > have not researched myself, but please analyze the cases where
> > missing_okay is passed as true to figure out if those match our RESET
> > case. Try to reproduce if possible and then take a call.
> I thought about the above point and I also think this check is not
> required. Also, the function was calling PublicationDropSchemas with
> missing_ok as false. I have changed it to be true.
>

Okay. Is there a reason for not using PublicationDropTables() here? We
have rewritten similar code in the Reset flow.

> > 3)
> > +ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA public;
> > +ERROR:  syntax error at or near "ALL"
> > +LINE 1: ALTER PUBLICATION testpub_reset ADD ALL TABLES IN SCHEMA pub...
> >
> > There is a problem in syntax, I think the intention of testcase was to
> > run this query successfully.
>
> I have fixed it.
>
> Thanks Shveta for reviewing the patch. I have addressed the comments
> and posted an updated version v15 in [1].

Thanks for the patches. My review is in progress but please find few
comments on 002:

1)
where exception_object is:
    [ ONLY ] table_name [ * ]

We have the above in CREATE and ALTER pub docs, but we do not explain
ONLY with EXCEPT. We do have an explanation of ONLY under 'FOR TABLE'.
But since 'FOR TABLE' and 'EXCEPT' do not go together, it is somewhat
difficult to connect the dots and find the information ONLY in the
context of EXCEPT. We shall have ONLY explained for EXCEPT as well. Or
we can have ONLY defined in a way that both 'FOR TABLE' and 'EXCEPT'
can refer to it.

2)
We get tab-completion options in this command:
postgres=# create publication pub5 for TABLE tab1 W
WHERE (  WITH (

Similarly in this command:
create publication pub5 for TABLES IN SCHEMA s1

But once we have 'EXCEPT TABLE', we do not get further tab-completion
option like WITH(...)
create publication pub5 for ALL TABLES EXCEPT TABLE tab1

3)
During tab-expansion, 'EXCEPT TABLE' and  'WITH (' in the below
command looks like they are connecting words. Can the gap be increased
similar to tab-expansion of next command shown below:

postgres=# create publication pub4 for ALL TABLES
EXCEPT TABLE  WITH (

postgres=# create publication pub4 for
ALL TABLES        TABLE             TABLES IN SCHEMA

4)
alter_publication.sgml.orig is a left-over in patch002.

thanks
Shveta