Re: Added schema level support for publication. - Mailing list pgsql-hackers

On Tue, Aug 31, 2021 at 10:50 AM Greg Nancarrow <gregn4422@gmail.com> wrote:
>
> On Tue, Aug 31, 2021 at 1:41 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> > >
> > > I notice that "CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sc1,
> > > TABLE sc1.test;"  maintains the table separately and results in the
> > > following in the \dRp+ output:
> > >
> > > Tables:
> > >     "sc1.test"
> > > Schemas:
> > >     "sc1"
> > >
> > > and also then "ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;"
> > > still leaves the "sc1.test" table in the publication.
> >
> > I had intentionally implemented this way, the reason being it gives
> > the flexibility to modify the publications based on the way the
> > publication is created. My idea was that if a user specified a
> > table/schema of the same schema while creating the publication, the
> > user should be allowed to drop any of them at any time. In the above
> > case if we don't maintain the results separately, users will not be
> > able to drop the table from the publication at a later point of time.
> > Thoughts?
> >
>
> Hmmm. I'm not sure it should work like that (but maybe I'm wrong -
> what do others think???).
> I thought that "CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sc1,
> TABLE sc1.test;" should silently just ignore the "TABLE sc1.test"
> part, as that is a table in schema sc1, so it's effectively a
> duplicate.
>

I find the way it is implemented to be more intuitive as that gives
users more flexibility to retain certain tables from the schema and
appears to be exactly what users intended by the command. I don't
think finding duplicates among different object lists (schema, table)
is a good idea because tomorrow for some other objects the same thing
can happen. It might be better to get some other opinions on this
matter though.

> Also, I noticed the following:
>
> postgres=# CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sc1,
> postgres-# TABLE sc1.test;
> CREATE PUBLICATION
> postgres=# \dRp+
>                             Publication pub1
>  Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
> -------+------------+---------+---------+---------+-----------+----------
>  gregn | f          | t       | t       | t       | t         | f
> Tables:
>     "sc1.test"
> Schemas:
>     "sc1"
>
> postgres=# ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;
> ALTER PUBLICATION
> postgres=# \dRp+
>                             Publication pub1
>  Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
> -------+------------+---------+---------+---------+-----------+----------
>  gregn | f          | t       | t       | t       | t         | f
> Tables:
>     "sc1.test"
>
> postgres=# ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;
> ERROR:  schema "sc1" is not part of the publication
>

What will happen if you second time run the command as ALTER
PUBLICATION pub1 DROP Table sc1.test? If that works, I think the
behavior should be fine.

-- 
With Regards,
Amit Kapila.