From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | kimber7(at)illinois(dot)edu |
Subject: | BUG #17634: Inconsistent view_definition in information_schema.views |
Date: | 2022-10-11 13:32:03 |
Message-ID: | 17634-fd5cb524a79c1df9@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17634
Logged by: Kimberly Blum
Email address: kimber7(at)illinois(dot)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)));
From | Date | Subject | |
---|---|---|---|
Next Message | Japin Li | 2022-10-11 14:35:50 | Re: BUG #17633: Define rule on views which do insert to another relation trigger cache lookup failed error. |
Previous Message | Richard Guo | 2022-10-11 13:16:54 | Re: BUG #17633: Define rule on views which do insert to another relation trigger cache lookup failed error. |