pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_get_object_address reports a published relation as non-existent
4+ messages / 3 participants
[nested] [flat]
* pg_get_object_address reports a published relation as non-existent
@ 2026-09-17 14:21 Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Manuel Reyes Bravo @ 2026-09-17 14:21 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; +Cc: Amit Kapila <amit.kapila16@gmail.com>; Peter Smith <smithpb2250@gmail.com>; shveta malik <shveta.malik@gmail.com>
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_qTdmqA@mail.gmail.com
[2] https://postgr.es/m/CAJpy0uCp+43rgbPE9v8Zy0FXq=nssizOsbaF0NNaezTK65XVYQ@mail.gmail.com
Regards,
Manu
Attachments:
[text/x-patch] 0001-Explain-why-a-published-relation-has-no-publication-.patch (9.1K, ../../CA+bCEdAUGBdCtGdvMgi3sc74+Ddvc00si62RwW1zB96DhLbsPQ@mail.gmail.com/2-0001-Explain-why-a-published-relation-has-no-publication-.patch)
download | inline diff:
From 5d94d2685b7db01df866e21512e60fd20069a4a3 Mon Sep 17 00:00:00 2001
From: Manu <manuelreyesbravo@gmail.com>
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
[application/sql] pubrel_detail_matrix.sql (4.0K, ../../CA+bCEdAUGBdCtGdvMgi3sc74+Ddvc00si62RwW1zB96DhLbsPQ@mail.gmail.com/3-pubrel_detail_matrix.sql)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_get_object_address reports a published relation as non-existent
@ 2026-09-17 14:57 Álvaro Herrera <alvherre@kurilemu.de>
parent: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Álvaro Herrera @ 2026-09-17 14:57 UTC (permalink / raw)
To: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Amit Kapila <amit.kapila16@gmail.com>; Peter Smith <smithpb2250@gmail.com>; shveta malik <shveta.malik@gmail.com>
On 2026-Sep-17, Manuel Reyes Bravo wrote:
> 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
I'm not sure this is a valid complaint. pg_publication_tables is a
user-friendly view, so there's no reason for pg_get_object_address() to
react to values obtained from there, I think.
The docs for pg_get_object_address say:
Returns a row containing enough information to uniquely identify the
database object specified by a type code and object name and argument
arrays. The returned values are the ones that would be used in system
catalogs such as pg_depend; they can be passed to other system
functions such as pg_describe_object or pg_identify_object. classid is
the OID of the system catalog containing the object; objid is the OID
of the object itself, and objsubid is the sub-object ID, or zero if
none. This function is the inverse of pg_identify_object_as_address.
Undefined objects are identified with NULL values.
Now if you look in pg_depend after creating the publication FOR ALL
TABLES, you can see this:
alvherre=# select objid, (pg_identify_object(classid, objid, objsubid)).*, deptype, (pg_identify_object(refclassid, refobjid, refobjsubid)).* from pg_depend where objid > 16000;
objid | type | schema | name | identity | deptype | type | schema | name | identity
-------+-------+--------+------+-------------+---------+--------+--------+--------+-----------
16400 | type | public | _t1 | public.t1[] | i | type | public | t1 | public.t1
16401 | type | public | t1 | public.t1 | i | table | public | t1 | public.t1
16399 | table | public | t1 | public.t1 | n | schema | | public | public
No representation is visible for the table being in the publication. If
you drop that publication and create it for that table specifically,
instead you get
alvherre=# drop publication pub ;
DROP PUBLICATION
alvherre=# create publication pub for table t1;
CREATE PUBLICATION
alvherre=# select objid, (pg_identify_object(classid, objid, objsubid)).*, deptype, (pg_identify_object(refclassid, refobjid, refobjsubid)).* from pg_depend where objid > 16000;
objid | type | schema | name | identity | deptype | type | schema | name | identity
-------+----------------------+--------+------+------------------------------+---------+-------------+--------+--------+-----------
16400 | type | public | _t1 | public.t1[] | i | type | public | t1 | public.t1
16401 | type | public | t1 | public.t1 | i | table | public | t1 | public.t1
16399 | table | public | t1 | public.t1 | n | schema | | public | public
16407 | publication relation | | | public.t1 in publication pub | a | publication | | pub | pub
16407 | publication relation | | | public.t1 in publication pub | a | table | public | t1 | public.t1
Have a look at how src/test/regress/sql/object_address.sql sets up for
roundtripping these things ...
Does that make sense?
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"La verdad no siempre es bonita, pero el hambre de ella sí"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_get_object_address reports a published relation as non-existent
@ 2026-09-17 15:07 Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
parent: Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 0 replies; 4+ messages in thread
From: Manuel Reyes Bravo @ 2026-09-17 15:07 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Amit Kapila <amit.kapila16@gmail.com>; Peter Smith <smithpb2250@gmail.com>; shveta malik <shveta.malik@gmail.com>
Hi Álvaro,
Yes, it does. The address names a catalog row, not a (table,
publication) pair: FOR TABLE gives a pg_publication_rel row, FOR TABLES
IN SCHEMA gives a pg_publication_namespace row, which is how
object_address.sql round-trips it ('publication namespace', '{addr_nsp}',
'{addr_pub_schema}'), and FOR ALL TABLES gives nothing beyond the
publication itself, as pg_depend shows. So nothing is missing, and
comparing with pg_publication_tables was the wrong frame.
I withdraw the patch.
Regards,
Manu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_get_object_address reports a published relation as non-existent
@ 2026-09-17 22:50 Peter Smith <smithpb2250@gmail.com>
parent: Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 0 replies; 4+ messages in thread
From: Peter Smith @ 2026-09-17 22:50 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Amit Kapila <amit.kapila16@gmail.com>; shveta malik <shveta.malik@gmail.com>
On Fri, Sep 18, 2026 at 12:57 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2026-Sep-17, Manuel Reyes Bravo wrote:
>
> > 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
>
> I'm not sure this is a valid complaint. pg_publication_tables is a
> user-friendly view, so there's no reason for pg_get_object_address() to
> react to values obtained from there, I think.
>
My original post [1] was not comparing to the view
pg_publication_tables, or expecting anything to work. It was simply an
observation that the pg_get_object_address() ERROR message was
misleading by saying the relation is not a member of a publication,
when clearly it is.
e.g.
pg_get_object_address('publication relation','{public,t1}','{pub}');
ERROR: publication relation "t1" in publication "pub" does not exist
vs.
\d+ public.t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain |
| |
Included in publications:
"pub"
Access method: heap
~~~
So, I'd imagined a trivial fix to give more context for the message, like:
ERROR: publication relation "t1" in publication "pub" does not exist
in system catalog "pg_publication_rel".
(If it is not permitted for messages to expose system catalog names
like that, then some other minor rewording of the original error would
be OK too ).
======
[1] https://www.postgresql.org/message-id/CAHut%2BPvbNtMyWYyH7XO3MP%3DFwTfG%3DYyyDJTgrs9rd1fePqQ-Zw%40ma...
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-17 22:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-17 14:21 pg_get_object_address reports a published relation as non-existent Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
2026-09-17 14:57 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-09-17 15:07 ` Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
2026-09-17 22:50 ` Peter Smith <smithpb2250@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox