Обсуждение: BUG #17634: Inconsistent view_definition in information_schema.views

The following bug has been logged on the website:

Bug reference:      17634
Logged by:          Kimberly Blum
Email address:      kimber7@illinois.edu
PostgreSQL version: 14.5
Operating system:   RHEL 8
Description:

I use information_schema.views (the view_definition column) in code that
provides dependency information for functions and procedures. All objects in
my code are schema qualified, and when I look for dependencies, I rely on
that qualification. 

If I run my query against information_schema.views while my session is
active in a schema that is referenced in my query, information_schema.views
strips out that schema name. All other objects remain schema qualified.

My example query: 
SELECT view_definition FROM information_schema.views WHERE table_schema =
'appcode' AND table_name = 'cfg_msg_v';

The results from first query, which was run from schema api (which is not
referenced in the code), and all schema qualification shows up. Note all the
areas where appcfg.<obj> is shown.
query: select view_definition from information_schema.views where 
 SELECT cfg_msg.id_msg,
    cfg_msg.id_src,
    cfg_src.src_name,
    cfg_msg.msg_type,
    cfg_msg.msg_version,
    cfg_msg.id_class,
    cfg_cls class_name,
    cfg_msg.descript
   FROM ((appcfg.cfg_msg cfg_msg
     JOIN appcfg.cfg_data_src cfg_src ON (((cfg_msg.id_src)::integer =
(cfg_src.id_src)::integer)))
     JOIN cmdb.cfg_class cfg_cls ON (((cfg_msg.id_class)::integer =
(cfg_cls.id_class)::integer)));

If I switch my session to the appcfg schema, and run the same query, this is
the result (appcfg qualification disappears, other schema qualification is
fine). 
 SELECT cfg_msg.id_msg,
    cfg_msg.id_src,
    cfg_src.src_name,
    cfg_msg.msg_type,
    cfg_msg.msg_version,
    cfg_msg.id_class,
    cfg_cls.class_name,
    cfg_msg.descript
   FROM ((cfg_msg cfg_msg
     JOIN cfg_data_src cfg_src ON (((cfg_msg.id_src)::integer =
(cfg_src.id_src)::integer)))
     JOIN cmdb.cfg_class cfg_cls ON (((cfg_msg.id_class)::integer =
(cfg_cls.id_class)::integer)));

PG Bug reporting form <noreply@postgresql.org> writes:
> If I run my query against information_schema.views while my session is
> active in a schema that is referenced in my query, information_schema.views
> strips out that schema name. All other objects remain schema qualified.

This is not a bug, it's normal behavior of the rule decompiler:
object names are qualified only if necessary (where "necessary"
means "would not be found in a search_path search").

If you don't like it, set search_path to empty.

            regards, tom lane

Then perhaps the rule decompiler should be used when populating a column that implies it contains the view definition -
becauseit is not the correct definition. No snark intended, just my thoughts. On a related note, having dependencies
forall object (including functions and procedures) available in postgres would be awesome. You all provide a great
productfor us opensource users, and it's appreciated. 

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, October 11, 2022 9:58 AM
To: Blum, Kimber <kimber7@illinois.edu>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17634: Inconsistent view_definition in information_schema.views

PG Bug reporting form <noreply@postgresql.org> writes:
> If I run my query against information_schema.views while my session is
> active in a schema that is referenced in my query,
> information_schema.views strips out that schema name. All other objects remain schema qualified.

This is not a bug, it's normal behavior of the rule decompiler:
object names are qualified only if necessary (where "necessary"
means "would not be found in a search_path search").

If you don't like it, set search_path to empty.

            regards, tom lane