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
v1-0001-Error-if-same-table-has-ONLY-mismatch.patch
Description: Binary data
