On Tue, 15 Sept 2026 at 10:07, shveta malik <[email protected]> wrote: > > On Sun, Sep 13, 2026 at 9:01 PM vignesh C <[email protected]> wrote: > > > > > > I preferred the single lookup approach. The > > v1-0002-Review-comment-fixes.patch at [1] has the changes for the > > same. > > Thanks Vignesh. > > Instead of isPublicationRelationExcept() with the exclusion logic > outside and the fallback logic inside -- which is also not very clear: > > + /* fallback to a non-exclusion entry for an undefined object */ > + return false; > > I think we could introduce a function similar to > getProcedureTypeDescription() and getConstraintTypeDescription(). > Please see the attached patch. Take the changes if you agree.
Your suggestion looks better, I have merged the proposed changes with a couple of minor changes a) changed function name getPublicationRelationDescription to getPublicationRelationTypeDescription. This will keep it consistent by having Type in the function name similar to getConstraintTypeDescription, getProcedureTypeDescription and getRelationTypeDescription. b) Changed "failed for publication relation" to "failed for publication table" so that it is consistent with other search sys cache failures of PUBLICATIONREL. The attached v2 version patch has the changes for the same. This also addresses Hou's comments from [1]. [1] - https://www.postgresql.org/message-id/TY4PR01MB1771860516F5316ECB8CD275494BB2%40TY4PR01MB17718.jpnprd01.prod.outlook.com Regards, Vignesh
From 70ddb19b9febba934722642c11f37c04cf7bee79 Mon Sep 17 00:00:00 2001 From: Fujii Masao <[email protected]> Date: Thu, 6 Aug 2026 11:29:47 +0900 Subject: [PATCH v2] Distinguish publication exclusions in object addresses pg_publication_rel entries can represent either explicitly published relations or tables excluded from FOR ALL TABLES publications. However, object address code treated all pg_publication_rel entries as publication relations, without checking prexcept. That made EXCEPT entries look like ordinary published-table mappings in dependency messages, pg_identify_object(), and pg_identify_object_as_address(). Fix this by distinguishing EXCEPT entries in the object address code. They are now reported as publication excluded relation, with object identities indicating that the table is excluded from the publication. Also accept "publication excluded relation" as an object type in pg_get_object_address(), so the output of pg_identify_object_as_address() continues to round-trip. "publication relation" resolves only non-EXCEPT entries, while "publication excluded relation" resolves only EXCEPT entries. --- src/backend/catalog/aclchk.c | 2 + src/backend/catalog/objectaddress.c | 129 ++++++++++++++++--- src/backend/commands/dropcmds.c | 1 + src/backend/commands/event_trigger.c | 2 + src/backend/commands/seclabel.c | 1 + src/include/nodes/parsenodes.h | 1 + src/test/regress/expected/object_address.out | 17 +++ src/test/regress/expected/publication.out | 29 +++++ src/test/regress/sql/object_address.sql | 17 +++ src/test/regress/sql/publication.sql | 24 ++++ 10 files changed, 203 insertions(+), 20 deletions(-) diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index e67358f3858..3243ec4c2a5 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -2767,6 +2767,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_DOMCONSTRAINT: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_ROLE: case OBJECT_RULE: case OBJECT_TABCONSTRAINT: @@ -2908,6 +2909,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_PARAMETER_ACL: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_ROLE: case OBJECT_TRANSFORM: case OBJECT_TSPARSER: diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index b08e076e65f..5b3aadf81f9 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -823,6 +823,9 @@ static const struct object_type_map { "publication relation", OBJECT_PUBLICATION_REL }, + { + "publication excluded relation", OBJECT_PUBLICATION_EXCLUDED_REL + }, { "subscription", OBJECT_SUBSCRIPTION }, @@ -865,7 +868,8 @@ static ObjectAddress get_object_address_usermapping(List *object, bool missing_ok); static ObjectAddress get_object_address_publication_rel(List *object, Relation *relp, - bool missing_ok); + bool missing_ok, + bool pubrel_is_exclusion); static ObjectAddress get_object_address_publication_schema(List *object, bool missing_ok); static ObjectAddress get_object_address_defacl(List *object, @@ -880,6 +884,9 @@ static void getRelationTypeDescription(StringInfo buffer, Oid relid, int32 objectSubId, bool missing_ok); static void getProcedureTypeDescription(StringInfo buffer, Oid procid, bool missing_ok); +static void getPublicationRelationTypeDescription(StringInfo buffer, + Oid pubreloid, + bool missing_ok); static void getConstraintTypeDescription(StringInfo buffer, Oid constroid, bool missing_ok); static void getOpFamilyIdentity(StringInfo buffer, Oid opfid, List **object, @@ -1113,9 +1120,11 @@ get_object_address(ObjectType objtype, Node *object, missing_ok); break; case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: address = get_object_address_publication_rel(castNode(List, object), &relation, - missing_ok); + missing_ok, + objtype == OBJECT_PUBLICATION_EXCLUDED_REL); break; case OBJECT_DEFACL: address = get_object_address_defacl(castNode(List, object), @@ -1860,19 +1869,21 @@ get_object_address_usermapping(List *object, bool missing_ok) } /* - * Find the ObjectAddress for a publication relation. The first element of - * the object parameter is the relation name, the second is the + * Find the ObjectAddress for a publication relation or exclusion. The first + * element of the object parameter is the relation name, the second is the * publication name. */ static ObjectAddress get_object_address_publication_rel(List *object, - Relation *relp, bool missing_ok) + Relation *relp, bool missing_ok, + bool pubrel_is_exclusion) { ObjectAddress address; Relation relation; List *relname; char *pubname; Publication *pub; + HeapTuple tup; ObjectAddressSet(address, PublicationRelRelationId, InvalidOid); @@ -1893,23 +1904,49 @@ get_object_address_publication_rel(List *object, return address; } - /* Find the publication relation mapping in syscache. */ - address.objectId = - GetSysCacheOid2(PUBLICATIONRELMAP, Anum_pg_publication_rel_oid, - ObjectIdGetDatum(RelationGetRelid(relation)), - ObjectIdGetDatum(pub->oid)); - if (!OidIsValid(address.objectId)) + /* + * Find the publication relation mapping in syscache. Fetch the tuple + * rather than just its OID, so that prexcept can be checked without a + * second lookup. A missing entry, or one of the other kind, falls + * through to the not-found handling below. + */ + tup = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(RelationGetRelid(relation)), + ObjectIdGetDatum(pub->oid)); + if (HeapTupleIsValid(tup)) { - if (!missing_ok) + /* Found row in pg_publication_rel */ + Form_pg_publication_rel prform = + (Form_pg_publication_rel) GETSTRUCT(tup); + Oid pubreloid = prform->oid; + bool isexcept = prform->prexcept; + + ReleaseSysCache(tup); + + /* Treat a prexcept mismatch as not found. */ + if (isexcept == pubrel_is_exclusion) + { + address.objectId = pubreloid; + *relp = relation; + return address; + } + } + + if (!missing_ok) + { + if (pubrel_is_exclusion) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication excluded relation \"%s\" from 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))); - relation_close(relation, AccessShareLock); - return address; } - *relp = relation; + relation_close(relation, AccessShareLock); return address; } @@ -2236,6 +2273,7 @@ pg_get_object_address(PG_FUNCTION_ARGS) case OBJECT_DOMCONSTRAINT: case OBJECT_CAST: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_DEFACL: case OBJECT_TRANSFORM: if (list_length(args) != 1) @@ -2327,6 +2365,7 @@ pg_get_object_address(PG_FUNCTION_ARGS) objnode = (Node *) list_make2(typename, linitial(args)); break; case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: objnode = (Node *) list_make2(name, linitial(args)); break; case OBJECT_PUBLICATION_NAMESPACE: @@ -2556,6 +2595,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, case OBJECT_DEFACL: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_USER_MAPPING: /* These are currently not supported or don't make sense here. */ elog(ERROR, "unsupported object type: %d", (int) objtype); @@ -4020,9 +4060,18 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok) initStringInfo(&rel); getRelationDescription(&rel, prform->prrelid, false); - /* translator: first %s is, e.g., "table %s" */ - appendStringInfo(&buffer, _("publication of %s in publication %s"), - rel.data, pubname); + if (prform->prexcept) + { + /* translator: first %s is, e.g., "table %s" */ + appendStringInfo(&buffer, _("exclusion of %s from publication %s"), + rel.data, pubname); + } + else + { + /* translator: first %s is, e.g., "table %s" */ + appendStringInfo(&buffer, _("publication of %s in publication %s"), + rel.data, pubname); + } pfree(rel.data); ReleaseSysCache(tup); break; @@ -4654,7 +4703,8 @@ getObjectTypeDescription(const ObjectAddress *object, bool missing_ok) break; case PublicationRelRelationId: - appendStringInfoString(&buffer, "publication relation"); + getPublicationRelationTypeDescription(&buffer, object->objectId, + missing_ok); break; case SubscriptionRelationId: @@ -4808,6 +4858,41 @@ getProcedureTypeDescription(StringInfo buffer, Oid procid, ReleaseSysCache(procTup); } +/* + * subroutine for getObjectTypeDescription: describe a publication relation + * + * Appends "publication excluded relation" for EXCEPT entries, or + * "publication relation" for published relations, to buffer. + */ +static void +getPublicationRelationTypeDescription(StringInfo buffer, Oid pubreloid, + bool missing_ok) +{ + HeapTuple tup; + Form_pg_publication_rel prform; + + tup = SearchSysCache1(PUBLICATIONREL, ObjectIdGetDatum(pubreloid)); + if (!HeapTupleIsValid(tup)) + { + if (!missing_ok) + elog(ERROR, "cache lookup failed for publication table %u", + pubreloid); + + /* fallback to "publication relation" for an undefined object */ + appendStringInfoString(buffer, "publication relation"); + return; + } + + prform = (Form_pg_publication_rel) GETSTRUCT(tup); + + if (prform->prexcept) + appendStringInfoString(buffer, "publication excluded relation"); + else + appendStringInfoString(buffer, "publication relation"); + + ReleaseSysCache(tup); +} + /* * Obtain a given object's identity, as a palloc'ed string. * @@ -5949,7 +6034,11 @@ getObjectIdentityParts(const ObjectAddress *object, pubname = get_publication_name(prform->prpubid, false); getRelationIdentity(&buffer, prform->prrelid, objname, false); - appendStringInfo(&buffer, " in publication %s", pubname); + if (prform->prexcept) + appendStringInfo(&buffer, " excluded from publication %s", + pubname); + else + appendStringInfo(&buffer, " in publication %s", pubname); if (objargs) *objargs = list_make1(pubname); diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c index 92526012d2a..ddddde3b965 100644 --- a/src/backend/commands/dropcmds.c +++ b/src/backend/commands/dropcmds.c @@ -506,6 +506,7 @@ does_not_exist_skipping(ObjectType objtype, Node *object) case OBJECT_PARAMETER_ACL: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_TABCONSTRAINT: case OBJECT_USER_MAPPING: /* These are currently not used or needed. */ diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index d868c7f42c3..2d666e0e2c5 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -2318,6 +2318,7 @@ stringify_grant_objtype(ObjectType objtype) case OBJECT_PUBLICATION: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_ROLE: case OBJECT_RULE: case OBJECT_STATISTIC_EXT: @@ -2402,6 +2403,7 @@ stringify_adefprivs_objtype(ObjectType objtype) case OBJECT_PUBLICATION: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_ROLE: case OBJECT_RULE: case OBJECT_STATISTIC_EXT: diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c index 5b80396723c..ff21aaca036 100644 --- a/src/backend/commands/seclabel.c +++ b/src/backend/commands/seclabel.c @@ -82,6 +82,7 @@ SecLabelSupportsObjectType(ObjectType objtype) case OBJECT_POLICY: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: + case OBJECT_PUBLICATION_EXCLUDED_REL: case OBJECT_RULE: case OBJECT_STATISTIC_EXT: case OBJECT_TABCONSTRAINT: diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 910eef936f1..8eb3ab89fc6 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2416,6 +2416,7 @@ typedef enum ObjectType OBJECT_POLICY, OBJECT_PROCEDURE, OBJECT_PUBLICATION, + OBJECT_PUBLICATION_EXCLUDED_REL, OBJECT_PUBLICATION_NAMESPACE, OBJECT_PUBLICATION_REL, OBJECT_ROLE, diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out index 4c266d7fa67..1ac9c0cc387 100644 --- a/src/test/regress/expected/object_address.out +++ b/src/test/regress/expected/object_address.out @@ -509,6 +509,23 @@ subscription|NULL|regress_addr_sub|regress_addr_sub|t publication|NULL|addr_pub|addr_pub|t publication relation|NULL|NULL|addr_nsp.gentable in publication addr_pub|t publication namespace|NULL|NULL|addr_nsp in publication addr_pub_schema|t +-- A FOR ALL TABLES publication is listed by \d for every table in the +-- database, which would disturb the other tests running concurrently in this +-- parallel group. Create it in a transaction that is rolled back, so that it +-- is never visible to another session. +BEGIN; +SET LOCAL client_min_messages = 'ERROR'; +CREATE PUBLICATION addr_pub_except FOR ALL TABLES EXCEPT (TABLE addr_nsp.gentable); +SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, + ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)) = + ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.objsubid)) AS roundtrip +FROM pg_get_object_address('publication excluded relation', + '{addr_nsp, gentable}', + '{addr_pub_except}') AS addr1, + pg_identify_object_as_address(classid, objid, objsubid) AS ioa (typ, nms, args), + pg_get_object_address(typ, nms, ioa.args) AS addr2; +publication excluded relation|NULL|NULL|addr_nsp.gentable excluded from publication addr_pub_except|t +ROLLBACK; --- --- Cleanup resources --- diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index fe602c4538e..534b0f3f8af 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -274,6 +274,35 @@ Excluded from publications: "testpub_foralltables_excepttable" "testpub_foralltables_excepttable1" +-- Check object address handling for an EXCEPT entry. +\a\t +SELECT (pg_identify_object('pg_publication_rel'::regclass, pr.oid, 0)).* +FROM pg_publication_rel pr +JOIN pg_publication p ON p.oid = pr.prpubid +JOIN pg_class c ON c.oid = pr.prrelid +WHERE p.pubname = 'testpub_foralltables_excepttable1' + AND c.relname = 'testpub_tbl1'; +publication excluded relation|||public.testpub_tbl1 excluded from publication testpub_foralltables_excepttable1 +SELECT pg_get_object_address('publication excluded relation', + '{public, testpub_tbl1}', '{testpub_fortable}'); +ERROR: publication excluded relation "testpub_tbl1" from publication "testpub_fortable" does not exist +SELECT pg_get_object_address('publication relation', + '{public, testpub_tbl1}', + '{testpub_foralltables_excepttable1}'); +ERROR: publication relation "testpub_tbl1" in publication "testpub_foralltables_excepttable1" does not exist +-- Check pg_describe_object output for both included and excluded entries +CREATE PUBLICATION testpub_describe FOR TABLE testpub_tbl1; +SELECT p.pubname, + pg_describe_object('pg_publication_rel'::regclass, pr.oid, 0) AS description, + pr.prexcept +FROM pg_publication_rel pr +JOIN pg_publication p ON p.oid = pr.prpubid +WHERE p.pubname IN ('testpub_describe', 'testpub_foralltables_excepttable1') +ORDER BY p.pubname; +testpub_describe|publication of table testpub_tbl1 in publication testpub_describe|f +testpub_foralltables_excepttable1|exclusion of table testpub_tbl1 from publication testpub_foralltables_excepttable1|t +DROP PUBLICATION testpub_describe; +\a\t -- fail - first table in the EXCEPT list should use TABLE keyword CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2); ERROR: syntax error at or near "testpub_tbl1" diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql index 653a53038e3..518442bb641 100644 --- a/src/test/regress/sql/object_address.sql +++ b/src/test/regress/sql/object_address.sql @@ -225,6 +225,23 @@ FROM objects, pg_get_object_address(typ, nms, ioa.args) AS addr2 ORDER BY addr1.classid, addr1.objid, addr1.objsubid; +-- A FOR ALL TABLES publication is listed by \d for every table in the +-- database, which would disturb the other tests running concurrently in this +-- parallel group. Create it in a transaction that is rolled back, so that it +-- is never visible to another session. +BEGIN; +SET LOCAL client_min_messages = 'ERROR'; +CREATE PUBLICATION addr_pub_except FOR ALL TABLES EXCEPT (TABLE addr_nsp.gentable); +SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, + ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)) = + ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.objsubid)) AS roundtrip +FROM pg_get_object_address('publication excluded relation', + '{addr_nsp, gentable}', + '{addr_pub_except}') AS addr1, + pg_identify_object_as_address(classid, objid, objsubid) AS ioa (typ, nms, args), + pg_get_object_address(typ, nms, ioa.args) AS addr2; +ROLLBACK; + --- --- Cleanup resources --- diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 80c244c9138..2e4bf749149 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -123,6 +123,30 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABL -- Check that the table description shows the publications where it is listed -- in the EXCEPT clause \d testpub_tbl1 +-- Check object address handling for an EXCEPT entry. +\a\t +SELECT (pg_identify_object('pg_publication_rel'::regclass, pr.oid, 0)).* +FROM pg_publication_rel pr +JOIN pg_publication p ON p.oid = pr.prpubid +JOIN pg_class c ON c.oid = pr.prrelid +WHERE p.pubname = 'testpub_foralltables_excepttable1' + AND c.relname = 'testpub_tbl1'; +SELECT pg_get_object_address('publication excluded relation', + '{public, testpub_tbl1}', '{testpub_fortable}'); +SELECT pg_get_object_address('publication relation', + '{public, testpub_tbl1}', + '{testpub_foralltables_excepttable1}'); +-- Check pg_describe_object output for both included and excluded entries +CREATE PUBLICATION testpub_describe FOR TABLE testpub_tbl1; +SELECT p.pubname, + pg_describe_object('pg_publication_rel'::regclass, pr.oid, 0) AS description, + pr.prexcept +FROM pg_publication_rel pr +JOIN pg_publication p ON p.oid = pr.prpubid +WHERE p.pubname IN ('testpub_describe', 'testpub_foralltables_excepttable1') +ORDER BY p.pubname; +DROP PUBLICATION testpub_describe; +\a\t -- fail - first table in the EXCEPT list should use TABLE keyword CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2); -- 2.55.0
