Hi, In the "Distinguish publication exclusions in object addresses" thread, Peter Smith ran into the case below, Amit suggested starting a new thread for it [1], and shveta noted that it happens with TABLES IN SCHEMA too and that no documentation describes it [2]. Here it is, with a patch.
The case
--------
CREATE TABLE t1(a int);
CREATE PUBLICATION pub FOR ALL TABLES;
SELECT schemaname, tablename FROM pg_publication_tables
WHERE pubname = 'pub';
schemaname | tablename
------------+-----------
public | t1
SELECT pg_get_object_address('publication relation','{public,t1}','{pub}');
ERROR: publication relation "t1" in publication "pub" does not exist
One catalog view says the relation is published by that publication;
the other interface says it does not exist. Both are right in their own
terms -- a FOR ALL TABLES publication stores no pg_publication_rel entry,
so there is no object of that kind to address -- but the message does
not say that, and a user comparing the two has nothing to go on. FOR
TABLES IN SCHEMA behaves the same way, and so does a partition published
through its partitioned ancestor.
It still reproduces on REL_19_STABLE after 91ff666f1d81, and 94670ba6d56
on master leaves this error unchanged. It reproduced identically on 13
through 18.
The patch
---------
I did not change what the function returns: the address really does
not exist, and inventing one would be worse. The patch adds a detail
that says why:
ERROR: publication relation "t1" in publication "pub" does not exist
DETAIL: Table "t1" is published by publication "pub" without an entry
of its own, through FOR ALL TABLES, FOR TABLES IN SCHEMA, or
a partitioned ancestor.
The detail is emitted only when the table really is published; a
relation that is not still gets the plain message. To decide that, the
patch uses is_table_publishable_in_publication(), the test
pg_get_publication_tables() already applies when filtering by relation,
and exports it from pg_publication.c. It only touches the "publication
relation" branch, not the one for excluded relations.
To check that choice, the attached pubrel_detail_matrix.sql tries 11
relations (plain, unlogged, view, sequence, a table in a schema, the
partitioned tables and their partitions, and tables in an EXCEPT
clause) against 6 publications (FOR ALL TABLES with and without
publish_via_partition_root, with EXCEPT, TABLES IN SCHEMA, and FOR TABLE
on a partitioned table with and without publish_via_partition_root),
and compares pg_publication_tables with what pg_get_object_address()
reports. Of the 66 pairs, 62 reach this error:
* REL_19_STABLE without the patch: 18 of those 62 are published
according to pg_publication_tables, and all 18 get the plain
message.
* master with the patch: the detail appears on exactly those 18 and
on none of the other 44.
* A cheaper test, looking only at the publication kind (FOR ALL TABLES,
or the relation's schema in the publication), which would have
avoided exporting anything, is wrong in 18 of the 62: it would add
the detail for views, sequences, unlogged tables, partitions of a
table in the EXCEPT clause, and whichever of a partitioned table or
its partitions publish_via_partition_root leaves out, and it would
miss partitions published through their ancestor.
The remaining 4 pairs are the two tables that do have an entry and the
two EXCEPT tables, which now get the message added by 94670ba6d56.
The patch adds tests to object_address for the schema case, the
partition-through-ancestor case, and a relation that is not published.
They use FOR TABLES IN SCHEMA and FOR TABLE rather than FOR ALL TABLES,
because a FOR ALL TABLES publication in the regression database disturbs
the tests running in parallel with it. It applies to master at
bca67e5a33b, builds without warnings, and make check passes.
I did not touch the docs. The pg_get_object_address() entry does not
describe any object type on its own, so a sentence about this one might
not belong there; if you think it does, or know a better place, I will
add it.
Two things I am not sure about, and would rather ask than guess:
1. Is a detail the right weight for this, or would you rather the
message itself were reworded?
2. Is exporting is_table_publishable_in_publication() for a message
acceptable, or would you rather keep it static and have the message
be less precise?
[1]
https://postgr.es/m/caa4ek1++nmjbd4aiahih6n8kx01-mecc87kpmz_bbvk_qtd...@mail.gmail.com
[2]
https://postgr.es/m/CAJpy0uCp+43rgbPE9v8Zy0FXq=nssizosbaf0nnaeztk65x...@mail.gmail.com
Regards,
Manu
From 5d94d2685b7db01df866e21512e60fd20069a4a3 Mon Sep 17 00:00:00 2001 From: Manu <[email protected]> Date: Wed, 16 Sep 2026 05:37:06 -0300 Subject: [PATCH] Explain why a published relation has no publication relation address A table published through FOR ALL TABLES, FOR TABLES IN SCHEMA, or a partitioned ancestor has no pg_publication_rel entry of its own, so get_object_address_publication_rel() finds nothing and reports ERROR: publication relation "t1" in publication "pub" does not exist which reads as "that table is not published", while pg_publication_tables lists the very same table as published by the very same publication. The address really does not exist, but the message is on its own in saying why, and a user comparing it with the catalog has no way to reconcile the two. Add a detail saying that the table is published without an entry of its own. It is emitted only when the table really is published, as decided by is_table_publishable_in_publication(), the same test that pg_get_publication_tables() applies when filtering by relation; that function is exported for this purpose. A cheaper test on the publication kind alone would add the detail for relations that are not published at all: views, sequences, unlogged tables, the partitions of a table in the EXCEPT clause, and whichever of a partitioned table or its partitions publish_via_partition_root leaves out. It would also miss partitions published through their ancestor. Reported-by: Peter Smith --- src/backend/catalog/objectaddress.c | 29 ++++++++++++++++--- src/backend/catalog/pg_publication.c | 2 +- src/include/catalog/pg_publication.h | 1 + src/test/regress/expected/object_address.out | 30 ++++++++++++++++++++ src/test/regress/sql/object_address.sql | 22 ++++++++++++++ 5 files changed, 79 insertions(+), 5 deletions(-) diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index d3eee732062..83b9e5e9f38 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -1926,10 +1926,31 @@ get_object_address_publication_rel(ObjectType objtype, List *object, errmsg("publication excluded relation \"%s\" in publication \"%s\" does not exist", RelationGetRelationName(relation), pubname))); else - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("publication relation \"%s\" in publication \"%s\" does not exist", - RelationGetRelationName(relation), pubname))); + { + /* + * A table can be published without a pg_publication_rel + * entry of its own: through FOR ALL TABLES, FOR TABLES IN + * SCHEMA, or a partitioned ancestor. Say so, since otherwise + * the message flatly contradicts pg_publication_tables, which + * does list the table. Sequences are left out: they are + * never published as tables. + */ + if (is_publishable_relation(relation) && + relation->rd_rel->relkind != RELKIND_SEQUENCE && + is_table_publishable_in_publication(RelationGetRelid(relation), + pub)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication relation \"%s\" in publication \"%s\" does not exist", + RelationGetRelationName(relation), pubname), + errdetail("Table \"%s\" is published by publication \"%s\" without an entry of its own, through FOR ALL TABLES, FOR TABLES IN SCHEMA, or a partitioned ancestor.", + RelationGetRelationName(relation), pubname))); + else + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication relation \"%s\" in publication \"%s\" does not exist", + RelationGetRelationName(relation), pubname))); + } } relation_close(relation, AccessShareLock); return address; diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 6b752c4c738..9e7bfb479b6 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -1361,7 +1361,7 @@ GetPublicationByName(const char *pubname, bool missing_ok) * Note: this leaks memory for the ancestors list into the current memory * context. */ -static bool +bool is_table_publishable_in_publication(Oid relid, Publication *pub) { bool relispartition; diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h index 5d1e6c54a85..315ece9927e 100644 --- a/src/include/catalog/pg_publication.h +++ b/src/include/catalog/pg_publication.h @@ -197,6 +197,7 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors, extern bool is_publishable_relation(Relation rel); extern bool is_schema_publication(Oid pubid); extern bool is_table_publication(Oid pubid); +extern bool is_table_publishable_in_publication(Oid relid, Publication *pub); extern bool check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt, Bitmapset **cols); extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri, diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out index 101bf3cdfac..9219eb2aa0e 100644 --- a/src/test/regress/expected/object_address.out +++ b/src/test/regress/expected/object_address.out @@ -664,3 +664,33 @@ ORDER BY objects.classid, objects.objid, objects.objsubid; ("(""parameter ACL"",,,)")|("(""parameter ACL"",,)")|NULL -- restore normal output mode \a\t +-- A table published through FOR TABLES IN SCHEMA or through a partitioned +-- ancestor has no pg_publication_rel entry of its own, so it has no object +-- address of this kind even though it is published. Check that the message +-- says so, and that it stays quiet for a relation that really is not +-- published. (FOR ALL TABLES behaves the same way, but such a publication +-- would disturb the tests running in parallel with this one.) +CREATE SCHEMA addr_pub_nsp; +CREATE TABLE addr_pub_nsp.tbl (a int); +CREATE TABLE addr_pub_nsp.unpublished (a int); +CREATE PUBLICATION addr_pub_sch FOR TABLES IN SCHEMA addr_pub_nsp; +SELECT pg_get_object_address('publication relation', + '{addr_pub_nsp, tbl}', '{addr_pub_sch}'); +ERROR: publication relation "tbl" in publication "addr_pub_sch" does not exist +DETAIL: Table "tbl" is published by publication "addr_pub_sch" without an entry of its own, through FOR ALL TABLES, FOR TABLES IN SCHEMA, or a partitioned ancestor. +CREATE TABLE addr_pub_nsp.parted (a int) PARTITION BY RANGE (a); +CREATE TABLE addr_pub_nsp.part1 PARTITION OF addr_pub_nsp.parted FOR VALUES FROM (0) TO (10); +CREATE PUBLICATION addr_pub_one FOR TABLE addr_pub_nsp.tbl, addr_pub_nsp.parted; +SELECT pg_get_object_address('publication relation', + '{addr_pub_nsp, part1}', '{addr_pub_one}'); +ERROR: publication relation "part1" in publication "addr_pub_one" does not exist +DETAIL: Table "part1" is published by publication "addr_pub_one" without an entry of its own, through FOR ALL TABLES, FOR TABLES IN SCHEMA, or a partitioned ancestor. +SELECT pg_get_object_address('publication relation', + '{addr_pub_nsp, unpublished}', '{addr_pub_one}'); +ERROR: publication relation "unpublished" in publication "addr_pub_one" does not exist +DROP PUBLICATION addr_pub_sch, addr_pub_one; +DROP SCHEMA addr_pub_nsp CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table addr_pub_nsp.tbl +drop cascades to table addr_pub_nsp.unpublished +drop cascades to table addr_pub_nsp.parted diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql index 3e4638b905b..f94453572a6 100644 --- a/src/test/regress/sql/object_address.sql +++ b/src/test/regress/sql/object_address.sql @@ -317,3 +317,25 @@ ORDER BY objects.classid, objects.objid, objects.objsubid; -- restore normal output mode \a\t + +-- A table published through FOR TABLES IN SCHEMA or through a partitioned +-- ancestor has no pg_publication_rel entry of its own, so it has no object +-- address of this kind even though it is published. Check that the message +-- says so, and that it stays quiet for a relation that really is not +-- published. (FOR ALL TABLES behaves the same way, but such a publication +-- would disturb the tests running in parallel with this one.) +CREATE SCHEMA addr_pub_nsp; +CREATE TABLE addr_pub_nsp.tbl (a int); +CREATE TABLE addr_pub_nsp.unpublished (a int); +CREATE PUBLICATION addr_pub_sch FOR TABLES IN SCHEMA addr_pub_nsp; +SELECT pg_get_object_address('publication relation', + '{addr_pub_nsp, tbl}', '{addr_pub_sch}'); +CREATE TABLE addr_pub_nsp.parted (a int) PARTITION BY RANGE (a); +CREATE TABLE addr_pub_nsp.part1 PARTITION OF addr_pub_nsp.parted FOR VALUES FROM (0) TO (10); +CREATE PUBLICATION addr_pub_one FOR TABLE addr_pub_nsp.tbl, addr_pub_nsp.parted; +SELECT pg_get_object_address('publication relation', + '{addr_pub_nsp, part1}', '{addr_pub_one}'); +SELECT pg_get_object_address('publication relation', + '{addr_pub_nsp, unpublished}', '{addr_pub_one}'); +DROP PUBLICATION addr_pub_sch, addr_pub_one; +DROP SCHEMA addr_pub_nsp CASCADE; -- 2.55.0
pubrel_detail_matrix.sql
Description: application/sql
