Re: Added schema level support for publication.

On Tue, Oct 5, 2021 at 3:11 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> > Code has been added to prevent a table being set (via ALTER TABLE) to
> > UNLOGGED if it is part of a publication, but I found that I could
> > still add all tables of a schema having a table that is UNLOGGED:
> >
> > postgres=# create schema sch;
> > CREATE SCHEMA
> > postgres=# create unlogged table sch.test(i int);
> > CREATE TABLE
> > postgres=# create publication pub for table sch.test;
> > ERROR: cannot add relation "test" to publication
> > DETAIL: Temporary and unlogged relations cannot be replicated.
> > postgres=# create publication pub for all tables in schema sch;
> > CREATE PUBLICATION
> >
>
> What about when you use "create publication pub for all tables;"? I
> think that also works, now on similar lines shouldn't the behavior of
> "all tables in schema" publication be the same? I mean if we want we
> can detect and give an error but is it advisable to give an error if
> there are just one or few tables in schema that are unlogged?
>

OK, it seems that for the ALL TABLES case, there is no such error
check, and it just silently skips replication of any
temporary/unlogged tables. This is intentional, right?
I couldn't see any documentation specifically related to this, so I
think perhaps it should be updated to describe this behaviour. At the
moment, the existing documentation just states FOR TABLE that
"Temporary tables, unlogged tables, foreign tables, materialized
views, and regular views cannot be part of a publication".
Yes, I agree that ALL TABLES IN SCHEMA should behave the same as the
ALL TABLES case.
Problem is, shouldn't setting UNLOGGED on a table only then be
disallowed if that table was publicised using FOR TABLE?

With the patch applied:

postgres=# create publication pub3 for all tables in schema sch;
CREATE PUBLICATION
postgres=# create table sch.test3(i int);
CREATE TABLE
postgres=# alter table sch.test3 set unlogged;
ERROR: cannot change table "test3" to unlogged because it is part of
a publication
DETAIL: Unlogged relations cannot be replicated.

Regards,
Greg Nancarrow
Fujitsu Australia