2 -- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA}
4 -- Clean up in case a prior regression run failed
5 SET client_min_messages TO 'warning';
6 DROP ROLE IF EXISTS regress_alter_user1;
7 DROP ROLE IF EXISTS regress_alter_user2;
8 DROP ROLE IF EXISTS regress_alter_user3;
9 RESET client_min_messages;
10 CREATE USER regress_alter_user3;
11 CREATE USER regress_alter_user2;
12 CREATE USER regress_alter_user1 IN ROLE regress_alter_user3;
13 CREATE SCHEMA alt_nsp1;
14 CREATE SCHEMA alt_nsp2;
15 GRANT ALL ON SCHEMA alt_nsp1, alt_nsp2 TO public;
16 SET search_path = alt_nsp1, public;
18 -- Function and Aggregate
20 SET SESSION AUTHORIZATION regress_alter_user1;
21 CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql
23 CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql
25 CREATE AGGREGATE alt_agg1 (
26 sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 0
28 CREATE AGGREGATE alt_agg2 (
29 sfunc1 = int4mi, basetype = int4, stype1 = int4, initcond = 0
31 ALTER AGGREGATE alt_func1(int) RENAME TO alt_func3; -- failed (not aggregate)
32 ERROR: function alt_func1(integer) is not an aggregate
33 ALTER AGGREGATE alt_func1(int) OWNER TO regress_alter_user3; -- failed (not aggregate)
34 ERROR: function alt_func1(integer) is not an aggregate
35 ALTER AGGREGATE alt_func1(int) SET SCHEMA alt_nsp2; -- failed (not aggregate)
36 ERROR: function alt_func1(integer) is not an aggregate
37 ALTER FUNCTION alt_func1(int) RENAME TO alt_func2; -- failed (name conflict)
38 ERROR: function alt_func2(integer) already exists in schema "alt_nsp1"
39 ALTER FUNCTION alt_func1(int) RENAME TO alt_func3; -- OK
40 ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_user2; -- failed (no role membership)
41 ERROR: must be member of role "regress_alter_user2"
42 ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_user3; -- OK
43 ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp1; -- OK, already there
44 ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp2; -- OK
45 ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg2; -- failed (name conflict)
46 ERROR: function alt_agg2(integer) already exists in schema "alt_nsp1"
47 ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg3; -- OK
48 ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_user2; -- failed (no role membership)
49 ERROR: must be member of role "regress_alter_user2"
50 ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_user3; -- OK
51 ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- OK
52 SET SESSION AUTHORIZATION regress_alter_user2;
53 CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql
55 CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql
57 CREATE AGGREGATE alt_agg1 (
58 sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 100
60 CREATE AGGREGATE alt_agg2 (
61 sfunc1 = int4mi, basetype = int4, stype1 = int4, initcond = -100
63 ALTER FUNCTION alt_func3(int) RENAME TO alt_func4; -- failed (not owner)
64 ERROR: must be owner of function alt_func3
65 ALTER FUNCTION alt_func1(int) RENAME TO alt_func4; -- OK
66 ALTER FUNCTION alt_func3(int) OWNER TO regress_alter_user2; -- failed (not owner)
67 ERROR: must be owner of function alt_func3
68 ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_user3; -- failed (no role membership)
69 ERROR: must be member of role "regress_alter_user3"
70 ALTER FUNCTION alt_func3(int) SET SCHEMA alt_nsp2; -- failed (not owner)
71 ERROR: must be owner of function alt_func3
72 ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp2; -- failed (name conflicts)
73 ERROR: function alt_func2(integer) already exists in schema "alt_nsp2"
74 ALTER AGGREGATE alt_agg3(int) RENAME TO alt_agg4; -- failed (not owner)
75 ERROR: must be owner of function alt_agg3
76 ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg4; -- OK
77 ALTER AGGREGATE alt_agg3(int) OWNER TO regress_alter_user2; -- failed (not owner)
78 ERROR: must be owner of function alt_agg3
79 ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_user3; -- failed (no role membership)
80 ERROR: must be member of role "regress_alter_user3"
81 ALTER AGGREGATE alt_agg3(int) SET SCHEMA alt_nsp2; -- failed (not owner)
82 ERROR: must be owner of function alt_agg3
83 ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- failed (name conflict)
84 ERROR: function alt_agg2(integer) already exists in schema "alt_nsp2"
85 RESET SESSION AUTHORIZATION;
86 SELECT n.nspname, proname, prorettype::regtype, proisagg, a.rolname
87 FROM pg_proc p, pg_namespace n, pg_authid a
88 WHERE p.pronamespace = n.oid AND p.proowner = a.oid
89 AND n.nspname IN ('alt_nsp1', 'alt_nsp2')
90 ORDER BY nspname, proname;
91 nspname | proname | prorettype | proisagg | rolname
92 ----------+-----------+------------+----------+---------------------
93 alt_nsp1 | alt_agg2 | integer | t | regress_alter_user2
94 alt_nsp1 | alt_agg3 | integer | t | regress_alter_user1
95 alt_nsp1 | alt_agg4 | integer | t | regress_alter_user2
96 alt_nsp1 | alt_func2 | integer | f | regress_alter_user2
97 alt_nsp1 | alt_func3 | integer | f | regress_alter_user1
98 alt_nsp1 | alt_func4 | integer | f | regress_alter_user2
99 alt_nsp2 | alt_agg2 | integer | t | regress_alter_user3
100 alt_nsp2 | alt_func2 | integer | f | regress_alter_user3
104 -- We would test collations here, but it's not possible because the error
105 -- messages tend to be nonportable.
110 SET SESSION AUTHORIZATION regress_alter_user1;
111 CREATE CONVERSION alt_conv1 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
112 CREATE CONVERSION alt_conv2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
113 ALTER CONVERSION alt_conv1 RENAME TO alt_conv2; -- failed (name conflict)
114 ERROR: conversion "alt_conv2" already exists in schema "alt_nsp1"
115 ALTER CONVERSION alt_conv1 RENAME TO alt_conv3; -- OK
116 ALTER CONVERSION alt_conv2 OWNER TO regress_alter_user2; -- failed (no role membership)
117 ERROR: must be member of role "regress_alter_user2"
118 ALTER CONVERSION alt_conv2 OWNER TO regress_alter_user3; -- OK
119 ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- OK
120 SET SESSION AUTHORIZATION regress_alter_user2;
121 CREATE CONVERSION alt_conv1 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
122 CREATE CONVERSION alt_conv2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
123 ALTER CONVERSION alt_conv3 RENAME TO alt_conv4; -- failed (not owner)
124 ERROR: must be owner of conversion alt_conv3
125 ALTER CONVERSION alt_conv1 RENAME TO alt_conv4; -- OK
126 ALTER CONVERSION alt_conv3 OWNER TO regress_alter_user2; -- failed (not owner)
127 ERROR: must be owner of conversion alt_conv3
128 ALTER CONVERSION alt_conv2 OWNER TO regress_alter_user3; -- failed (no role membership)
129 ERROR: must be member of role "regress_alter_user3"
130 ALTER CONVERSION alt_conv3 SET SCHEMA alt_nsp2; -- failed (not owner)
131 ERROR: must be owner of conversion alt_conv3
132 ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- failed (name conflict)
133 ERROR: conversion "alt_conv2" already exists in schema "alt_nsp2"
134 RESET SESSION AUTHORIZATION;
135 SELECT n.nspname, c.conname, a.rolname
136 FROM pg_conversion c, pg_namespace n, pg_authid a
137 WHERE c.connamespace = n.oid AND c.conowner = a.oid
138 AND n.nspname IN ('alt_nsp1', 'alt_nsp2')
139 ORDER BY nspname, conname;
140 nspname | conname | rolname
141 ----------+-----------+---------------------
142 alt_nsp1 | alt_conv2 | regress_alter_user2
143 alt_nsp1 | alt_conv3 | regress_alter_user1
144 alt_nsp1 | alt_conv4 | regress_alter_user2
145 alt_nsp2 | alt_conv2 | regress_alter_user3
149 -- Foreign Data Wrapper and Foreign Server
151 CREATE FOREIGN DATA WRAPPER alt_fdw1;
152 CREATE FOREIGN DATA WRAPPER alt_fdw2;
153 CREATE SERVER alt_fserv1 FOREIGN DATA WRAPPER alt_fdw1;
154 CREATE SERVER alt_fserv2 FOREIGN DATA WRAPPER alt_fdw2;
155 ALTER FOREIGN DATA WRAPPER alt_fdw1 RENAME TO alt_fdw2; -- failed (name conflict)
156 ERROR: foreign-data wrapper "alt_fdw2" already exists
157 ALTER FOREIGN DATA WRAPPER alt_fdw1 RENAME TO alt_fdw3; -- OK
158 ALTER SERVER alt_fserv1 RENAME TO alt_fserv2; -- failed (name conflict)
159 ERROR: server "alt_fserv2" already exists
160 ALTER SERVER alt_fserv1 RENAME TO alt_fserv3; -- OK
161 SELECT fdwname FROM pg_foreign_data_wrapper WHERE fdwname like 'alt_fdw%';
168 SELECT srvname FROM pg_foreign_server WHERE srvname like 'alt_fserv%';
176 -- Procedural Language
178 CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler;
179 CREATE LANGUAGE alt_lang2 HANDLER plpgsql_call_handler;
180 ALTER LANGUAGE alt_lang1 OWNER TO regress_alter_user1; -- OK
181 ALTER LANGUAGE alt_lang2 OWNER TO regress_alter_user2; -- OK
182 SET SESSION AUTHORIZATION regress_alter_user1;
183 ALTER LANGUAGE alt_lang1 RENAME TO alt_lang2; -- failed (name conflict)
184 ERROR: language "alt_lang2" already exists
185 ALTER LANGUAGE alt_lang2 RENAME TO alt_lang3; -- failed (not owner)
186 ERROR: must be owner of language alt_lang2
187 ALTER LANGUAGE alt_lang1 RENAME TO alt_lang3; -- OK
188 ALTER LANGUAGE alt_lang2 OWNER TO regress_alter_user3; -- failed (not owner)
189 ERROR: must be owner of language alt_lang2
190 ALTER LANGUAGE alt_lang3 OWNER TO regress_alter_user2; -- failed (no role membership)
191 ERROR: must be member of role "regress_alter_user2"
192 ALTER LANGUAGE alt_lang3 OWNER TO regress_alter_user3; -- OK
193 RESET SESSION AUTHORIZATION;
194 SELECT lanname, a.rolname
195 FROM pg_language l, pg_authid a
196 WHERE l.lanowner = a.oid AND l.lanname like 'alt_lang%'
199 -----------+---------------------
200 alt_lang2 | regress_alter_user2
201 alt_lang3 | regress_alter_user3
207 SET SESSION AUTHORIZATION regress_alter_user1;
208 CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi );
209 CREATE OPERATOR @+@ ( leftarg = int4, rightarg = int4, procedure = int4pl );
210 ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_user2; -- failed (no role membership)
211 ERROR: must be member of role "regress_alter_user2"
212 ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_user3; -- OK
213 ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- OK
214 SET SESSION AUTHORIZATION regress_alter_user2;
215 CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi );
216 ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_user2; -- failed (not owner)
217 ERROR: must be owner of operator @+@
218 ALTER OPERATOR @-@(int4, int4) OWNER TO regress_alter_user3; -- failed (no role membership)
219 ERROR: must be member of role "regress_alter_user3"
220 ALTER OPERATOR @+@(int4, int4) SET SCHEMA alt_nsp2; -- failed (not owner)
221 ERROR: must be owner of operator @+@
222 -- can't test this: the error message includes the raw oid of namespace
223 -- ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- failed (name conflict)
224 RESET SESSION AUTHORIZATION;
225 SELECT n.nspname, oprname, a.rolname,
226 oprleft::regtype, oprright::regtype, oprcode::regproc
227 FROM pg_operator o, pg_namespace n, pg_authid a
228 WHERE o.oprnamespace = n.oid AND o.oprowner = a.oid
229 AND n.nspname IN ('alt_nsp1', 'alt_nsp2')
230 ORDER BY nspname, oprname;
231 nspname | oprname | rolname | oprleft | oprright | oprcode
232 ----------+---------+---------------------+---------+----------+---------
233 alt_nsp1 | @+@ | regress_alter_user3 | integer | integer | int4pl
234 alt_nsp1 | @-@ | regress_alter_user2 | integer | integer | int4mi
235 alt_nsp2 | @-@ | regress_alter_user1 | integer | integer | int4mi
239 -- OpFamily and OpClass
241 CREATE OPERATOR FAMILY alt_opf1 USING hash;
242 CREATE OPERATOR FAMILY alt_opf2 USING hash;
243 ALTER OPERATOR FAMILY alt_opf1 USING hash OWNER TO regress_alter_user1;
244 ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user1;
245 CREATE OPERATOR CLASS alt_opc1 FOR TYPE uuid USING hash AS STORAGE uuid;
246 CREATE OPERATOR CLASS alt_opc2 FOR TYPE uuid USING hash AS STORAGE uuid;
247 ALTER OPERATOR CLASS alt_opc1 USING hash OWNER TO regress_alter_user1;
248 ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user1;
249 SET SESSION AUTHORIZATION regress_alter_user1;
250 ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf2; -- failed (name conflict)
251 ERROR: operator family "alt_opf2" for access method "hash" already exists in schema "alt_nsp1"
252 ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf3; -- OK
253 ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user2; -- failed (no role membership)
254 ERROR: must be member of role "regress_alter_user2"
255 ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user3; -- OK
256 ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- OK
257 ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc2; -- failed (name conflict)
258 ERROR: operator class "alt_opc2" for access method "hash" already exists in schema "alt_nsp1"
259 ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc3; -- OK
260 ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user2; -- failed (no role membership)
261 ERROR: must be member of role "regress_alter_user2"
262 ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user3; -- OK
263 ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- OK
264 RESET SESSION AUTHORIZATION;
265 CREATE OPERATOR FAMILY alt_opf1 USING hash;
266 CREATE OPERATOR FAMILY alt_opf2 USING hash;
267 ALTER OPERATOR FAMILY alt_opf1 USING hash OWNER TO regress_alter_user2;
268 ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user2;
269 CREATE OPERATOR CLASS alt_opc1 FOR TYPE macaddr USING hash AS STORAGE macaddr;
270 CREATE OPERATOR CLASS alt_opc2 FOR TYPE macaddr USING hash AS STORAGE macaddr;
271 ALTER OPERATOR CLASS alt_opc1 USING hash OWNER TO regress_alter_user2;
272 ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user2;
273 SET SESSION AUTHORIZATION regress_alter_user2;
274 ALTER OPERATOR FAMILY alt_opf3 USING hash RENAME TO alt_opf4; -- failed (not owner)
275 ERROR: must be owner of operator family alt_opf3
276 ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf4; -- OK
277 ALTER OPERATOR FAMILY alt_opf3 USING hash OWNER TO regress_alter_user2; -- failed (not owner)
278 ERROR: must be owner of operator family alt_opf3
279 ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user3; -- failed (no role membership)
280 ERROR: must be member of role "regress_alter_user3"
281 ALTER OPERATOR FAMILY alt_opf3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner)
282 ERROR: must be owner of operator family alt_opf3
283 ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict)
284 ERROR: operator family "alt_opf2" for access method "hash" already exists in schema "alt_nsp2"
285 ALTER OPERATOR CLASS alt_opc3 USING hash RENAME TO alt_opc4; -- failed (not owner)
286 ERROR: must be owner of operator class alt_opc3
287 ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc4; -- OK
288 ALTER OPERATOR CLASS alt_opc3 USING hash OWNER TO regress_alter_user2; -- failed (not owner)
289 ERROR: must be owner of operator class alt_opc3
290 ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user3; -- failed (no role membership)
291 ERROR: must be member of role "regress_alter_user3"
292 ALTER OPERATOR CLASS alt_opc3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner)
293 ERROR: must be owner of operator class alt_opc3
294 ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict)
295 ERROR: operator class "alt_opc2" for access method "hash" already exists in schema "alt_nsp2"
296 RESET SESSION AUTHORIZATION;
297 SELECT nspname, opfname, amname, rolname
298 FROM pg_opfamily o, pg_am m, pg_namespace n, pg_authid a
299 WHERE o.opfmethod = m.oid AND o.opfnamespace = n.oid AND o.opfowner = a.oid
300 AND n.nspname IN ('alt_nsp1', 'alt_nsp2')
301 AND NOT opfname LIKE 'alt_opc%'
302 ORDER BY nspname, opfname;
303 nspname | opfname | amname | rolname
304 ----------+----------+--------+---------------------
305 alt_nsp1 | alt_opf2 | hash | regress_alter_user2
306 alt_nsp1 | alt_opf3 | hash | regress_alter_user1
307 alt_nsp1 | alt_opf4 | hash | regress_alter_user2
308 alt_nsp2 | alt_opf2 | hash | regress_alter_user3
311 SELECT nspname, opcname, amname, rolname
312 FROM pg_opclass o, pg_am m, pg_namespace n, pg_authid a
313 WHERE o.opcmethod = m.oid AND o.opcnamespace = n.oid AND o.opcowner = a.oid
314 AND n.nspname IN ('alt_nsp1', 'alt_nsp2')
315 ORDER BY nspname, opcname;
316 nspname | opcname | amname | rolname
317 ----------+----------+--------+---------------------
318 alt_nsp1 | alt_opc2 | hash | regress_alter_user2
319 alt_nsp1 | alt_opc3 | hash | regress_alter_user1
320 alt_nsp1 | alt_opc4 | hash | regress_alter_user2
321 alt_nsp2 | alt_opc2 | hash | regress_alter_user3
324 -- ALTER OPERATOR FAMILY ... ADD/DROP
325 -- Should work. Textbook case of CREATE / ALTER ADD / ALTER DROP / DROP
327 CREATE OPERATOR FAMILY alt_opf4 USING btree;
328 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD
330 OPERATOR 1 < (int4, int2) ,
331 OPERATOR 2 <= (int4, int2) ,
332 OPERATOR 3 = (int4, int2) ,
333 OPERATOR 4 >= (int4, int2) ,
334 OPERATOR 5 > (int4, int2) ,
335 FUNCTION 1 btint42cmp(int4, int2);
336 ALTER OPERATOR FAMILY alt_opf4 USING btree DROP
338 OPERATOR 1 (int4, int2) ,
339 OPERATOR 2 (int4, int2) ,
340 OPERATOR 3 (int4, int2) ,
341 OPERATOR 4 (int4, int2) ,
342 OPERATOR 5 (int4, int2) ,
343 FUNCTION 1 (int4, int2) ;
344 DROP OPERATOR FAMILY alt_opf4 USING btree;
346 -- Should fail. Invalid values for ALTER OPERATOR FAMILY .. ADD / DROP
347 CREATE OPERATOR FAMILY alt_opf4 USING btree;
348 ALTER OPERATOR FAMILY alt_opf4 USING invalid_index_method ADD OPERATOR 1 < (int4, int2); -- invalid indexing_method
349 ERROR: access method "invalid_index_method" does not exist
350 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 6 < (int4, int2); -- operator number should be between 1 and 5
351 ERROR: invalid operator number 6, must be between 1 and 5
352 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 0 < (int4, int2); -- operator number should be between 1 and 5
353 ERROR: invalid operator number 0, must be between 1 and 5
354 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types
355 ERROR: operator argument types must be specified in ALTER OPERATOR FAMILY
356 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5
357 ERROR: invalid procedure number 0, must be between 1 and 2
358 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5
359 ERROR: invalid procedure number 6, must be between 1 and 2
360 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY
361 ERROR: STORAGE cannot be specified in ALTER OPERATOR FAMILY
362 DROP OPERATOR FAMILY alt_opf4 USING btree;
363 -- Should fail. Need to be SUPERUSER to do ALTER OPERATOR FAMILY .. ADD / DROP
365 CREATE ROLE regress_alter_user5 NOSUPERUSER;
366 CREATE OPERATOR FAMILY alt_opf5 USING btree;
367 SET ROLE regress_alter_user5;
368 ALTER OPERATOR FAMILY alt_opf5 USING btree ADD OPERATOR 1 < (int4, int2), FUNCTION 1 btint42cmp(int4, int2);
369 ERROR: must be superuser to alter an operator family
371 ERROR: current transaction is aborted, commands ignored until end of transaction block
372 DROP OPERATOR FAMILY alt_opf5 USING btree;
373 ERROR: current transaction is aborted, commands ignored until end of transaction block
375 -- Should fail. Need rights to namespace for ALTER OPERATOR FAMILY .. ADD / DROP
377 CREATE ROLE regress_alter_user6;
378 CREATE SCHEMA alt_nsp6;
379 REVOKE ALL ON SCHEMA alt_nsp6 FROM regress_alter_user6;
380 CREATE OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree;
381 SET ROLE regress_alter_user6;
382 ALTER OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree ADD OPERATOR 1 < (int4, int2);
383 ERROR: permission denied for schema alt_nsp6
385 -- Should fail. Only two arguments required for ALTER OPERATOR FAMILY ... DROP OPERATOR
386 CREATE OPERATOR FAMILY alt_opf7 USING btree;
387 ALTER OPERATOR FAMILY alt_opf7 USING btree ADD OPERATOR 1 < (int4, int2);
388 ALTER OPERATOR FAMILY alt_opf7 USING btree DROP OPERATOR 1 (int4, int2, int8);
389 ERROR: one or two argument types must be specified
390 DROP OPERATOR FAMILY alt_opf7 USING btree;
391 -- Should work. During ALTER OPERATOR FAMILY ... DROP OPERATOR
392 -- when left type is the same as right type, a DROP with only one argument type should work
393 CREATE OPERATOR FAMILY alt_opf8 USING btree;
394 ALTER OPERATOR FAMILY alt_opf8 USING btree ADD OPERATOR 1 < (int4, int4);
395 DROP OPERATOR FAMILY alt_opf8 USING btree;
396 -- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY
397 CREATE OPERATOR FAMILY alt_opf9 USING gist;
398 ALTER OPERATOR FAMILY alt_opf9 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
399 DROP OPERATOR FAMILY alt_opf9 USING gist;
400 -- Should fail. Ensure correct ordering methods in ALTER OPERATOR FAMILY ... ADD OPERATOR .. FOR ORDER BY
401 CREATE OPERATOR FAMILY alt_opf10 USING btree;
402 ALTER OPERATOR FAMILY alt_opf10 USING btree ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
403 ERROR: access method "btree" does not support ordering operators
404 DROP OPERATOR FAMILY alt_opf10 USING btree;
405 -- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY
406 CREATE OPERATOR FAMILY alt_opf11 USING gist;
407 ALTER OPERATOR FAMILY alt_opf11 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
408 ALTER OPERATOR FAMILY alt_opf11 USING gist DROP OPERATOR 1 (int4, int4);
409 DROP OPERATOR FAMILY alt_opf11 USING gist;
410 -- Should fail. btree comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION
412 CREATE OPERATOR FAMILY alt_opf12 USING btree;
413 CREATE FUNCTION fn_opf12 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
414 ALTER OPERATOR FAMILY alt_opf12 USING btree ADD FUNCTION 1 fn_opf12(int4, int2);
415 ERROR: btree comparison procedures must return integer
416 DROP OPERATOR FAMILY alt_opf12 USING btree;
417 ERROR: current transaction is aborted, commands ignored until end of transaction block
419 -- Should fail. hash comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION
421 CREATE OPERATOR FAMILY alt_opf13 USING hash;
422 CREATE FUNCTION fn_opf13 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
423 ALTER OPERATOR FAMILY alt_opf13 USING hash ADD FUNCTION 1 fn_opf13(int4);
424 ERROR: hash procedures must return integer
425 DROP OPERATOR FAMILY alt_opf13 USING hash;
426 ERROR: current transaction is aborted, commands ignored until end of transaction block
428 -- Should fail. btree comparison functions should have two arguments in ALTER OPERATOR FAMILY ... ADD FUNCTION
430 CREATE OPERATOR FAMILY alt_opf14 USING btree;
431 CREATE FUNCTION fn_opf14 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
432 ALTER OPERATOR FAMILY alt_opf14 USING btree ADD FUNCTION 1 fn_opf14(int4);
433 ERROR: btree comparison procedures must have two arguments
434 DROP OPERATOR FAMILY alt_opf14 USING btree;
435 ERROR: current transaction is aborted, commands ignored until end of transaction block
437 -- Should fail. hash comparison functions should have one argument in ALTER OPERATOR FAMILY ... ADD FUNCTION
439 CREATE OPERATOR FAMILY alt_opf15 USING hash;
440 CREATE FUNCTION fn_opf15 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
441 ALTER OPERATOR FAMILY alt_opf15 USING hash ADD FUNCTION 1 fn_opf15(int4, int2);
442 ERROR: hash procedures must have one argument
443 DROP OPERATOR FAMILY alt_opf15 USING hash;
444 ERROR: current transaction is aborted, commands ignored until end of transaction block
446 -- Should fail. In gist throw an error when giving different data types for function argument
447 -- without defining left / right type in ALTER OPERATOR FAMILY ... ADD FUNCTION
448 CREATE OPERATOR FAMILY alt_opf16 USING gist;
449 ALTER OPERATOR FAMILY alt_opf16 USING gist ADD FUNCTION 1 btint42cmp(int4, int2);
450 ERROR: associated data types must be specified for index support procedure
451 DROP OPERATOR FAMILY alt_opf16 USING gist;
452 -- Should fail. duplicate operator number / function number in ALTER OPERATOR FAMILY ... ADD FUNCTION
453 CREATE OPERATOR FAMILY alt_opf17 USING btree;
454 ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4), OPERATOR 1 < (int4, int4); -- operator # appears twice in same statement
455 ERROR: operator number 1 for (integer,integer) appears more than once
456 ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested first-time
457 ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested again in separate statement
458 ERROR: operator 1(integer,integer) already exists in operator family "alt_opf17"
459 ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
460 OPERATOR 1 < (int4, int2) ,
461 OPERATOR 2 <= (int4, int2) ,
462 OPERATOR 3 = (int4, int2) ,
463 OPERATOR 4 >= (int4, int2) ,
464 OPERATOR 5 > (int4, int2) ,
465 FUNCTION 1 btint42cmp(int4, int2) ,
466 FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears twice in same statement
467 ERROR: procedure number 1 for (integer,smallint) appears more than once
468 ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
469 OPERATOR 1 < (int4, int2) ,
470 OPERATOR 2 <= (int4, int2) ,
471 OPERATOR 3 = (int4, int2) ,
472 OPERATOR 4 >= (int4, int2) ,
473 OPERATOR 5 > (int4, int2) ,
474 FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears first time
475 ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
476 OPERATOR 1 < (int4, int2) ,
477 OPERATOR 2 <= (int4, int2) ,
478 OPERATOR 3 = (int4, int2) ,
479 OPERATOR 4 >= (int4, int2) ,
480 OPERATOR 5 > (int4, int2) ,
481 FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 requested again in separate statement
482 ERROR: operator 1(integer,smallint) already exists in operator family "alt_opf17"
483 DROP OPERATOR FAMILY alt_opf17 USING btree;
484 -- Should fail. Ensure that DROP requests for missing OPERATOR / FUNCTIONS
485 -- return appropriate message in ALTER OPERATOR FAMILY ... DROP OPERATOR / FUNCTION
486 CREATE OPERATOR FAMILY alt_opf18 USING btree;
487 ALTER OPERATOR FAMILY alt_opf18 USING btree DROP OPERATOR 1 (int4, int4);
488 ERROR: operator 1(integer,integer) does not exist in operator family "alt_opf18"
489 ALTER OPERATOR FAMILY alt_opf18 USING btree ADD
490 OPERATOR 1 < (int4, int2) ,
491 OPERATOR 2 <= (int4, int2) ,
492 OPERATOR 3 = (int4, int2) ,
493 OPERATOR 4 >= (int4, int2) ,
494 OPERATOR 5 > (int4, int2) ,
495 FUNCTION 1 btint42cmp(int4, int2);
496 ALTER OPERATOR FAMILY alt_opf18 USING btree DROP FUNCTION 2 (int4, int4);
497 ERROR: function 2(integer,integer) does not exist in operator family "alt_opf18"
498 DROP OPERATOR FAMILY alt_opf18 USING btree;
502 SET SESSION AUTHORIZATION regress_alter_user1;
503 CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
504 CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
505 CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
506 ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict)
507 ERROR: statistics object "alt_stat2" already exists in schema "alt_nsp1"
508 ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- failed (name conflict)
509 ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user2; -- failed (no role membership)
510 ERROR: must be member of role "regress_alter_user2"
511 ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user3; -- OK
512 ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK
513 SET SESSION AUTHORIZATION regress_alter_user2;
514 CREATE TABLE alt_regress_2 (a INTEGER, b INTEGER);
515 CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_2;
516 CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_2;
517 ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
518 ERROR: must be owner of statistics object alt_stat3
519 ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
520 ALTER STATISTICS alt_stat3 OWNER TO regress_alter_user2; -- failed (not owner)
521 ERROR: must be owner of statistics object alt_stat3
522 ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user3; -- failed (no role membership)
523 ERROR: must be member of role "regress_alter_user3"
524 ALTER STATISTICS alt_stat3 SET SCHEMA alt_nsp2; -- failed (not owner)
525 ERROR: must be owner of statistics object alt_stat3
526 ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- failed (name conflict)
527 ERROR: statistics object "alt_stat2" already exists in schema "alt_nsp2"
528 RESET SESSION AUTHORIZATION;
529 SELECT nspname, stxname, rolname
530 FROM pg_statistic_ext s, pg_namespace n, pg_authid a
531 WHERE s.stxnamespace = n.oid AND s.stxowner = a.oid
532 AND n.nspname in ('alt_nsp1', 'alt_nsp2')
533 ORDER BY nspname, stxname;
534 nspname | stxname | rolname
535 ----------+-----------+---------------------
536 alt_nsp1 | alt_stat2 | regress_alter_user2
537 alt_nsp1 | alt_stat3 | regress_alter_user1
538 alt_nsp1 | alt_stat4 | regress_alter_user2
539 alt_nsp2 | alt_stat2 | regress_alter_user3
543 -- Text Search Dictionary
545 SET SESSION AUTHORIZATION regress_alter_user1;
546 CREATE TEXT SEARCH DICTIONARY alt_ts_dict1 (template=simple);
547 CREATE TEXT SEARCH DICTIONARY alt_ts_dict2 (template=simple);
548 ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict2; -- failed (name conflict)
549 ERROR: text search dictionary "alt_ts_dict2" already exists in schema "alt_nsp1"
550 ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict3; -- OK
551 ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_user2; -- failed (no role membership)
552 ERROR: must be member of role "regress_alter_user2"
553 ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_user3; -- OK
554 ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- OK
555 SET SESSION AUTHORIZATION regress_alter_user2;
556 CREATE TEXT SEARCH DICTIONARY alt_ts_dict1 (template=simple);
557 CREATE TEXT SEARCH DICTIONARY alt_ts_dict2 (template=simple);
558 ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 RENAME TO alt_ts_dict4; -- failed (not owner)
559 ERROR: must be owner of text search dictionary alt_ts_dict3
560 ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict4; -- OK
561 ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 OWNER TO regress_alter_user2; -- failed (not owner)
562 ERROR: must be owner of text search dictionary alt_ts_dict3
563 ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_user3; -- failed (no role membership)
564 ERROR: must be member of role "regress_alter_user3"
565 ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 SET SCHEMA alt_nsp2; -- failed (not owner)
566 ERROR: must be owner of text search dictionary alt_ts_dict3
567 ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- failed (name conflict)
568 ERROR: text search dictionary "alt_ts_dict2" already exists in schema "alt_nsp2"
569 RESET SESSION AUTHORIZATION;
570 SELECT nspname, dictname, rolname
571 FROM pg_ts_dict t, pg_namespace n, pg_authid a
572 WHERE t.dictnamespace = n.oid AND t.dictowner = a.oid
573 AND n.nspname in ('alt_nsp1', 'alt_nsp2')
574 ORDER BY nspname, dictname;
575 nspname | dictname | rolname
576 ----------+--------------+---------------------
577 alt_nsp1 | alt_ts_dict2 | regress_alter_user2
578 alt_nsp1 | alt_ts_dict3 | regress_alter_user1
579 alt_nsp1 | alt_ts_dict4 | regress_alter_user2
580 alt_nsp2 | alt_ts_dict2 | regress_alter_user3
584 -- Text Search Configuration
586 SET SESSION AUTHORIZATION regress_alter_user1;
587 CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english);
588 CREATE TEXT SEARCH CONFIGURATION alt_ts_conf2 (copy=english);
589 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf2; -- failed (name conflict)
590 ERROR: text search configuration "alt_ts_conf2" already exists in schema "alt_nsp1"
591 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf3; -- OK
592 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_user2; -- failed (no role membership)
593 ERROR: must be member of role "regress_alter_user2"
594 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_user3; -- OK
595 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- OK
596 SET SESSION AUTHORIZATION regress_alter_user2;
597 CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english);
598 CREATE TEXT SEARCH CONFIGURATION alt_ts_conf2 (copy=english);
599 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 RENAME TO alt_ts_conf4; -- failed (not owner)
600 ERROR: must be owner of text search configuration alt_ts_conf3
601 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf4; -- OK
602 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 OWNER TO regress_alter_user2; -- failed (not owner)
603 ERROR: must be owner of text search configuration alt_ts_conf3
604 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_user3; -- failed (no role membership)
605 ERROR: must be member of role "regress_alter_user3"
606 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 SET SCHEMA alt_nsp2; -- failed (not owner)
607 ERROR: must be owner of text search configuration alt_ts_conf3
608 ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- failed (name conflict)
609 ERROR: text search configuration "alt_ts_conf2" already exists in schema "alt_nsp2"
610 RESET SESSION AUTHORIZATION;
611 SELECT nspname, cfgname, rolname
612 FROM pg_ts_config t, pg_namespace n, pg_authid a
613 WHERE t.cfgnamespace = n.oid AND t.cfgowner = a.oid
614 AND n.nspname in ('alt_nsp1', 'alt_nsp2')
615 ORDER BY nspname, cfgname;
616 nspname | cfgname | rolname
617 ----------+--------------+---------------------
618 alt_nsp1 | alt_ts_conf2 | regress_alter_user2
619 alt_nsp1 | alt_ts_conf3 | regress_alter_user1
620 alt_nsp1 | alt_ts_conf4 | regress_alter_user2
621 alt_nsp2 | alt_ts_conf2 | regress_alter_user3
625 -- Text Search Template
627 CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize);
628 CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize);
629 ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp2; -- failed (name conflict)
630 ERROR: text search template "alt_ts_temp2" already exists in schema "alt_nsp1"
631 ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp3; -- OK
632 ALTER TEXT SEARCH TEMPLATE alt_ts_temp2 SET SCHEMA alt_nsp2; -- OK
633 CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize);
634 ALTER TEXT SEARCH TEMPLATE alt_ts_temp2 SET SCHEMA alt_nsp2; -- failed (name conflict)
635 ERROR: text search template "alt_ts_temp2" already exists in schema "alt_nsp2"
636 SELECT nspname, tmplname
637 FROM pg_ts_template t, pg_namespace n
638 WHERE t.tmplnamespace = n.oid AND nspname like 'alt_nsp%'
639 ORDER BY nspname, tmplname;
641 ----------+--------------
642 alt_nsp1 | alt_ts_temp2
643 alt_nsp1 | alt_ts_temp3
644 alt_nsp2 | alt_ts_temp2
648 -- Text Search Parser
650 CREATE TEXT SEARCH PARSER alt_ts_prs1
651 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
652 CREATE TEXT SEARCH PARSER alt_ts_prs2
653 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
654 ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs2; -- failed (name conflict)
655 ERROR: text search parser "alt_ts_prs2" already exists in schema "alt_nsp1"
656 ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs3; -- OK
657 ALTER TEXT SEARCH PARSER alt_ts_prs2 SET SCHEMA alt_nsp2; -- OK
658 CREATE TEXT SEARCH PARSER alt_ts_prs2
659 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
660 ALTER TEXT SEARCH PARSER alt_ts_prs2 SET SCHEMA alt_nsp2; -- failed (name conflict)
661 ERROR: text search parser "alt_ts_prs2" already exists in schema "alt_nsp2"
662 SELECT nspname, prsname
663 FROM pg_ts_parser t, pg_namespace n
664 WHERE t.prsnamespace = n.oid AND nspname like 'alt_nsp%'
665 ORDER BY nspname, prsname;
667 ----------+-------------
668 alt_nsp1 | alt_ts_prs2
669 alt_nsp1 | alt_ts_prs3
670 alt_nsp2 | alt_ts_prs2
674 --- Cleanup resources
676 \set VERBOSITY terse \\ -- suppress cascade details
677 DROP FOREIGN DATA WRAPPER alt_fdw2 CASCADE;
678 NOTICE: drop cascades to server alt_fserv2
679 DROP FOREIGN DATA WRAPPER alt_fdw3 CASCADE;
680 NOTICE: drop cascades to server alt_fserv3
681 DROP LANGUAGE alt_lang2 CASCADE;
682 DROP LANGUAGE alt_lang3 CASCADE;
683 DROP SCHEMA alt_nsp1 CASCADE;
684 NOTICE: drop cascades to 28 other objects
685 DROP SCHEMA alt_nsp2 CASCADE;
686 NOTICE: drop cascades to 9 other objects
687 DROP USER regress_alter_user1;
688 DROP USER regress_alter_user2;
689 DROP USER regress_alter_user3;