Re: pg_get_object_address reports a published relation as non-existent

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í"