From: Chao Li <li.evan.chao@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_61D2D67E-1346-4A09-B5AA-E8A43D5E87C5"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Fix unqualified catalog references in psql describe queries
Message-Id: <C809E3C7-2437-486E-B626-0BE5F40BBB6C@gmail.com>
Date: Mon, 8 Jun 2026 16:46:46 +0800
Cc: Fujii Masao <masao.fujii@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 Jeff Davis <pgsql@j-davis.com>,
 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/C809E3C7-2437-486E-B626-0BE5F40BBB6C%40gmail.com>
Precedence: bulk


--Apple-Mail=_61D2D67E-1346-4A09-B5AA-E8A43D5E87C5
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hi,

While testing "[aecc55866] psql: Show comments in \dRp+, \dRs+, and =
\dX+", I noticed a small issue that was actually introduced by =
"[8185bb534] CREATE SUBSCRIPTION =E2=80=A6 SERVER=E2=80=9D.

The problem is that, when querying pg_foreign_server, it misses the =
"pg_catalog" schema qualification:
```
			appendPQExpBuffer(&buf,
							  ", (select =
srvname from pg_foreign_server where oid=3Dsubserver) AS \"%s\"\n",
							  =
gettext_noop("Server"));
```

This is not a big problem, but it provides a way to pollute the result =
of \dRs+ by adding a fake pg_foreign_server earlier in search_path. See =
this repro:

1. Setup: create a server and a sub
```
evantest=3D# create extension postgres_fdw;
CREATE EXTENSION
evantest=3D# create publication pub;
CREATE PUBLICATION
evantest=3D# create server s foreign data wrapper postgres_fdw options =
(dbname 'postgres');
CREATE SERVER
evantest=3D# create user mapping for current_user server s;
CREATE USER MAPPING
evantest=3D# create subscription sub server s publication pub with =
(connect=3Dfalse, slot_name=3Dnone);
WARNING:  subscription was created, but is not connected
HINT:  To initiate replication, you must manually create the replication =
slot, enable the subscription, and alter the subscription to refresh =
publications.
CREATE SUBSCRIPTION
evantest=3D# \dRs+ sub;
                                                                         =
                                                                     =
List of subscriptions
 Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase =
commit | Disable on error | Origin | Password required | Run as owner? | =
Failover | Server | Retain dead tuples | Max retention duration | =
Retention active | Synchronous commit | Conninfo | Receiver timeout |  =
Skip LSN  | Description
=
------+-------+---------+-------------+--------+-----------+--------------=
----+------------------+--------+-------------------+---------------+-----=
-----+--------+--------------------+------------------------+-------------=
-----+--------------------+----------+------------------+------------+----=
---------
 sub  | chaol | f       | {pub}       | f      | parallel  | d           =
     | f                | any    | t                 | f             | f =
       | s      | f                  |                      0 | f        =
        | off                |          | -1               | 0/00000000 =
|
(1 row)
```

As shown above, =E2=80=9CServer=E2=80=9D column shows the correct server =
name =E2=80=9Cs=E2=80=9D.

2. Now, pollute the result
```
evantest=3D# create temp table pg_foreign_server (oid oid, srvname =
name);
CREATE TABLE
evantest=3D# insert into pg_foreign_server select oid, 'fake_s'::name =
from pg_catalog.pg_foreign_server where srvname=3D's';
INSERT 0 1
evantest=3D# \dRs+ sub;
                                                                         =
                                                                     =
List of subscriptions
 Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase =
commit | Disable on error | Origin | Password required | Run as owner? | =
Failover | Server | Retain dead tuples | Max retention duration | =
Retention active | Synchronous commit | Conninfo | Receiver timeout |  =
Skip LSN  | Description
=
------+-------+---------+-------------+--------+-----------+--------------=
----+------------------+--------+-------------------+---------------+-----=
-----+--------+--------------------+------------------------+-------------=
-----+--------------------+----------+------------------+------------+----=
---------
 sub  | chaol | f       | {pub}       | f      | parallel  | d           =
     | f                | any    | t                 | f             | f =
       | fake_s | f                  |                      0 | f        =
        | off                |          | -1               | 0/00000000 =
|
(1 row)
```

Now, the "Server" column shows the fake server name that I supplied.

The fix is to add the schema qualification, using =
"pg_catalog.pg_foreign_server". In describe.c, catalog objects are =
generally referenced by qualified names. I found 3 other occurrences =
that missed schema qualification, so I fixed them as well.

