Feedback-ID: ie3de48e3:Fastmail
Date: Thu, 17 Sep 2026 16:57:22 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
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>
Subject: Re: pg_get_object_address reports a published relation as
 non-existent
Message-ID: <aqv6UJp69o-0Uo3M@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+bCEdAUGBdCtGdvMgi3sc74+Ddvc00si62RwW1zB96DhLbsPQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aqv6UJp69o-0Uo3M%40alvherre.pgsql>
Precedence: bulk

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