How to get info about relations between system tables?
One my customer asked me "what tables holds references to table pg_roles"?
The reply is simple - none. pg_roles is view. But this view uses very important table pg_authid. For custom tables we can use a query:
SELECT conname, conrelid::pg_catalog.regclass AS ontable,
pg_catalog.pg_get_constraintdef(oid, true) AS condef
FROM pg_catalog.pg_constraint c
WHERE confrelid = 'a'::pg_catalog.regclass
AND contype = 'f'
ORDER BY conname;
┌─────────────┬─────────┬─────────────────────────────────────┐
│ conname │ ontable │ condef │
╞═════════════╪═════════╪═════════════════════════════════════╡
│ b_a_id_fkey │ b │ FOREIGN KEY (a_id) REFERENCES a(id) │
└─────────────┴─────────┴─────────────────────────────────────┘
(1 row)
But this method doesn't work for system tables. These tables doesn't use explicitly defined foreign keys.
We should to use different method. The system function pg_get_catalog_foreign_keys returns all referencies between system tables, and we can filter result:
SELECT *
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass;
┌─────────────────────────┬──────────────┬───────────┬────────┬──────────┬────────┐
│ fktable │ fkcols │ pktable │ pkcols │ is_array │ is_opt │
╞═════════════════════════╪══════════════╪═══════════╪════════╪══════════╪════════╡
│ pg_proc │ {proowner} │ pg_authid │ {oid} │ f │ f │
│ pg_type │ {typowner} │ pg_authid │ {oid} │ f │ f │
│ pg_class │ {relowner} │ pg_authid │ {oid} │ f │ f │
│ pg_operator │ {oprowner} │ pg_authid │ {oid} │ f │ f │
│ pg_opfamily │ {opfowner} │ pg_authid │ {oid} │ f │ f │
│ pg_opclass │ {opcowner} │ pg_authid │ {oid} │ f │ f │
│ pg_language │ {lanowner} │ pg_authid │ {oid} │ f │ f │
│ pg_largeobject_metadata │ {lomowner} │ pg_authid │ {oid} │ f │ f │
│ pg_statistic_ext │ {stxowner} │ pg_authid │ {oid} │ f │ f │
│ pg_event_trigger │ {evtowner} │ pg_authid │ {oid} │ f │ f │
│ pg_namespace │ {nspowner} │ pg_authid │ {oid} │ f │ f │
│ pg_conversion │ {conowner} │ pg_authid │ {oid} │ f │ f │
│ pg_database │ {datdba} │ pg_authid │ {oid} │ f │ f │
│ pg_db_role_setting │ {setrole} │ pg_authid │ {oid} │ f │ t │
│ pg_tablespace │ {spcowner} │ pg_authid │ {oid} │ f │ f │
│ pg_auth_members │ {roleid} │ pg_authid │ {oid} │ f │ f │
│ pg_auth_members │ {member} │ pg_authid │ {oid} │ f │ f │
│ pg_auth_members │ {grantor} │ pg_authid │ {oid} │ f │ f │
│ pg_ts_config │ {cfgowner} │ pg_authid │ {oid} │ f │ f │
│ pg_ts_dict │ {dictowner} │ pg_authid │ {oid} │ f │ f │
│ pg_extension │ {extowner} │ pg_authid │ {oid} │ f │ f │
│ pg_foreign_data_wrapper │ {fdwowner} │ pg_authid │ {oid} │ f │ f │
│ pg_foreign_server │ {srvowner} │ pg_authid │ {oid} │ f │ f │
│ pg_user_mapping │ {umuser} │ pg_authid │ {oid} │ f │ t │
│ pg_policy │ {polroles} │ pg_authid │ {oid} │ t │ t │
│ pg_default_acl │ {defaclrole} │ pg_authid │ {oid} │ f │ f │
│ pg_collation │ {collowner} │ pg_authid │ {oid} │ f │ f │
│ pg_publication │ {pubowner} │ pg_authid │ {oid} │ f │ f │
│ pg_subscription │ {subowner} │ pg_authid │ {oid} │ f │ f │
│ pg_variable │ {varowner} │ pg_authid │ {oid} │ f │ f │
└─────────────────────────┴──────────────┴───────────┴────────┴──────────┴────────┘
(30 rows)
posted by Pavel Stěhule @ 11:52 PM
1 Comments