There are 4 spots in total. Two are v19-new, oversights of =
8185bb53476378443240d57f7d844347d5fae1bf and =
2f094e7ac691abc9d2fe0f4dcf0feac4a6ce1d9c. The other two are older and =
might be worth back-patching. So I split the fix into 2 commits: 0001 is =
v19-new, and 0002 is a back-patch candidate.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_61D2D67E-1346-4A09-B5AA-E8A43D5E87C5
Content-Disposition: attachment;
	filename=v1-0001-psql-Schema-qualify-catalog-references-in-describ.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-psql-Schema-qualify-catalog-references-in-describ.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=2007876f501725a48d75b6da492b24dbda7fd066ec=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Mon,=208=20Jun=202026=2016:09:07=20+0800=0ASubject:=20[PATCH=20=
v1=201/2]=20psql:=20Schema-qualify=20catalog=20references=20in=20=
describe=0A=20queries=0A=0ASome=20recently=20added=20describe=20queries=20=
missed=20pg_catalog=20qualification=20for=0Acatalog=20relations.=20This=20=
could=20allow=20user=20objects=20earlier=20in=20search_path,=0Aincluding=20=
temporary=20tables,=20to=20affect=20psql=20describe=20output.=0A=0AFix=20=
the=20subscription=20describe=20query=20to=20use=20=
pg_catalog.pg_foreign_server,=0Aand=20fix=20the=20property=20graph=20=
describe=20query=20to=20use=20pg_catalog-qualified=0Acatalog=20=
relations.=0A=0AThese=20were=20oversights=20in=20commits=208185bb53=20=
and=202f094e7a.=0A=0AAuthor:=20Chao=20Li=20<lic@highgo.com>=0A---=0A=20=
src/bin/psql/describe.c=20|=2012=20++++++------=0A=201=20file=20changed,=20=
6=20insertions(+),=206=20deletions(-)=0A=0Adiff=20--git=20=
a/src/bin/psql/describe.c=20b/src/bin/psql/describe.c=0Aindex=20=
e1449654f96..a245a0a7912=20100644=0A---=20a/src/bin/psql/describe.c=0A=
+++=20b/src/bin/psql/describe.c=0A@@=20-1950,11=20+1950,11=20@@=20=
describeOneTableDetails(const=20char=20*schemaname,=0A=20=09=09=09=09=09=09=
=20=20"\n=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
when=20"=20CppAsString2(PGEKIND_EDGE)=20"=20then=20'edge'=20end=20AS=20=
\"%s\","=0A=20=09=09=09=09=09=09=20=20"\n=20=20=20=20=20s.pgealias=20as=20=
\"%s\","=0A=20=09=09=09=09=09=09=20=20"\n=20=20=20=20=20d.pgealias=20as=20=
\"%s\""=0A-=09=09=09=09=09=09=20=20"\n=20FROM=20pg_propgraph_element=20=
e"=0A-=09=09=09=09=09=09=20=20"\n=20=20=20=20=20=20INNER=20JOIN=20=
pg_class=20c=20ON=20c.oid=20=3D=20e.pgerelid"=0A-=09=09=09=09=09=09=20=20=
"\n=20=20=20=20=20=20INNER=20JOIN=20pg_namespace=20n=20ON=20=
c.relnamespace=20=3D=20n.oid"=0A-=09=09=09=09=09=09=20=20"\n=20=20=20=20=20=
=20LEFT=20JOIN=20pg_propgraph_element=20s=20ON=20e.pgesrcvertexid=20=3D=20=
s.oid"=0A-=09=09=09=09=09=09=20=20"\n=20=20=20=20=20=20LEFT=20JOIN=20=
pg_propgraph_element=20d=20ON=20e.pgedestvertexid=20=3D=20d.oid"=0A+=09=09=
=09=09=09=09=20=20"\n=20FROM=20pg_catalog.pg_propgraph_element=20e"=0A+=09=
=09=09=09=09=09=20=20"\n=20=20=20=20=20=20INNER=20JOIN=20=
pg_catalog.pg_class=20c=20ON=20c.oid=20=3D=20e.pgerelid"=0A+=09=09=09=09=09=
=09=20=20"\n=20=20=20=20=20=20INNER=20JOIN=20pg_catalog.pg_namespace=20n=20=
ON=20c.relnamespace=20=3D=20n.oid"=0A+=09=09=09=09=09=09=20=20"\n=20=20=20=
=20=20=20LEFT=20JOIN=20pg_catalog.pg_propgraph_element=20s=20ON=20=
e.pgesrcvertexid=20=3D=20s.oid"=0A+=09=09=09=09=09=09=20=20"\n=20=20=20=20=
=20=20LEFT=20JOIN=20pg_catalog.pg_propgraph_element=20d=20ON=20=
e.pgedestvertexid=20=3D=20d.oid"=0A=20=09=09=09=09=09=09=20=20"\n=20=
WHERE=20e.pgepgid=20=3D=20'%s'"=0A=20=09=09=09=09=09=09=20=20"\n=20ORDER=20=
BY=20e.pgealias",=0A=20=09=09=09=09=09=09=20=20gettext_noop("Element=20=
Alias"),=0A@@=20-7165,7=20+7165,7=20@@=20describeSubscriptions(const=20=
char=20*pattern,=20bool=20verbose)=0A=20=09=09if=20(pset.sversion=20>=3D=20=
190000)=0A=20=09=09{=0A=20=09=09=09appendPQExpBuffer(&buf,=0A-=09=09=09=09=
=09=09=09=20=20",=20(select=20srvname=20from=20pg_foreign_server=20where=20=
oid=3Dsubserver)=20AS=20\"%s\"\n",=0A+=09=09=09=09=09=09=09=20=20",=20=
(select=20srvname=20from=20pg_catalog.pg_foreign_server=20where=20=
oid=3Dsubserver)=20AS=20\"%s\"\n",=0A=20=09=09=09=09=09=09=09=20=20=
gettext_noop("Server"));=0A=20=0A=20=09=09=09appendPQExpBuffer(&buf,=0A=
--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A=

--Apple-Mail=_61D2D67E-1346-4A09-B5AA-E8A43D5E87C5
Content-Disposition: attachment;
	filename=v1-0002-psql-Schema-qualify-pg_get_expr-in-publication-de.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0002-psql-Schema-qualify-pg_get_expr-in-publication-de.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=20bb58dcb7ce8386dcff84efde6a7feab7bac0eaa8=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Mon,=208=20Jun=202026=2016:11:51=20+0800=0ASubject:=20[PATCH=20=
v1=202/2]=20psql:=20Schema-qualify=20pg_get_expr()=20in=20publication=0A=20=
describe=20queries=0A=0AThe=20publication=20describe=20queries=20used=20=
unqualified=20pg_get_expr()=20calls=20when=0Ashowing=20row=20filters.=20=
A=20user-defined=20function=20earlier=20in=20search_path=20could=0A=
therefore=20affect=20psql=20describe=20output.=0A=0AUse=20=
pg_catalog.pg_get_expr(),=20as=20nearby=20describe=20queries=20already=20=
do.=0A=0AAuthor:=20Chao=20Li=20<lic@highgo.com>=0A---=0A=20=
src/bin/psql/describe.c=20|=204=20++--=0A=201=20file=20changed,=202=20=
insertions(+),=202=20deletions(-)=0A=0Adiff=20--git=20=
a/src/bin/psql/describe.c=20b/src/bin/psql/describe.c=0Aindex=20=
a245a0a7912..015586b9895=20100644=0A---=20a/src/bin/psql/describe.c=0A=
+++=20b/src/bin/psql/describe.c=0A@@=20-3189,7=20+3189,7=20@@=20=
describeOneTableDetails(const=20char=20*schemaname,=0A=20=09=09=09=09=09=09=
=09=09=20=20"WHERE=20pc.oid=20=3D'%s'=20and=20=
pg_catalog.pg_relation_is_publishable('%s')\n"=0A=20=09=09=09=09=09=09=09=
=09=20=20"UNION\n"=0A=20=09=09=09=09=09=09=09=09=20=20"SELECT=20=
pubname\n"=0A-=09=09=09=09=09=09=09=09=20=20"=20=20=20=20=20,=20=
pg_get_expr(pr.prqual,=20c.oid)\n"=0A+=09=09=09=09=09=09=09=09=20=20"=20=20=
=20=20=20,=20pg_catalog.pg_get_expr(pr.prqual,=20c.oid)\n"=0A=20=09=09=09=
=09=09=09=09=09=20=20"=20=20=20=20=20,=20(CASE=20WHEN=20pr.prattrs=20IS=20=
NOT=20NULL=20THEN\n"=0A=20=09=09=09=09=09=09=09=09=20=20"=20=20=20=20=20=20=
=20=20=20(SELECT=20string_agg(attname,=20',=20')\n"=0A=20=09=09=09=09=09=09=
=09=09=20=20"=20=20=20=20=20=20=20=20=20=20=20FROM=20=
pg_catalog.generate_series(0,=20=
pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[],=201))=20s,\n"=0A@@=20=
-6995,7=20+6995,7=20@@=20describePublications(const=20char=20*pattern)=0A=
=20=09=09=09if=20(pset.sversion=20>=3D=20150000)=0A=20=09=09=09{=0A=20=09=
=09=09=09appendPQExpBufferStr(&buf,=0A-=09=09=09=09=09=09=09=09=09=20",=20=
pg_get_expr(pr.prqual,=20c.oid)");=0A+=09=09=09=09=09=09=09=09=09=20",=20=
pg_catalog.pg_get_expr(pr.prqual,=20c.oid)");=0A=20=09=09=09=09=
appendPQExpBufferStr(&buf,=0A=20=09=09=09=09=09=09=09=09=09=20",=20(CASE=20=
WHEN=20pr.prattrs=20IS=20NOT=20NULL=20THEN\n"=0A=20=09=09=09=09=09=09=09=09=
=09=20"=20=20=20=20=20pg_catalog.array_to_string("=0A--=20=0A2.50.1=20=
(Apple=20Git-155)=0A=0A=

--Apple-Mail=_61D2D67E-1346-4A09-B5AA-E8A43D5E87C5--