pg_get_object_address reports a published relation as non-existent
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:t253825psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 17, 2026 at 02:33 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 t253825_1 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 t253825_1 && git checkout t253825_1Patchset v1 (message #1) is on t253825_1
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]/messages/by-id/CAA4eK1++NMJbD4aiahiH6N8kx01-MEcC87KPMZ_BBvK_qTdmqA@mail.gmail.com, and shveta noted that it happens with TABLES IN
SCHEMA too and that no documentation describes it [2]/messages/by-id/CAJpy0uCp+43rgbPE9v8Zy0FXq=nssizOsbaF0NNaezTK65XVYQ@mail.gmail.com. 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]: /messages/by-id/CAA4eK1++NMJbD4aiahiH6N8kx01-MEcC87KPMZ_BBvK_qTdmqA@mail.gmail.com
[2]: /messages/by-id/CAJpy0uCp+43rgbPE9v8Zy0FXq=nssizOsbaF0NNaezTK65XVYQ@mail.gmail.com
Regards,
Manu
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 | t1SELECT 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í"
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