Re: Publication of same parent table with ONLY-ness mismatch should error

On Fri, Aug 28, 2026 at 9:04 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> Hi.
>
> While reviewing another thread, I had cause to enumerate and try many
> dozens of CREATE PUBLICATION clause combinations. I found one
> combination that gave an unexpected result. IMO this a bug:
>
> CREATE PUBLICATION ... FOR TABLE s1.parent, TABLE ONLY s1.parent;
> -- No error? First wins. Behaves like FOR TABLE
>
> CREATE PUBLICATION ... FOR TABLE ONLY s1.parent, TABLE s1.parent;
> -- No error? First wins. Behaves like FOR TABLE ONLY

I also feel that it should be an error scenario. Let's see what others
have to say here.

While validating the patch, I figured out that this simple scenario,
even without inheritance created, also triggers the error:

postgres=# create table tab1(i int);
CREATE TABLE
postgres=# create publication pub1 for table only tab1, tab1;
ERROR: conflicting ONLY specifications for table "tab1"

I see that 't->relation->inh' is true for anything which does not have
'ONLY' specified with it. It makes sense, too. Even though tab1
doesn't have any descendants yet, it may have them in the future, and
then this publication can break silently. So, it's better to reject
such a publication at creation time, even for tables like tab1.

> Below is how to reproduce:
>
> ======
>
> CREATE SCHEMA s1;
> CREATE SCHEMA s2;
>
> -- Create the parent table
> CREATE TABLE IF NOT EXISTS s1.parent ( id SERIAL PRIMARY KEY, name
> TEXT NOT NULL, email TEXT );
>
> -- Create the child tables that inherit from parent
> CREATE TABLE IF NOT EXISTS s1.child1 ( department TEXT NOT NULL )
> INHERITS (s1.parent);
> CREATE TABLE IF NOT EXISTS s2.child2 ( department TEXT NOT NULL )
> INHERITS (s1.parent);
>
> CREATE PUBLICATION pub_table FOR TABLE s1.parent;
> CREATE PUBLICATION pub_tableonly FOR TABLE ONLY s1.parent;
>
> CREATE PUBLICATION pub_table_tableonly FOR TABLE s1.parent, TABLE ONLY
> s1.parent;
> CREATE PUBLICATION pub_tableonly_table FOR TABLE ONLY s1.parent, TABLE
> s1.parent;
>
> CREATE PUBLICATION pub_s1 FOR TABLES IN SCHEMA s1;
> CREATE PUBLICATION pub_s1_table FOR TABLES IN SCHEMA s1, TABLE s1.parent;
> CREATE PUBLICATION pub_s1_tableonly FOR TABLES IN SCHEMA s1, TABLE
> ONLY s1.parent;
> CREATE PUBLICATION pub_table_s1 FOR TABLE s1.parent, TABLES IN SCHEMA s1;
> CREATE PUBLICATION pub_tableonly_s1 FOR TABLE ONLY s1.parent, TABLES
> IN SCHEMA s1;
>
> SELECT pubname, schemaname, tablename FROM pg_publication_tables ORDER
> by pubname;
>
> ~~~
>
> Results (unpatched):
>
> pubname schemaname tablename
>
> -- FOR TABLES IN SCHEMA does not reach across to s2. ok
> pub_s1 s1 parent
> pub_s1 s1 child1
>
> -- Extra FOR TABLE does reach across to schema s2. good!
> pub_s1_table s1 parent
> pub_s1_table s1 child1
> pub_s1_table s2 child2
>
> -- Extra FOR TABLE ONLY does not reach into s2. ok
> pub_s1_tableonly s1 parent
> pub_s1_tableonly s1 child1
>
> -- FOR TABLE reaches into s2. ok
> pub_table s1 parent
> pub_table s1 child1
> pub_table s2 child2
>
> -- Extra FOR TABLE does reach across into schema s2. good!
> pub_table_s1 s1 parent
> pub_table_s1 s1 child1
> pub_table_s1 s2 child2
>
> -- FOR TABLE ONLY is just that table. ok
> pub_tableonly s1 parent
>
> -- Extra FOR TABLE ONLY does not reach into s2. ok
> pub_tableonly_s1 s1 parent
> pub_tableonly_s1 s1 child1
>
> -- No error? First wins. Behaves like FOR TABLE ONLY. BUG??
> pub_tableonly_table s1 parent
>
> -- No error? First wins. Behaves like FOR TABLE. BUG??
> pub_table_tableonly s1 parent
> pub_table_tableonly s1 child1
> pub_table_tableonly s2 child2
>
> ======
>
> The "FOR TABLE s1.parent" and "FOR TABLE ONLY s1.parent" are clearly
> not the same thing, so why are they allowed to co-exist in the same
> publication. AFAIK, nothing else uses a "first-one-wins" behaviour
> like this (e.g. repeated tables with different column-lists gives an
> error, not first-one-wins).
>
> IMO this is a bug.
>
> My AI query agrees that this "first-one-wins" rule for TABLE ONLY is
> undocumented and has not been discussed before -- it's just some
> undocumented/untested side-effect of a 2017 code that does "continue;"
> whenever the same relid is encountered in the publication.
>
> ~~~
>
> PSA a patch to now report an ONLY-ness mismatch as another
> "conflicting" error. Note, there were already other similar conflict
> errors for clashing column-lists and clashing row filters, but the
> clashing ONLY-ness error was missing.
>
> New regression tests added.
>
> `make check-world` is unaffected, and passes OK.
>
> ======
> Kind Regards,
> Peter Smith.
> Fujitsu Australia