1-- 2-- Test for pg_get_object_address 3-- 4 5-- Clean up in case a prior regression run failed 6SET client_min_messages TO 'warning'; 7DROP ROLE IF EXISTS regress_addr_user; 8RESET client_min_messages; 9 10CREATE USER regress_addr_user; 11 12-- Test generic object addressing/identification functions 13CREATE SCHEMA addr_nsp; 14SET search_path TO 'addr_nsp'; 15CREATE FOREIGN DATA WRAPPER addr_fdw; 16CREATE SERVER addr_fserv FOREIGN DATA WRAPPER addr_fdw; 17CREATE TEXT SEARCH DICTIONARY addr_ts_dict (template=simple); 18CREATE TEXT SEARCH CONFIGURATION addr_ts_conf (copy=english); 19CREATE TEXT SEARCH TEMPLATE addr_ts_temp (lexize=dsimple_lexize); 20CREATE TEXT SEARCH PARSER addr_ts_prs 21 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 22CREATE TABLE addr_nsp.gentable ( 23 a serial primary key CONSTRAINT a_chk CHECK (a > 0), 24 b text DEFAULT 'hello'); 25CREATE TABLE addr_nsp.parttable ( 26 a int PRIMARY KEY 27) PARTITION BY RANGE (a); 28CREATE VIEW addr_nsp.genview AS SELECT * from addr_nsp.gentable; 29CREATE MATERIALIZED VIEW addr_nsp.genmatview AS SELECT * FROM addr_nsp.gentable; 30CREATE TYPE addr_nsp.gencomptype AS (a int); 31CREATE TYPE addr_nsp.genenum AS ENUM ('one', 'two'); 32CREATE FOREIGN TABLE addr_nsp.genftable (a int) SERVER addr_fserv; 33CREATE AGGREGATE addr_nsp.genaggr(int4) (sfunc = int4pl, stype = int4); 34CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0); 35CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$; 36CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig(); 37CREATE POLICY genpol ON addr_nsp.gentable; 38CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$; 39CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw; 40CREATE USER MAPPING FOR regress_addr_user SERVER "integer"; 41ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user; 42ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM regress_addr_user; 43-- this transform would be quite unsafe to leave lying around, 44-- except that the SQL language pays no attention to transforms: 45CREATE TRANSFORM FOR int LANGUAGE SQL ( 46 FROM SQL WITH FUNCTION prsd_lextype(internal), 47 TO SQL WITH FUNCTION int4recv(internal)); 48-- suppress warning that depends on wal_level 49SET client_min_messages = 'ERROR'; 50CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable; 51RESET client_min_messages; 52CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE); 53CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable; 54 55-- test some error cases 56SELECT pg_get_object_address('stone', '{}', '{}'); 57SELECT pg_get_object_address('table', '{}', '{}'); 58SELECT pg_get_object_address('table', '{NULL}', '{}'); 59 60-- unrecognized object types 61DO $$ 62DECLARE 63 objtype text; 64BEGIN 65 FOR objtype IN VALUES ('toast table'), ('index column'), ('sequence column'), 66 ('toast table column'), ('view column'), ('materialized view column') 67 LOOP 68 BEGIN 69 PERFORM pg_get_object_address(objtype, '{one}', '{}'); 70 EXCEPTION WHEN invalid_parameter_value THEN 71 RAISE WARNING 'error for %: %', objtype, sqlerrm; 72 END; 73 END LOOP; 74END; 75$$; 76 77-- miscellaneous other errors 78select * from pg_get_object_address('operator of access method', '{btree,integer_ops,1}', '{int4,bool}'); 79select * from pg_get_object_address('operator of access method', '{btree,integer_ops,99}', '{int4,int4}'); 80select * from pg_get_object_address('function of access method', '{btree,integer_ops,1}', '{int4,bool}'); 81select * from pg_get_object_address('function of access method', '{btree,integer_ops,99}', '{int4,int4}'); 82 83DO $$ 84DECLARE 85 objtype text; 86 names text[]; 87 args text[]; 88BEGIN 89 FOR objtype IN VALUES 90 ('table'), ('index'), ('sequence'), ('view'), 91 ('materialized view'), ('foreign table'), 92 ('table column'), ('foreign table column'), 93 ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'), 94 ('table constraint'), ('domain constraint'), ('conversion'), ('default value'), 95 ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'), 96 ('text search parser'), ('text search dictionary'), 97 ('text search template'), ('text search configuration'), 98 ('policy'), ('user mapping'), ('default acl'), ('transform'), 99 ('operator of access method'), ('function of access method'), 100 ('publication relation') 101 LOOP 102 FOR names IN VALUES ('{eins}'), ('{addr_nsp, zwei}'), ('{eins, zwei, drei}') 103 LOOP 104 FOR args IN VALUES ('{}'), ('{integer}') 105 LOOP 106 BEGIN 107 PERFORM pg_get_object_address(objtype, names, args); 108 EXCEPTION WHEN OTHERS THEN 109 RAISE WARNING 'error for %,%,%: %', objtype, names, args, sqlerrm; 110 END; 111 END LOOP; 112 END LOOP; 113 END LOOP; 114END; 115$$; 116 117-- these object types cannot be qualified names 118SELECT pg_get_object_address('language', '{one}', '{}'); 119SELECT pg_get_object_address('language', '{one,two}', '{}'); 120SELECT pg_get_object_address('large object', '{123}', '{}'); 121SELECT pg_get_object_address('large object', '{123,456}', '{}'); 122SELECT pg_get_object_address('large object', '{blargh}', '{}'); 123SELECT pg_get_object_address('schema', '{one}', '{}'); 124SELECT pg_get_object_address('schema', '{one,two}', '{}'); 125SELECT pg_get_object_address('role', '{one}', '{}'); 126SELECT pg_get_object_address('role', '{one,two}', '{}'); 127SELECT pg_get_object_address('database', '{one}', '{}'); 128SELECT pg_get_object_address('database', '{one,two}', '{}'); 129SELECT pg_get_object_address('tablespace', '{one}', '{}'); 130SELECT pg_get_object_address('tablespace', '{one,two}', '{}'); 131SELECT pg_get_object_address('foreign-data wrapper', '{one}', '{}'); 132SELECT pg_get_object_address('foreign-data wrapper', '{one,two}', '{}'); 133SELECT pg_get_object_address('server', '{one}', '{}'); 134SELECT pg_get_object_address('server', '{one,two}', '{}'); 135SELECT pg_get_object_address('extension', '{one}', '{}'); 136SELECT pg_get_object_address('extension', '{one,two}', '{}'); 137SELECT pg_get_object_address('event trigger', '{one}', '{}'); 138SELECT pg_get_object_address('event trigger', '{one,two}', '{}'); 139SELECT pg_get_object_address('access method', '{one}', '{}'); 140SELECT pg_get_object_address('access method', '{one,two}', '{}'); 141SELECT pg_get_object_address('publication', '{one}', '{}'); 142SELECT pg_get_object_address('publication', '{one,two}', '{}'); 143SELECT pg_get_object_address('subscription', '{one}', '{}'); 144SELECT pg_get_object_address('subscription', '{one,two}', '{}'); 145 146-- test successful cases 147WITH objects (type, name, args) AS (VALUES 148 ('table', '{addr_nsp, gentable}'::text[], '{}'::text[]), 149 ('table', '{addr_nsp, parttable}'::text[], '{}'::text[]), 150 ('index', '{addr_nsp, gentable_pkey}', '{}'), 151 ('index', '{addr_nsp, parttable_pkey}', '{}'), 152 ('sequence', '{addr_nsp, gentable_a_seq}', '{}'), 153 -- toast table 154 ('view', '{addr_nsp, genview}', '{}'), 155 ('materialized view', '{addr_nsp, genmatview}', '{}'), 156 ('foreign table', '{addr_nsp, genftable}', '{}'), 157 ('table column', '{addr_nsp, gentable, b}', '{}'), 158 ('foreign table column', '{addr_nsp, genftable, a}', '{}'), 159 ('aggregate', '{addr_nsp, genaggr}', '{int4}'), 160 ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'), 161 ('procedure', '{addr_nsp, proc}', '{int4}'), 162 ('type', '{pg_catalog._int4}', '{}'), 163 ('type', '{addr_nsp.gendomain}', '{}'), 164 ('type', '{addr_nsp.gencomptype}', '{}'), 165 ('type', '{addr_nsp.genenum}', '{}'), 166 ('cast', '{int8}', '{int4}'), 167 ('collation', '{default}', '{}'), 168 ('table constraint', '{addr_nsp, gentable, a_chk}', '{}'), 169 ('domain constraint', '{addr_nsp.gendomain}', '{domconstr}'), 170 ('conversion', '{pg_catalog, koi8_r_to_mic}', '{}'), 171 ('default value', '{addr_nsp, gentable, b}', '{}'), 172 ('language', '{plpgsql}', '{}'), 173 -- large object 174 ('operator', '{+}', '{int4, int4}'), 175 ('operator class', '{btree, int4_ops}', '{}'), 176 ('operator family', '{btree, integer_ops}', '{}'), 177 ('operator of access method', '{btree,integer_ops,1}', '{integer,integer}'), 178 ('function of access method', '{btree,integer_ops,2}', '{integer,integer}'), 179 ('rule', '{addr_nsp, genview, _RETURN}', '{}'), 180 ('trigger', '{addr_nsp, gentable, t}', '{}'), 181 ('schema', '{addr_nsp}', '{}'), 182 ('text search parser', '{addr_ts_prs}', '{}'), 183 ('text search dictionary', '{addr_ts_dict}', '{}'), 184 ('text search template', '{addr_ts_temp}', '{}'), 185 ('text search configuration', '{addr_ts_conf}', '{}'), 186 ('role', '{regress_addr_user}', '{}'), 187 -- database 188 -- tablespace 189 ('foreign-data wrapper', '{addr_fdw}', '{}'), 190 ('server', '{addr_fserv}', '{}'), 191 ('user mapping', '{regress_addr_user}', '{integer}'), 192 ('default acl', '{regress_addr_user,public}', '{r}'), 193 ('default acl', '{regress_addr_user}', '{r}'), 194 -- extension 195 -- event trigger 196 ('policy', '{addr_nsp, gentable, genpol}', '{}'), 197 ('transform', '{int}', '{sql}'), 198 ('access method', '{btree}', '{}'), 199 ('publication', '{addr_pub}', '{}'), 200 ('publication relation', '{addr_nsp, gentable}', '{addr_pub}'), 201 ('subscription', '{regress_addr_sub}', '{}'), 202 ('statistics object', '{addr_nsp, gentable_stat}', '{}') 203 ) 204SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, 205 -- test roundtrip through pg_identify_object_as_address 206 ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)) = 207 ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.objsubid)) 208 FROM objects, pg_get_object_address(type, name, args) addr1, 209 pg_identify_object_as_address(classid, objid, objsubid) ioa(typ,nms,args), 210 pg_get_object_address(typ, nms, ioa.args) as addr2 211 ORDER BY addr1.classid, addr1.objid, addr1.objsubid; 212 213--- 214--- Cleanup resources 215--- 216DROP FOREIGN DATA WRAPPER addr_fdw CASCADE; 217DROP PUBLICATION addr_pub; 218DROP SUBSCRIPTION regress_addr_sub; 219 220DROP SCHEMA addr_nsp CASCADE; 221 222DROP OWNED BY regress_addr_user; 223DROP USER regress_addr_user; 224 225-- 226-- Checks for invalid objects 227-- 228-- Make sure that NULL handling is correct. 229\pset null 'NULL' 230-- Temporarily disable fancy output, so as future additions never create 231-- a large amount of diffs. 232\a\t 233 234-- Keep this list in the same order as getObjectIdentityParts() 235-- in objectaddress.c. 236WITH objects (classid, objid, objsubid) AS (VALUES 237 ('pg_class'::regclass, 0, 0), -- no relation 238 ('pg_class'::regclass, 'pg_class'::regclass, 100), -- no column for relation 239 ('pg_proc'::regclass, 0, 0), -- no function 240 ('pg_type'::regclass, 0, 0), -- no type 241 ('pg_cast'::regclass, 0, 0), -- no cast 242 ('pg_collation'::regclass, 0, 0), -- no collation 243 ('pg_constraint'::regclass, 0, 0), -- no constraint 244 ('pg_conversion'::regclass, 0, 0), -- no conversion 245 ('pg_attrdef'::regclass, 0, 0), -- no default attribute 246 ('pg_language'::regclass, 0, 0), -- no language 247 ('pg_largeobject'::regclass, 0, 0), -- no large object, no error 248 ('pg_operator'::regclass, 0, 0), -- no operator 249 ('pg_opclass'::regclass, 0, 0), -- no opclass, no need to check for no access method 250 ('pg_opfamily'::regclass, 0, 0), -- no opfamily 251 ('pg_am'::regclass, 0, 0), -- no access method 252 ('pg_amop'::regclass, 0, 0), -- no AM operator 253 ('pg_amproc'::regclass, 0, 0), -- no AM proc 254 ('pg_rewrite'::regclass, 0, 0), -- no rewrite 255 ('pg_trigger'::regclass, 0, 0), -- no trigger 256 ('pg_namespace'::regclass, 0, 0), -- no schema 257 ('pg_statistic_ext'::regclass, 0, 0), -- no statistics 258 ('pg_ts_parser'::regclass, 0, 0), -- no TS parser 259 ('pg_ts_dict'::regclass, 0, 0), -- no TS dictionnary 260 ('pg_ts_template'::regclass, 0, 0), -- no TS template 261 ('pg_ts_config'::regclass, 0, 0), -- no TS configuration 262 ('pg_authid'::regclass, 0, 0), -- no role 263 ('pg_database'::regclass, 0, 0), -- no database 264 ('pg_tablespace'::regclass, 0, 0), -- no tablespace 265 ('pg_foreign_data_wrapper'::regclass, 0, 0), -- no FDW 266 ('pg_foreign_server'::regclass, 0, 0), -- no server 267 ('pg_user_mapping'::regclass, 0, 0), -- no user mapping 268 ('pg_default_acl'::regclass, 0, 0), -- no default ACL 269 ('pg_extension'::regclass, 0, 0), -- no extension 270 ('pg_event_trigger'::regclass, 0, 0), -- no event trigger 271 ('pg_policy'::regclass, 0, 0), -- no policy 272 ('pg_publication'::regclass, 0, 0), -- no publication 273 ('pg_publication_rel'::regclass, 0, 0), -- no publication relation 274 ('pg_subscription'::regclass, 0, 0), -- no subscription 275 ('pg_transform'::regclass, 0, 0) -- no transformation 276 ) 277SELECT ROW(pg_identify_object(objects.classid, objects.objid, objects.objsubid)) 278 AS ident, 279 ROW(pg_identify_object_as_address(objects.classid, objects.objid, objects.objsubid)) 280 AS addr, 281 pg_describe_object(objects.classid, objects.objid, objects.objsubid) 282 AS descr 283FROM objects 284ORDER BY objects.classid, objects.objid, objects.objsubid; 285 286-- restore normal output mode 287\a\t 288