Publication of same parent table with ONLY-ness mismatch should error
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253586psql -h localhost -U postgresBuilt from patchset v11 (message #11), October 24, 2026 at 11:17 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253586_11 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253586_11 && git checkout t253586_11Patchset v11 (message #11) is on t253586_11
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
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
On Fri, Aug 28, 2026 at 9:04 AM Peter Smith <smithpb2250@gmail.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 TABLECREATE 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.
Show quoted text
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
Hi all
On 2026/08/28 13:54, shveta malik wrote:
On Fri, Aug 28, 2026 at 9:04 AM Peter Smith <smithpb2250@gmail.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 TABLECREATE PUBLICATION ... FOR TABLE ONLY s1.parent, TABLE s1.parent;
-- No error? First wins. Behaves like FOR TABLE ONLY
I also think this is a bug and should become an error. Specifying both
TABLE and TABLE ONLY does not make much sense.
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.
For a plain table with no child tables, I also feel it make sense to
make this error. It seems to me a publication table is registered in
pg_publication_rel based on the definition when CREATE PUBLICATION is
executed. So, even if the plain table gets a child table in future, it
would not affect the publication definition. Having said that, this
should become an error.
The fix and test code look good to me. I also noticed that you improved
the doc explanation regarding the relationship between ONLY clause and
partitioned table. How about splitting this doc patch to a separate
patch and thread? The doc fix seems good to me but it's not directly
related to the main issue in this thread. By separating the thread, I
guess it would get more attention and be easier to commit.
Regards,
Takeshi Ideriha
On Thu, Oct 15, 2026 at 5:51 PM Takeshi Ideriha
<iderihatakeshi@gmail.com> wrote:
Hi all
On 2026/08/28 13:54, shveta malik wrote:
On Fri, Aug 28, 2026 at 9:04 AM Peter Smith <smithpb2250@gmail.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 TABLECREATE PUBLICATION ... FOR TABLE ONLY s1.parent, TABLE s1.parent;
-- No error? First wins. Behaves like FOR TABLE ONLYI also think this is a bug and should become an error. Specifying both
TABLE and TABLE ONLY does not make much sense.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.For a plain table with no child tables, I also feel it make sense to
make this error. It seems to me a publication table is registered in
pg_publication_rel based on the definition when CREATE PUBLICATION is
executed. So, even if the plain table gets a child table in future, it
would not affect the publication definition. Having said that, this
should become an error.The fix and test code look good to me. I also noticed that you improved
the doc explanation regarding the relationship between ONLY clause and
partitioned table. How about splitting this doc patch to a separate
patch and thread? The doc fix seems good to me but it's not directly
related to the main issue in this thread. By separating the thread, I
guess it would get more attention and be easier to commit.
Hi Takeshi-San.
Thanks for giving feedback on this patch.
Bugfix:
I found a scenario that was not being handled correctly -- i.e. when
implicitly inherited children were also explicitly mentioned.
Fixed and added a test case.
Docs patch:
The docs part is an independent improvement, so I'll move it to a
separate thread as suggested.
~
PSA patch v2
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Mon, Oct 19, 2026 at 11:50 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Thu, Oct 15, 2026 at 5:51 PM Takeshi Ideriha
<iderihatakeshi@gmail.com> wrote:Hi all
On 2026/08/28 13:54, shveta malik wrote:
On Fri, Aug 28, 2026 at 9:04 AM Peter Smith <smithpb2250@gmail.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 TABLECREATE PUBLICATION ... FOR TABLE ONLY s1.parent, TABLE s1.parent;
-- No error? First wins. Behaves like FOR TABLE ONLYI also think this is a bug and should become an error. Specifying both
TABLE and TABLE ONLY does not make much sense.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.For a plain table with no child tables, I also feel it make sense to
make this error. It seems to me a publication table is registered in
pg_publication_rel based on the definition when CREATE PUBLICATION is
executed. So, even if the plain table gets a child table in future, it
would not affect the publication definition. Having said that, this
should become an error.The fix and test code look good to me. I also noticed that you improved
the doc explanation regarding the relationship between ONLY clause and
partitioned table. How about splitting this doc patch to a separate
patch and thread? The doc fix seems good to me but it's not directly
related to the main issue in this thread. By separating the thread, I
guess it would get more attention and be easier to commit.Hi Takeshi-San.
Thanks for giving feedback on this patch.
Bugfix:
I found a scenario that was not being handled correctly -- i.e. when
implicitly inherited children were also explicitly mentioned.
Fixed and added a test case.Docs patch:
The docs part is an independent improvement, so I'll move it to a
separate thread as suggested.~
PSA patch v2
Overall the patch looks good. But with so many 'relids' in play, it
takes time to figure out what each one represents. Do you think we
should also add below in the new comment:
/*
* relids holds every relation added to the result so far, both those the
* user named and inheritance children added implicitly by recursion. It
* is used to filter out duplicates.
*
* relids_explicit holds only the relations the user actually named (a
* subset of relids). relids_recursing holds those explicitly named
* relations whose first mention did not specify ONLY. Together they are
* used to detect conflicting ONLY specifications for the same table.
*/
thanks
Shveta
On Mon, Oct 19, 2026 at 6:11 PM shveta malik <shveta.malik@gmail.com> wrote:
...
Overall the patch looks good. But with so many 'relids' in play, it
takes time to figure out what each one represents. Do you think we
should also add below in the new comment:/*
* relids holds every relation added to the result so far, both those the
* user named and inheritance children added implicitly by recursion. It
* is used to filter out duplicates.
*
* relids_explicit holds only the relations the user actually named (a
* subset of relids). relids_recursing holds those explicitly named
* relations whose first mention did not specify ONLY. Together they are
* used to detect conflicting ONLY specifications for the same table.
*/
Hi Shveta.
I agree, your suggested code comment looks helpful.
Added it in patch v3. PSA.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Tue, Oct 20, 2026 at 3:34 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Mon, Oct 19, 2026 at 6:11 PM shveta malik <shveta.malik@gmail.com> wrote:
...
Overall the patch looks good. But with so many 'relids' in play, it
takes time to figure out what each one represents. Do you think we
should also add below in the new comment:/*
* relids holds every relation added to the result so far, both those the
* user named and inheritance children added implicitly by recursion. It
* is used to filter out duplicates.
*
* relids_explicit holds only the relations the user actually named (a
* subset of relids). relids_recursing holds those explicitly named
* relations whose first mention did not specify ONLY. Together they are
* used to detect conflicting ONLY specifications for the same table.
*/Hi Shveta.
I agree, your suggested code comment looks helpful.
Added it in patch v3. PSA.
The comment looks good.
Should we also add tests for the EXCEPT clause, as it touches EXCEPT?
postgres=# create publication pub1 for all tables except(table only
testpub_tbl_parent, testpub_tbl_parent);
ERROR: conflicting ONLY specifications for table "testpub_tbl_parent"
thanks
Shveta
On Tue, Oct 20, 2026 at 2:30 PM shveta malik <shveta.malik@gmail.com> wrote:
...
Should we also add tests for the EXCEPT clause, as it touches EXCEPT?
postgres=# create publication pub1 for all tables except(table only
testpub_tbl_parent, testpub_tbl_parent);
ERROR: conflicting ONLY specifications for table "testpub_tbl_parent"
I think having EXCEPT tests for ONLY-ness mismatch will execute the
same internal logic (OpenTableList) that is already being tested.
I've added a few EXCEPT tests just for completeness. To make them a
bit different, they are using ALTER PUBLICATION.
PSA patch v4.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
PSA patch v4.
Hi Peter,
Thank you for the patches,
I reviewed the patch v4 and these are minor points, so please feel
free to leave them out if you don’t think they’re necessary.
1. In the comment in publicationcmds.c,
typo :“explcitly” -> “explicitly.”
2. In publication.sql, CREATE TABLE tbl_plain(a int);;
has an extra semicolon.
3. Will adding the following test for tbl_plain be redundant?
CREATE PUBLICATION testpub_conflict5 FOR TABLE tbl_plain, ONLY tbl_plain;
CREATE PUBLICATION testpub_conflict6 FOR TABLE ONLY tbl_plain, tbl_plain;
Apart from these the patch LGTM.
Best,
Kedar
On Tue, Oct 20, 2026 at 11:23 PM kedar anavardekar
<kedar.anavardekar@gmail.com> wrote:
PSA patch v4.
Hi Peter,
Thank you for the patches,
I reviewed the patch v4 and these are minor points, so please feel
free to leave them out if you don’t think they’re necessary.1. In the comment in publicationcmds.c,
typo :“explcitly” -> “explicitly.”2. In publication.sql, CREATE TABLE tbl_plain(a int);;
has an extra semicolon.
Hi Kedar. Thanks for catching those typos.
3. Will adding the following test for tbl_plain be redundant?
CREATE PUBLICATION testpub_conflict5 FOR TABLE tbl_plain, ONLY tbl_plain;
CREATE PUBLICATION testpub_conflict6 FOR TABLE ONLY tbl_plain, tbl_plain;
AFAIK, those fall into the same category as the EXCEPT tests. e.g.
they are not testing anything that is not already tested, but still it
may be better to include them for completeness and to prevent people
from wondering why they are absent. Anyway, I have added them to patch
v5.
PSA patch v5.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
v5-0001-Error-if-same-table-has-ONLY-mismatch.patchapplication/octet-stream; name=v5-0001-Error-if-same-table-has-ONLY-mismatch.patchDownload+13-8
Oops. The previously posted patch v5 was broken/incomplete.
PSA patch v6.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
t253586_11v6-0001-Error-if-same-table-has-ONLY-mismatch.patchapplication/octet-stream; name=v6-0001-Error-if-same-table-has-ONLY-mismatch.patchDownload+171-7
On Wed, Oct 21, 2026 at 4:28 AM Peter Smith <smithpb2250@gmail.com> wrote:
Oops. The previously posted patch v5 was broken/incomplete.
PSA patch v6.
LGTM.
thanks
Shveta
On Oct 21, 2026, at 06:58, Peter Smith <smithpb2250@gmail.com> wrote:
Oops. The previously posted patch v5 was broken/incomplete.
PSA patch v6.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
<v6-0001-Error-if-same-table-has-ONLY-mismatch.patch>
V6 overall LGTM.
A nit comment:
```
+ if (list_member_oid(relids_explicit, myrelid) &&
+ recurse != list_member_oid(relids_recursing, myrelid) &&
+ rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("conflicting ONLY specifications for table \"%s\"",
+ RelationGetRelationName(rel))));
```Would it be slightly better to put rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE first? So that partitioned tables skip both list searches.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/