Thread: BUG #17634: Inconsistent view_definition in information_schema.views
BUG #17634: Inconsistent view_definition in information_schema.views
From
PG Bug reporting form
Date:
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