| blob | 388097a69578709c9ff07147a68451a7abf22d14 |
1 --
2 -- Test for pg_get_object_address
3 --
4 -- Clean up in case a prior regression run failed
5 SET client_min_messages TO 'warning';
6 DROP ROLE IF EXISTS regress_addr_user;
7 RESET client_min_messages;
8 CREATE USER regress_addr_user;
9 -- Test generic object addressing/identification functions
10 CREATE SCHEMA addr_nsp;
11 SET search_path TO 'addr_nsp';
12 CREATE FOREIGN DATA WRAPPER addr_fdw;
13 CREATE SERVER addr_fserv FOREIGN DATA WRAPPER addr_fdw;
14 CREATE TEXT SEARCH DICTIONARY addr_ts_dict (template=simple);
15 CREATE TEXT SEARCH CONFIGURATION addr_ts_conf (copy=english);
16 CREATE TEXT SEARCH TEMPLATE addr_ts_temp (lexize=dsimple_lexize);
17 CREATE TEXT SEARCH PARSER addr_ts_prs
18 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
19 CREATE TABLE addr_nsp.gentable (
20 a serial primary key CONSTRAINT a_chk CHECK (a > 0),
21 b text DEFAULT 'hello');
22 CREATE TABLE addr_nsp.parttable (
23 a int PRIMARY KEY
24 ) PARTITION BY RANGE (a);
25 CREATE VIEW addr_nsp.genview AS SELECT * from addr_nsp.gentable;
26 CREATE MATERIALIZED VIEW addr_nsp.genmatview AS SELECT * FROM addr_nsp.gentable;
27 CREATE TYPE addr_nsp.gencomptype AS (a int);
28 CREATE TYPE addr_nsp.genenum AS ENUM ('one', 'two');
29 CREATE FOREIGN TABLE addr_nsp.genftable (a int) SERVER addr_fserv;
30 CREATE AGGREGATE addr_nsp.genaggr(int4) (sfunc = int4pl, stype = int4);
31 CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0);
32 CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$;
33 CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig();
34 CREATE POLICY genpol ON addr_nsp.gentable;
35 CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$;
36 CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw;
37 CREATE USER MAPPING FOR regress_addr_user SERVER "integer";
38 ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user;
39 ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM regress_addr_user;
40 -- this transform would be quite unsafe to leave lying around,
41 -- except that the SQL language pays no attention to transforms:
42 CREATE TRANSFORM FOR int LANGUAGE SQL (
43 FROM SQL WITH FUNCTION prsd_lextype(internal),
44 TO SQL WITH FUNCTION int4recv(internal));
45 -- suppress warning that depends on wal_level
46 SET client_min_messages = 'ERROR';
47 CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
48 RESET client_min_messages;
49 CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
50 WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
51 CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
52 -- test some error cases
53 SELECT pg_get_object_address('stone', '{}', '{}');
54 ERROR: unrecognized object type "stone"
55 SELECT pg_get_object_address('table', '{}', '{}');
56 ERROR: name list length must be at least 1
57 SELECT pg_get_object_address('table', '{NULL}', '{}');
58 ERROR: name or argument lists may not contain nulls
59 -- unrecognized object types
60 DO $$
61 DECLARE
62 objtype text;
63 BEGIN
64 FOR objtype IN VALUES ('toast table'), ('index column'), ('sequence column'),
65 ('toast table column'), ('view column'), ('materialized view column')
66 LOOP
67 BEGIN
68 PERFORM pg_get_object_address(objtype, '{one}', '{}');
69 EXCEPTION WHEN invalid_parameter_value THEN
70 RAISE WARNING 'error for %: %', objtype, sqlerrm;
71 END;
72 END LOOP;
73 END;
74 $$;
75 WARNING: error for toast table: unsupported object type "toast table"
76 WARNING: error for index column: unsupported object type "index column"
77 WARNING: error for sequence column: unsupported object type "sequence column"
78 WARNING: error for toast table column: unsupported object type "toast table column"
79 WARNING: error for view column: unsupported object type "view column"
80 WARNING: error for materialized view column: unsupported object type "materialized view column"
81 -- miscellaneous other errors
82 select * from pg_get_object_address('operator of access method', '{btree,integer_ops,1}', '{int4,bool}');
83 ERROR: operator 1 (int4, bool) of operator family integer_ops for access method btree does not exist
84 select * from pg_get_object_address('operator of access method', '{btree,integer_ops,99}', '{int4,int4}');
85 ERROR: operator 99 (int4, int4) of operator family integer_ops for access method btree does not exist
86 select * from pg_get_object_address('function of access method', '{btree,integer_ops,1}', '{int4,bool}');
87 ERROR: function 1 (int4, bool) of operator family integer_ops for access method btree does not exist
88 select * from pg_get_object_address('function of access method', '{btree,integer_ops,99}', '{int4,int4}');
89 ERROR: function 99 (int4, int4) of operator family integer_ops for access method btree does not exist
90 DO $$
91 DECLARE
92 objtype text;
93 names text[];
94 args text[];
95 BEGIN
96 FOR objtype IN VALUES
97 ('table'), ('index'), ('sequence'), ('view'),
98 ('materialized view'), ('foreign table'),
99 ('table column'), ('foreign table column'),
100 ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'),
101 ('table constraint'), ('domain constraint'), ('conversion'), ('default value'),
102 ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'),
103 ('text search parser'), ('text search dictionary'),
104 ('text search template'), ('text search configuration'),
105 ('policy'), ('user mapping'), ('default acl'), ('transform'),
106 ('operator of access method'), ('function of access method'),
107 ('publication relation')
108 LOOP
109 FOR names IN VALUES ('{eins}'), ('{addr_nsp, zwei}'), ('{eins, zwei, drei}')
110 LOOP
111 FOR args IN VALUES ('{}'), ('{integer}')
112 LOOP
113 BEGIN
114 PERFORM pg_get_object_address(objtype, names, args);
115 EXCEPTION WHEN OTHERS THEN
116 RAISE WARNING 'error for %,%,%: %', objtype, names, args, sqlerrm;
117 END;
118 END LOOP;
119 END LOOP;
120 END LOOP;
121 END;
122 $$;
123 WARNING: error for table,{eins},{}: relation "eins" does not exist
124 WARNING: error for table,{eins},{integer}: relation "eins" does not exist
125 WARNING: error for table,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist
126 WARNING: error for table,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
127 WARNING: error for table,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei"
128 WARNING: error for table,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
129 WARNING: error for index,{eins},{}: relation "eins" does not exist
130 WARNING: error for index,{eins},{integer}: relation "eins" does not exist
131 WARNING: error for index,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist
132 WARNING: error for index,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
133 WARNING: error for index,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei"
134 WARNING: error for index,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
135 WARNING: error for sequence,{eins},{}: relation "eins" does not exist
136 WARNING: error for sequence,{eins},{integer}: relation "eins" does not exist
137 WARNING: error for sequence,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist
138 WARNING: error for sequence,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
139 WARNING: error for sequence,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei"
140 WARNING: error for sequence,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
141 WARNING: error for view,{eins},{}: relation "eins" does not exist
142 WARNING: error for view,{eins},{integer}: relation "eins" does not exist
143 WARNING: error for view,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist
144 WARNING: error for view,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
145 WARNING: error for view,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei"
146 WARNING: error for view,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
147 WARNING: error for materialized view,{eins},{}: relation "eins" does not exist
148 WARNING: error for materialized view,{eins},{integer}: relation "eins" does not exist
149 WARNING: error for materialized view,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist
150 WARNING: error for materialized view,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
151 WARNING: error for materialized view,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei"
152 WARNING: error for materialized view,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
153 WARNING: error for foreign table,{eins},{}: relation "eins" does not exist
154 WARNING: error for foreign table,{eins},{integer}: relation "eins" does not exist
155 WARNING: error for foreign table,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist
156 WARNING: error for foreign table,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
157 WARNING: error for foreign table,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei"
158 WARNING: error for foreign table,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
159 WARNING: error for table column,{eins},{}: column name must be qualified
160 WARNING: error for table column,{eins},{integer}: column name must be qualified
161 WARNING: error for table column,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
162 WARNING: error for table column,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
163 WARNING: error for table column,{eins,zwei,drei},{}: schema "eins" does not exist
164 WARNING: error for table column,{eins,zwei,drei},{integer}: schema "eins" does not exist
165 WARNING: error for foreign table column,{eins},{}: column name must be qualified
166 WARNING: error for foreign table column,{eins},{integer}: column name must be qualified
167 WARNING: error for foreign table column,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
168 WARNING: error for foreign table column,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
169 WARNING: error for foreign table column,{eins,zwei,drei},{}: schema "eins" does not exist
170 WARNING: error for foreign table column,{eins,zwei,drei},{integer}: schema "eins" does not exist
171 WARNING: error for aggregate,{eins},{}: aggregate eins(*) does not exist
172 WARNING: error for aggregate,{eins},{integer}: aggregate eins(integer) does not exist
173 WARNING: error for aggregate,{addr_nsp,zwei},{}: aggregate addr_nsp.zwei(*) does not exist
174 WARNING: error for aggregate,{addr_nsp,zwei},{integer}: aggregate addr_nsp.zwei(integer) does not exist
175 WARNING: error for aggregate,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
176 WARNING: error for aggregate,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
177 WARNING: error for function,{eins},{}: function eins() does not exist
178 WARNING: error for function,{eins},{integer}: function eins(integer) does not exist
179 WARNING: error for function,{addr_nsp,zwei},{}: function addr_nsp.zwei() does not exist
180 WARNING: error for function,{addr_nsp,zwei},{integer}: function addr_nsp.zwei(integer) does not exist
181 WARNING: error for function,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
182 WARNING: error for function,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
183 WARNING: error for procedure,{eins},{}: procedure eins() does not exist
184 WARNING: error for procedure,{eins},{integer}: procedure eins(integer) does not exist
185 WARNING: error for procedure,{addr_nsp,zwei},{}: procedure addr_nsp.zwei() does not exist
186 WARNING: error for procedure,{addr_nsp,zwei},{integer}: procedure addr_nsp.zwei(integer) does not exist
187 WARNING: error for procedure,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
188 WARNING: error for procedure,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
189 WARNING: error for type,{eins},{}: type "eins" does not exist
190 WARNING: error for type,{eins},{integer}: type "eins" does not exist
191 WARNING: error for type,{addr_nsp,zwei},{}: name list length must be exactly 1
192 WARNING: error for type,{addr_nsp,zwei},{integer}: name list length must be exactly 1
193 WARNING: error for type,{eins,zwei,drei},{}: name list length must be exactly 1
194 WARNING: error for type,{eins,zwei,drei},{integer}: name list length must be exactly 1
195 WARNING: error for cast,{eins},{}: argument list length must be exactly 1
196 WARNING: error for cast,{eins},{integer}: type "eins" does not exist
197 WARNING: error for cast,{addr_nsp,zwei},{}: name list length must be exactly 1
198 WARNING: error for cast,{addr_nsp,zwei},{integer}: name list length must be exactly 1
199 WARNING: error for cast,{eins,zwei,drei},{}: name list length must be exactly 1
200 WARNING: error for cast,{eins,zwei,drei},{integer}: name list length must be exactly 1
201 WARNING: error for table constraint,{eins},{}: must specify relation and object name
202 WARNING: error for table constraint,{eins},{integer}: must specify relation and object name
203 WARNING: error for table constraint,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
204 WARNING: error for table constraint,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
205 WARNING: error for table constraint,{eins,zwei,drei},{}: schema "eins" does not exist
206 WARNING: error for table constraint,{eins,zwei,drei},{integer}: schema "eins" does not exist
207 WARNING: error for domain constraint,{eins},{}: argument list length must be exactly 1
208 WARNING: error for domain constraint,{eins},{integer}: type "eins" does not exist
209 WARNING: error for domain constraint,{addr_nsp,zwei},{}: name list length must be exactly 1
210 WARNING: error for domain constraint,{addr_nsp,zwei},{integer}: name list length must be exactly 1
211 WARNING: error for domain constraint,{eins,zwei,drei},{}: name list length must be exactly 1
212 WARNING: error for domain constraint,{eins,zwei,drei},{integer}: name list length must be exactly 1
213 WARNING: error for conversion,{eins},{}: conversion "eins" does not exist
214 WARNING: error for conversion,{eins},{integer}: conversion "eins" does not exist
215 WARNING: error for conversion,{addr_nsp,zwei},{}: conversion "addr_nsp.zwei" does not exist
216 WARNING: error for conversion,{addr_nsp,zwei},{integer}: conversion "addr_nsp.zwei" does not exist
217 WARNING: error for conversion,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
218 WARNING: error for conversion,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
219 WARNING: error for default value,{eins},{}: column name must be qualified
220 WARNING: error for default value,{eins},{integer}: column name must be qualified
221 WARNING: error for default value,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
222 WARNING: error for default value,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
223 WARNING: error for default value,{eins,zwei,drei},{}: schema "eins" does not exist
224 WARNING: error for default value,{eins,zwei,drei},{integer}: schema "eins" does not exist
225 WARNING: error for operator,{eins},{}: argument list length must be exactly 2
226 WARNING: error for operator,{eins},{integer}: argument list length must be exactly 2
227 WARNING: error for operator,{addr_nsp,zwei},{}: argument list length must be exactly 2
228 WARNING: error for operator,{addr_nsp,zwei},{integer}: argument list length must be exactly 2
229 WARNING: error for operator,{eins,zwei,drei},{}: argument list length must be exactly 2
230 WARNING: error for operator,{eins,zwei,drei},{integer}: argument list length must be exactly 2
231 WARNING: error for operator class,{eins},{}: name list length must be at least 2
232 WARNING: error for operator class,{eins},{integer}: name list length must be at least 2
233 WARNING: error for operator class,{addr_nsp,zwei},{}: access method "addr_nsp" does not exist
234 WARNING: error for operator class,{addr_nsp,zwei},{integer}: access method "addr_nsp" does not exist
235 WARNING: error for operator class,{eins,zwei,drei},{}: access method "eins" does not exist
236 WARNING: error for operator class,{eins,zwei,drei},{integer}: access method "eins" does not exist
237 WARNING: error for operator family,{eins},{}: name list length must be at least 2
238 WARNING: error for operator family,{eins},{integer}: name list length must be at least 2
239 WARNING: error for operator family,{addr_nsp,zwei},{}: access method "addr_nsp" does not exist
240 WARNING: error for operator family,{addr_nsp,zwei},{integer}: access method "addr_nsp" does not exist
241 WARNING: error for operator family,{eins,zwei,drei},{}: access method "eins" does not exist
242 WARNING: error for operator family,{eins,zwei,drei},{integer}: access method "eins" does not exist
243 WARNING: error for rule,{eins},{}: must specify relation and object name
244 WARNING: error for rule,{eins},{integer}: must specify relation and object name
245 WARNING: error for rule,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
246 WARNING: error for rule,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
247 WARNING: error for rule,{eins,zwei,drei},{}: schema "eins" does not exist
248 WARNING: error for rule,{eins,zwei,drei},{integer}: schema "eins" does not exist
249 WARNING: error for trigger,{eins},{}: must specify relation and object name
250 WARNING: error for trigger,{eins},{integer}: must specify relation and object name
251 WARNING: error for trigger,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
252 WARNING: error for trigger,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
253 WARNING: error for trigger,{eins,zwei,drei},{}: schema "eins" does not exist
254 WARNING: error for trigger,{eins,zwei,drei},{integer}: schema "eins" does not exist
255 WARNING: error for text search parser,{eins},{}: text search parser "eins" does not exist
256 WARNING: error for text search parser,{eins},{integer}: text search parser "eins" does not exist
257 WARNING: error for text search parser,{addr_nsp,zwei},{}: text search parser "addr_nsp.zwei" does not exist
258 WARNING: error for text search parser,{addr_nsp,zwei},{integer}: text search parser "addr_nsp.zwei" does not exist
259 WARNING: error for text search parser,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
260 WARNING: error for text search parser,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
261 WARNING: error for text search dictionary,{eins},{}: text search dictionary "eins" does not exist
262 WARNING: error for text search dictionary,{eins},{integer}: text search dictionary "eins" does not exist
263 WARNING: error for text search dictionary,{addr_nsp,zwei},{}: text search dictionary "addr_nsp.zwei" does not exist
264 WARNING: error for text search dictionary,{addr_nsp,zwei},{integer}: text search dictionary "addr_nsp.zwei" does not exist
265 WARNING: error for text search dictionary,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
266 WARNING: error for text search dictionary,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
267 WARNING: error for text search template,{eins},{}: text search template "eins" does not exist
268 WARNING: error for text search template,{eins},{integer}: text search template "eins" does not exist
269 WARNING: error for text search template,{addr_nsp,zwei},{}: text search template "addr_nsp.zwei" does not exist
270 WARNING: error for text search template,{addr_nsp,zwei},{integer}: text search template "addr_nsp.zwei" does not exist
271 WARNING: error for text search template,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
272 WARNING: error for text search template,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
273 WARNING: error for text search configuration,{eins},{}: text search configuration "eins" does not exist
274 WARNING: error for text search configuration,{eins},{integer}: text search configuration "eins" does not exist
275 WARNING: error for text search configuration,{addr_nsp,zwei},{}: text search configuration "addr_nsp.zwei" does not exist
276 WARNING: error for text search configuration,{addr_nsp,zwei},{integer}: text search configuration "addr_nsp.zwei" does not exist
277 WARNING: error for text search configuration,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei
278 WARNING: error for text search configuration,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei
279 WARNING: error for policy,{eins},{}: must specify relation and object name
280 WARNING: error for policy,{eins},{integer}: must specify relation and object name
281 WARNING: error for policy,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist
282 WARNING: error for policy,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist
283 WARNING: error for policy,{eins,zwei,drei},{}: schema "eins" does not exist
284 WARNING: error for policy,{eins,zwei,drei},{integer}: schema "eins" does not exist
285 WARNING: error for user mapping,{eins},{}: argument list length must be exactly 1
286 WARNING: error for user mapping,{eins},{integer}: user mapping for user "eins" on server "integer" does not exist
287 WARNING: error for user mapping,{addr_nsp,zwei},{}: argument list length must be exactly 1
288 WARNING: error for user mapping,{addr_nsp,zwei},{integer}: user mapping for user "addr_nsp" on server "integer" does not exist
289 WARNING: error for user mapping,{eins,zwei,drei},{}: argument list length must be exactly 1
290 WARNING: error for user mapping,{eins,zwei,drei},{integer}: user mapping for user "eins" on server "integer" does not exist
291 WARNING: error for default acl,{eins},{}: argument list length must be exactly 1
292 WARNING: error for default acl,{eins},{integer}: unrecognized default ACL object type "i"
293 WARNING: error for default acl,{addr_nsp,zwei},{}: argument list length must be exactly 1
294 WARNING: error for default acl,{addr_nsp,zwei},{integer}: unrecognized default ACL object type "i"
295 WARNING: error for default acl,{eins,zwei,drei},{}: argument list length must be exactly 1
296 WARNING: error for default acl,{eins,zwei,drei},{integer}: unrecognized default ACL object type "i"
297 WARNING: error for transform,{eins},{}: argument list length must be exactly 1
298 WARNING: error for transform,{eins},{integer}: type "eins" does not exist
299 WARNING: error for transform,{addr_nsp,zwei},{}: name list length must be exactly 1
300 WARNING: error for transform,{addr_nsp,zwei},{integer}: name list length must be exactly 1
301 WARNING: error for transform,{eins,zwei,drei},{}: name list length must be exactly 1
302 WARNING: error for transform,{eins,zwei,drei},{integer}: name list length must be exactly 1
303 WARNING: error for operator of access method,{eins},{}: name list length must be at least 3
304 WARNING: error for operator of access method,{eins},{integer}: name list length must be at least 3
305 WARNING: error for operator of access method,{addr_nsp,zwei},{}: name list length must be at least 3
306 WARNING: error for operator of access method,{addr_nsp,zwei},{integer}: name list length must be at least 3
307 WARNING: error for operator of access method,{eins,zwei,drei},{}: argument list length must be exactly 2
308 WARNING: error for operator of access method,{eins,zwei,drei},{integer}: argument list length must be exactly 2
309 WARNING: error for function of access method,{eins},{}: name list length must be at least 3
310 WARNING: error for function of access method,{eins},{integer}: name list length must be at least 3
311 WARNING: error for function of access method,{addr_nsp,zwei},{}: name list length must be at least 3
312 WARNING: error for function of access method,{addr_nsp,zwei},{integer}: name list length must be at least 3
313 WARNING: error for function of access method,{eins,zwei,drei},{}: argument list length must be exactly 2
314 WARNING: error for function of access method,{eins,zwei,drei},{integer}: argument list length must be exactly 2
315 WARNING: error for publication relation,{eins},{}: argument list length must be exactly 1
316 WARNING: error for publication relation,{eins},{integer}: relation "eins" does not exist
317 WARNING: error for publication relation,{addr_nsp,zwei},{}: argument list length must be exactly 1
318 WARNING: error for publication relation,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist
319 WARNING: error for publication relation,{eins,zwei,drei},{}: argument list length must be exactly 1
320 WARNING: error for publication relation,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei"
321 -- these object types cannot be qualified names
322 SELECT pg_get_object_address('language', '{one}', '{}');
323 ERROR: language "one" does not exist
324 SELECT pg_get_object_address('language', '{one,two}', '{}');
325 ERROR: name list length must be exactly 1
326 SELECT pg_get_object_address('large object', '{123}', '{}');
327 ERROR: large object 123 does not exist
328 SELECT pg_get_object_address('large object', '{123,456}', '{}');
329 ERROR: name list length must be exactly 1
330 SELECT pg_get_object_address('large object', '{blargh}', '{}');
331 ERROR: invalid input syntax for type oid: "blargh"
332 SELECT pg_get_object_address('schema', '{one}', '{}');
333 ERROR: schema "one" does not exist
334 SELECT pg_get_object_address('schema', '{one,two}', '{}');
335 ERROR: name list length must be exactly 1
336 SELECT pg_get_object_address('role', '{one}', '{}');
337 ERROR: role "one" does not exist
338 SELECT pg_get_object_address('role', '{one,two}', '{}');
339 ERROR: name list length must be exactly 1
340 SELECT pg_get_object_address('database', '{one}', '{}');
341 ERROR: database "one" does not exist
342 SELECT pg_get_object_address('database', '{one,two}', '{}');
343 ERROR: name list length must be exactly 1
344 SELECT pg_get_object_address('tablespace', '{one}', '{}');
345 ERROR: tablespace "one" does not exist
346 SELECT pg_get_object_address('tablespace', '{one,two}', '{}');
347 ERROR: name list length must be exactly 1
348 SELECT pg_get_object_address('foreign-data wrapper', '{one}', '{}');
349 ERROR: foreign-data wrapper "one" does not exist
350 SELECT pg_get_object_address('foreign-data wrapper', '{one,two}', '{}');
351 ERROR: name list length must be exactly 1
352 SELECT pg_get_object_address('server', '{one}', '{}');
353 ERROR: server "one" does not exist
354 SELECT pg_get_object_address('server', '{one,two}', '{}');
355 ERROR: name list length must be exactly 1
356 SELECT pg_get_object_address('extension', '{one}', '{}');
357 ERROR: extension "one" does not exist
358 SELECT pg_get_object_address('extension', '{one,two}', '{}');
359 ERROR: name list length must be exactly 1
360 SELECT pg_get_object_address('event trigger', '{one}', '{}');
361 ERROR: event trigger "one" does not exist
362 SELECT pg_get_object_address('event trigger', '{one,two}', '{}');
363 ERROR: name list length must be exactly 1
364 SELECT pg_get_object_address('access method', '{one}', '{}');
365 ERROR: access method "one" does not exist
366 SELECT pg_get_object_address('access method', '{one,two}', '{}');
367 ERROR: name list length must be exactly 1
368 SELECT pg_get_object_address('publication', '{one}', '{}');
369 ERROR: publication "one" does not exist
370 SELECT pg_get_object_address('publication', '{one,two}', '{}');
371 ERROR: name list length must be exactly 1
372 SELECT pg_get_object_address('subscription', '{one}', '{}');
373 ERROR: subscription "one" does not exist
374 SELECT pg_get_object_address('subscription', '{one,two}', '{}');
375 ERROR: name list length must be exactly 1
376 -- test successful cases
377 WITH objects (type, name, args) AS (VALUES
378 ('table', '{addr_nsp, gentable}'::text[], '{}'::text[]),
379 ('table', '{addr_nsp, parttable}'::text[], '{}'::text[]),
380 ('index', '{addr_nsp, gentable_pkey}', '{}'),
381 ('index', '{addr_nsp, parttable_pkey}', '{}'),
382 ('sequence', '{addr_nsp, gentable_a_seq}', '{}'),
383 -- toast table
384 ('view', '{addr_nsp, genview}', '{}'),
385 ('materialized view', '{addr_nsp, genmatview}', '{}'),
386 ('foreign table', '{addr_nsp, genftable}', '{}'),
387 ('table column', '{addr_nsp, gentable, b}', '{}'),
388 ('foreign table column', '{addr_nsp, genftable, a}', '{}'),
389 ('aggregate', '{addr_nsp, genaggr}', '{int4}'),
390 ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'),
391 ('procedure', '{addr_nsp, proc}', '{int4}'),
392 ('type', '{pg_catalog._int4}', '{}'),
393 ('type', '{addr_nsp.gendomain}', '{}'),
394 ('type', '{addr_nsp.gencomptype}', '{}'),
395 ('type', '{addr_nsp.genenum}', '{}'),
396 ('cast', '{int8}', '{int4}'),
397 ('collation', '{default}', '{}'),
398 ('table constraint', '{addr_nsp, gentable, a_chk}', '{}'),
399 ('domain constraint', '{addr_nsp.gendomain}', '{domconstr}'),
400 ('conversion', '{pg_catalog, koi8_r_to_mic}', '{}'),
401 ('default value', '{addr_nsp, gentable, b}', '{}'),
402 ('language', '{plpgsql}', '{}'),
403 -- large object
404 ('operator', '{+}', '{int4, int4}'),
405 ('operator class', '{btree, int4_ops}', '{}'),
406 ('operator family', '{btree, integer_ops}', '{}'),
407 ('operator of access method', '{btree,integer_ops,1}', '{integer,integer}'),
408 ('function of access method', '{btree,integer_ops,2}', '{integer,integer}'),
409 ('rule', '{addr_nsp, genview, _RETURN}', '{}'),
410 ('trigger', '{addr_nsp, gentable, t}', '{}'),
411 ('schema', '{addr_nsp}', '{}'),
412 ('text search parser', '{addr_ts_prs}', '{}'),
413 ('text search dictionary', '{addr_ts_dict}', '{}'),
414 ('text search template', '{addr_ts_temp}', '{}'),
415 ('text search configuration', '{addr_ts_conf}', '{}'),
416 ('role', '{regress_addr_user}', '{}'),
417 -- database
418 -- tablespace
419 ('foreign-data wrapper', '{addr_fdw}', '{}'),
420 ('server', '{addr_fserv}', '{}'),
421 ('user mapping', '{regress_addr_user}', '{integer}'),
422 ('default acl', '{regress_addr_user,public}', '{r}'),
423 ('default acl', '{regress_addr_user}', '{r}'),
424 -- extension
425 -- event trigger
426 ('policy', '{addr_nsp, gentable, genpol}', '{}'),
427 ('transform', '{int}', '{sql}'),
428 ('access method', '{btree}', '{}'),
429 ('publication', '{addr_pub}', '{}'),
430 ('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
431 ('subscription', '{regress_addr_sub}', '{}'),
432 ('statistics object', '{addr_nsp, gentable_stat}', '{}')
433 )
434 SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
435 -- test roundtrip through pg_identify_object_as_address
436 ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)) =
437 ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.objsubid))
438 FROM objects, pg_get_object_address(type, name, args) addr1,
439 pg_identify_object_as_address(classid, objid, objsubid) ioa(typ,nms,args),
440 pg_get_object_address(typ, nms, ioa.args) as addr2
441 ORDER BY addr1.classid, addr1.objid, addr1.objsubid;
442 type | schema | name | identity | ?column?
443 ---------------------------+------------+-------------------+----------------------------------------------------------------------+----------
444 default acl | | | for role regress_addr_user in schema public on tables | t
445 default acl | | | for role regress_addr_user on tables | t
446 type | pg_catalog | _int4 | integer[] | t
447 type | addr_nsp | gencomptype | addr_nsp.gencomptype | t
448 type | addr_nsp | genenum | addr_nsp.genenum | t
449 type | addr_nsp | gendomain | addr_nsp.gendomain | t
450 function | pg_catalog | | pg_catalog.pg_identify_object(pg_catalog.oid,pg_catalog.oid,integer) | t
451 aggregate | addr_nsp | | addr_nsp.genaggr(integer) | t
452 procedure | addr_nsp | | addr_nsp.proc(integer) | t
453 sequence | addr_nsp | gentable_a_seq | addr_nsp.gentable_a_seq | t
454 table | addr_nsp | gentable | addr_nsp.gentable | t
455 table column | addr_nsp | gentable | addr_nsp.gentable.b | t
456 index | addr_nsp | gentable_pkey | addr_nsp.gentable_pkey | t
457 table | addr_nsp | parttable | addr_nsp.parttable | t
458 index | addr_nsp | parttable_pkey | addr_nsp.parttable_pkey | t
459 view | addr_nsp | genview | addr_nsp.genview | t
460 materialized view | addr_nsp | genmatview | addr_nsp.genmatview | t
461 foreign table | addr_nsp | genftable | addr_nsp.genftable | t
462 foreign table column | addr_nsp | genftable | addr_nsp.genftable.a | t
463 role | | regress_addr_user | regress_addr_user | t
464 server | | addr_fserv | addr_fserv | t
465 user mapping | | | regress_addr_user on server integer | t
466 foreign-data wrapper | | addr_fdw | addr_fdw | t
467 access method | | btree | btree | t
468 operator of access method | | | operator 1 (integer, integer) of pg_catalog.integer_ops USING btree | t
469 function of access method | | | function 2 (integer, integer) of pg_catalog.integer_ops USING btree | t
470 default value | | | for addr_nsp.gentable.b | t
471 cast | | | (bigint AS integer) | t
472 table constraint | addr_nsp | | a_chk on addr_nsp.gentable | t
473 domain constraint | addr_nsp | | domconstr on addr_nsp.gendomain | t
474 conversion | pg_catalog | koi8_r_to_mic | pg_catalog.koi8_r_to_mic | t
475 language | | plpgsql | plpgsql | t
476 schema | | addr_nsp | addr_nsp | t
477 operator class | pg_catalog | int4_ops | pg_catalog.int4_ops USING btree | t
478 operator | pg_catalog | | pg_catalog.+(integer,integer) | t
479 rule | | | "_RETURN" on addr_nsp.genview | t
480 trigger | | | t on addr_nsp.gentable | t
481 operator family | pg_catalog | integer_ops | pg_catalog.integer_ops USING btree | t
482 policy | | | genpol on addr_nsp.gentable | t
483 statistics object | addr_nsp | gentable_stat | addr_nsp.gentable_stat | t
484 collation | pg_catalog | "default" | pg_catalog."default" | t
485 transform | | | for integer on language sql | t
486 text search dictionary | addr_nsp | addr_ts_dict | addr_nsp.addr_ts_dict | t
487 text search parser | addr_nsp | addr_ts_prs | addr_nsp.addr_ts_prs | t
488 text search configuration | addr_nsp | addr_ts_conf | addr_nsp.addr_ts_conf | t
489 text search template | addr_nsp | addr_ts_temp | addr_nsp.addr_ts_temp | t
490 subscription | | regress_addr_sub | regress_addr_sub | t
491 publication | | addr_pub | addr_pub | t
492 publication relation | | | addr_nsp.gentable in publication addr_pub | t
493 (49 rows)
495 ---
496 --- Cleanup resources
497 ---
498 DROP FOREIGN DATA WRAPPER addr_fdw CASCADE;
499 NOTICE: drop cascades to 4 other objects
500 DETAIL: drop cascades to server addr_fserv
501 drop cascades to foreign table genftable
502 drop cascades to server integer
503 drop cascades to user mapping for regress_addr_user on server integer
504 DROP PUBLICATION addr_pub;
505 DROP SUBSCRIPTION regress_addr_sub;
506 DROP SCHEMA addr_nsp CASCADE;
507 NOTICE: drop cascades to 14 other objects
508 DETAIL: drop cascades to text search dictionary addr_ts_dict
509 drop cascades to text search configuration addr_ts_conf
510 drop cascades to text search template addr_ts_temp
511 drop cascades to text search parser addr_ts_prs
512 drop cascades to table gentable
513 drop cascades to table parttable
514 drop cascades to view genview
515 drop cascades to materialized view genmatview
516 drop cascades to type gencomptype
517 drop cascades to type genenum
518 drop cascades to function genaggr(integer)
519 drop cascades to type gendomain
520 drop cascades to function trig()
521 drop cascades to function proc(integer)
522 DROP OWNED BY regress_addr_user;
523 DROP USER regress_addr_user;
524 --
525 -- Checks for invalid objects
526 --
527 -- Make sure that NULL handling is correct.
528 \pset null 'NULL'
529 -- Temporarily disable fancy output, so as future additions never create
530 -- a large amount of diffs.
531 \a\t
532 -- Keep this list in the same order as getObjectIdentityParts()
533 -- in objectaddress.c.
534 WITH objects (classid, objid, objsubid) AS (VALUES
535 ('pg_class'::regclass, 0, 0), -- no relation
536 ('pg_class'::regclass, 'pg_class'::regclass, 100), -- no column for relation
537 ('pg_proc'::regclass, 0, 0), -- no function
538 ('pg_type'::regclass, 0, 0), -- no type
539 ('pg_cast'::regclass, 0, 0), -- no cast
540 ('pg_collation'::regclass, 0, 0), -- no collation
541 ('pg_constraint'::regclass, 0, 0), -- no constraint
542 ('pg_conversion'::regclass, 0, 0), -- no conversion
543 ('pg_attrdef'::regclass, 0, 0), -- no default attribute
544 ('pg_language'::regclass, 0, 0), -- no language
545 ('pg_largeobject'::regclass, 0, 0), -- no large object, no error
546 ('pg_operator'::regclass, 0, 0), -- no operator
547 ('pg_opclass'::regclass, 0, 0), -- no opclass, no need to check for no access method
548 ('pg_opfamily'::regclass, 0, 0), -- no opfamily
549 ('pg_am'::regclass, 0, 0), -- no access method
550 ('pg_amop'::regclass, 0, 0), -- no AM operator
551 ('pg_amproc'::regclass, 0, 0), -- no AM proc
552 ('pg_rewrite'::regclass, 0, 0), -- no rewrite
553 ('pg_trigger'::regclass, 0, 0), -- no trigger
554 ('pg_namespace'::regclass, 0, 0), -- no schema
555 ('pg_statistic_ext'::regclass, 0, 0), -- no statistics
556 ('pg_ts_parser'::regclass, 0, 0), -- no TS parser
557 ('pg_ts_dict'::regclass, 0, 0), -- no TS dictionnary
558 ('pg_ts_template'::regclass, 0, 0), -- no TS template
559 ('pg_ts_config'::regclass, 0, 0), -- no TS configuration
560 ('pg_authid'::regclass, 0, 0), -- no role
561 ('pg_database'::regclass, 0, 0), -- no database
562 ('pg_tablespace'::regclass, 0, 0), -- no tablespace
563 ('pg_foreign_data_wrapper'::regclass, 0, 0), -- no FDW
564 ('pg_foreign_server'::regclass, 0, 0), -- no server
565 ('pg_user_mapping'::regclass, 0, 0), -- no user mapping
566 ('pg_default_acl'::regclass, 0, 0), -- no default ACL
567 ('pg_extension'::regclass, 0, 0), -- no extension
568 ('pg_event_trigger'::regclass, 0, 0), -- no event trigger
569 ('pg_policy'::regclass, 0, 0), -- no policy
570 ('pg_publication'::regclass, 0, 0), -- no publication
571 ('pg_publication_rel'::regclass, 0, 0), -- no publication relation
572 ('pg_subscription'::regclass, 0, 0), -- no subscription
573 ('pg_transform'::regclass, 0, 0) -- no transformation
574 )
575 SELECT ROW(pg_identify_object(objects.classid, objects.objid, objects.objsubid))
576 AS ident,
577 ROW(pg_identify_object_as_address(objects.classid, objects.objid, objects.objsubid))
578 AS addr,
579 pg_describe_object(objects.classid, objects.objid, objects.objsubid)
580 AS descr
581 FROM objects
582 ORDER BY objects.classid, objects.objid, objects.objsubid;
583 ("(""default acl"",,,)")|("(""default acl"",,)")|NULL
584 ("(tablespace,,,)")|("(tablespace,,)")|NULL
585 ("(type,,,)")|("(type,,)")|NULL
586 ("(routine,,,)")|("(routine,,)")|NULL
587 ("(relation,,,)")|("(relation,,)")|NULL
588 ("(""table column"",,,)")|("(""table column"",,)")|NULL
589 ("(role,,,)")|("(role,,)")|NULL
590 ("(database,,,)")|("(database,,)")|NULL
591 ("(server,,,)")|("(server,,)")|NULL
592 ("(""user mapping"",,,)")|("(""user mapping"",,)")|NULL
593 ("(""foreign-data wrapper"",,,)")|("(""foreign-data wrapper"",,)")|NULL
594 ("(""access method"",,,)")|("(""access method"",,)")|NULL
595 ("(""operator of access method"",,,)")|("(""operator of access method"",,)")|NULL
596 ("(""function of access method"",,,)")|("(""function of access method"",,)")|NULL
597 ("(""default value"",,,)")|("(""default value"",,)")|NULL
598 ("(cast,,,)")|("(cast,,)")|NULL
599 ("(constraint,,,)")|("(constraint,,)")|NULL
600 ("(conversion,,,)")|("(conversion,,)")|NULL
601 ("(language,,,)")|("(language,,)")|NULL
602 ("(""large object"",,,)")|("(""large object"",,)")|NULL
603 ("(schema,,,)")|("(schema,,)")|NULL
604 ("(""operator class"",,,)")|("(""operator class"",,)")|NULL
605 ("(operator,,,)")|("(operator,,)")|NULL
606 ("(rule,,,)")|("(rule,,)")|NULL
607 ("(trigger,,,)")|("(trigger,,)")|NULL
608 ("(""operator family"",,,)")|("(""operator family"",,)")|NULL
609 ("(extension,,,)")|("(extension,,)")|NULL
610 ("(policy,,,)")|("(policy,,)")|NULL
611 ("(""statistics object"",,,)")|("(""statistics object"",,)")|NULL
612 ("(collation,,,)")|("(collation,,)")|NULL
613 ("(""event trigger"",,,)")|("(""event trigger"",,)")|NULL
614 ("(transform,,,)")|("(transform,,)")|NULL
615 ("(""text search dictionary"",,,)")|("(""text search dictionary"",,)")|NULL
616 ("(""text search parser"",,,)")|("(""text search parser"",,)")|NULL
617 ("(""text search configuration"",,,)")|("(""text search configuration"",,)")|NULL
618 ("(""text search template"",,,)")|("(""text search template"",,)")|NULL
619 ("(subscription,,,)")|("(subscription,,)")|NULL
620 ("(publication,,,)")|("(publication,,)")|NULL
621 ("(""publication relation"",,,)")|("(""publication relation"",,)")|NULL
622 -- restore normal output mode
623 \a\t