From ece26987c20c679edde74775ef0c56bff32bd1bc Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 1 Oct 2012 10:42:03 -0300 Subject: [PATCH] Remove collations from generic ALTER test The error messages they generate are not portable enough. Also, since the only point of the alter_generic_1 expected file was to cover platforms with no collation support, it's now useless, so remove it. --- src/test/regress/expected/alter_generic.out | 51 +- src/test/regress/expected/alter_generic_1.out | 545 ------------------ src/test/regress/sql/alter_generic.sql | 35 +- 3 files changed, 10 insertions(+), 621 deletions(-) delete mode 100644 src/test/regress/expected/alter_generic_1.out diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out index f6edb4c88f..3beddd783e 100644 --- a/src/test/regress/expected/alter_generic.out +++ b/src/test/regress/expected/alter_generic.out @@ -100,46 +100,9 @@ SELECT n.nspname, proname, prorettype::regtype, proisagg, a.rolname (8 rows) -- --- Collation +-- We would test collations here, but it's not possible because the error +-- messages tend to be nonportable. -- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE COLLATION alt_coll1 (locale = 'C'); -CREATE COLLATION alt_coll2 (locale = 'C'); --- can't test this: the error message includes the encoding name --- ALTER COLLATION alt_coll1 RENAME TO alt_coll2; -- failed (name conflict) -ALTER COLLATION alt_coll1 RENAME TO alt_coll3; -- OK -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user3; -- OK -ALTER COLLATION alt_coll2 SET SCHEMA alt_nsp2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE COLLATION alt_coll1 (locale = 'C'); -CREATE COLLATION alt_coll2 (locale = 'C'); -ALTER COLLATION alt_coll3 RENAME TO alt_coll4; -- failed (not owner) -ERROR: must be owner of collation alt_coll3 -ALTER COLLATION alt_coll1 RENAME TO alt_coll4; -- OK -ALTER COLLATION alt_coll3 OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of collation alt_coll3 -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER COLLATION alt_coll3 SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of collation alt_coll3 --- can't test this: the error message includes the encoding name --- ALTER COLLATION alt_coll2 SET SCHEMA alt_nsp2; -- failed (name conflict) -RESET SESSION AUTHORIZATION; -SELECT n.nspname, c.collname, a.rolname - FROM pg_collation c, pg_namespace n, pg_authid a - WHERE c.collnamespace = n.oid AND c.collowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY n.nspname, c.collname; - nspname | collname | rolname -----------+-----------+--------------------- - alt_nsp1 | alt_coll2 | regtest_alter_user2 - alt_nsp1 | alt_coll3 | regtest_alter_user1 - alt_nsp1 | alt_coll4 | regtest_alter_user2 - alt_nsp2 | alt_coll2 | regtest_alter_user3 -(4 rows) - -- -- Conversion -- @@ -316,6 +279,7 @@ ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user3; -- fail ERROR: must be member of role "regtest_alter_user3" ALTER OPERATOR FAMILY alt_opf3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) ERROR: must be owner of operator family alt_opf3 +-- can't test this: the error message includes the raw oid of namespace -- ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) ALTER OPERATOR CLASS alt_opc3 USING hash RENAME TO alt_opc4; -- failed (not owner) ERROR: must be owner of operator class alt_opc3 @@ -326,6 +290,7 @@ ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user3; -- faile ERROR: must be member of role "regtest_alter_user3" ALTER OPERATOR CLASS alt_opc3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) ERROR: must be owner of operator class alt_opc3 +-- can't test this: the error message includes the raw oid of namespace -- ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) RESET SESSION AUTHORIZATION; SELECT nspname, opfname, amname, rolname @@ -498,16 +463,13 @@ DROP LANGUAGE alt_lang3 CASCADE; DROP LANGUAGE alt_lang4 CASCADE; ERROR: language "alt_lang4" does not exist DROP SCHEMA alt_nsp1 CASCADE; -NOTICE: drop cascades to 29 other objects +NOTICE: drop cascades to 26 other objects DETAIL: drop cascades to function alt_func3(integer) drop cascades to function alt_agg3(integer) drop cascades to function alt_func4(integer) drop cascades to function alt_func2(integer) drop cascades to function alt_agg4(integer) drop cascades to function alt_agg2(integer) -drop cascades to collation alt_coll3 -drop cascades to collation alt_coll4 -drop cascades to collation alt_coll2 drop cascades to conversion alt_conv3 drop cascades to conversion alt_conv4 drop cascades to conversion alt_conv2 @@ -529,10 +491,9 @@ drop cascades to text search template alt_ts_temp2 drop cascades to text search parser alt_ts_prs3 drop cascades to text search parser alt_ts_prs2 DROP SCHEMA alt_nsp2 CASCADE; -NOTICE: drop cascades to 10 other objects +NOTICE: drop cascades to 9 other objects DETAIL: drop cascades to function alt_nsp2.alt_func2(integer) drop cascades to function alt_nsp2.alt_agg2(integer) -drop cascades to collation alt_coll2 drop cascades to conversion alt_conv2 drop cascades to operator alt_nsp2.@-@(integer,integer) drop cascades to operator family alt_nsp2.alt_opf2 for access method hash diff --git a/src/test/regress/expected/alter_generic_1.out b/src/test/regress/expected/alter_generic_1.out deleted file mode 100644 index 656fed450f..0000000000 --- a/src/test/regress/expected/alter_generic_1.out +++ /dev/null @@ -1,545 +0,0 @@ --- --- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA} --- --- Clean up in case a prior regression run failed -SET client_min_messages TO 'warning'; -DROP ROLE IF EXISTS regtest_alter_user1; -DROP ROLE IF EXISTS regtest_alter_user2; -DROP ROLE IF EXISTS regtest_alter_user3; -RESET client_min_messages; -CREATE USER regtest_alter_user3; -CREATE USER regtest_alter_user2; -CREATE USER regtest_alter_user1 IN ROLE regtest_alter_user3; -CREATE SCHEMA alt_nsp1; -CREATE SCHEMA alt_nsp2; -GRANT ALL ON SCHEMA alt_nsp1, alt_nsp2 TO public; -SET search_path = alt_nsp1, public; --- --- Function and Aggregate --- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql - AS 'SELECT $1 + 1'; -CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql - AS 'SELECT $1 - 1'; -CREATE AGGREGATE alt_agg1 ( - sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 0 -); -CREATE AGGREGATE alt_agg2 ( - sfunc1 = int4mi, basetype = int4, stype1 = int4, initcond = 0 -); -ALTER AGGREGATE alt_func1(int) RENAME TO alt_func3; -- failed (not aggregate) -ERROR: function alt_func1(integer) is not an aggregate -ALTER AGGREGATE alt_func1(int) OWNER TO regtest_alter_user3; -- failed (not aggregate) -ERROR: function alt_func1(integer) is not an aggregate -ALTER AGGREGATE alt_func1(int) SET SCHEMA alt_nsp2; -- failed (not aggregate) -ERROR: function alt_func1(integer) is not an aggregate -ALTER FUNCTION alt_func1(int) RENAME TO alt_func2; -- failed (name conflict) -ERROR: function alt_func2(integer) already exists in schema "alt_nsp1" -ALTER FUNCTION alt_func1(int) RENAME TO alt_func3; -- OK -ALTER FUNCTION alt_func2(int) OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER FUNCTION alt_func2(int) OWNER TO regtest_alter_user3; -- OK -ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp2; -- OK -ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg2; -- failed (name conflict) -ERROR: function alt_agg2(integer) already exists in schema "alt_nsp1" -ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg3; -- OK -ALTER AGGREGATE alt_agg2(int) OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER AGGREGATE alt_agg2(int) OWNER TO regtest_alter_user3; -- OK -ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql - AS 'SELECT $1 + 2'; -CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql - AS 'SELECT $1 - 2'; -CREATE AGGREGATE alt_agg1 ( - sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 100 -); -CREATE AGGREGATE alt_agg2 ( - sfunc1 = int4mi, basetype = int4, stype1 = int4, initcond = -100 -); -ALTER FUNCTION alt_func3(int) RENAME TO alt_func4; -- failed (not owner) -ERROR: must be owner of function alt_func3 -ALTER FUNCTION alt_func1(int) RENAME TO alt_func4; -- OK -ALTER FUNCTION alt_func3(int) OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of function alt_func3 -ALTER FUNCTION alt_func2(int) OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER FUNCTION alt_func3(int) SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of function alt_func3 -ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp2; -- failed (name conflicts) -ERROR: function "alt_func2" already exists in schema "alt_nsp2" -ALTER AGGREGATE alt_agg3(int) RENAME TO alt_agg4; -- failed (not owner) -ERROR: must be owner of function alt_agg3 -ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg4; -- OK -ALTER AGGREGATE alt_agg3(int) OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of function alt_agg3 -ALTER AGGREGATE alt_agg2(int) OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER AGGREGATE alt_agg3(int) SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of function alt_agg3 -ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- failed (name conflict) -ERROR: function "alt_agg2" already exists in schema "alt_nsp2" -RESET SESSION AUTHORIZATION; -SELECT n.nspname, proname, prorettype::regtype, proisagg, a.rolname - FROM pg_proc p, pg_namespace n, pg_authid a - WHERE p.pronamespace = n.oid AND p.proowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY nspname, proname; - nspname | proname | prorettype | proisagg | rolname -----------+-----------+------------+----------+--------------------- - alt_nsp1 | alt_agg2 | integer | t | regtest_alter_user2 - alt_nsp1 | alt_agg3 | integer | t | regtest_alter_user1 - alt_nsp1 | alt_agg4 | integer | t | regtest_alter_user2 - alt_nsp1 | alt_func2 | integer | f | regtest_alter_user2 - alt_nsp1 | alt_func3 | integer | f | regtest_alter_user1 - alt_nsp1 | alt_func4 | integer | f | regtest_alter_user2 - alt_nsp2 | alt_agg2 | integer | t | regtest_alter_user3 - alt_nsp2 | alt_func2 | integer | f | regtest_alter_user3 -(8 rows) - --- --- Collation --- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE COLLATION alt_coll1 (locale = 'C'); -ERROR: nondefault collations are not supported on this platform -CREATE COLLATION alt_coll2 (locale = 'C'); -ERROR: nondefault collations are not supported on this platform --- can't test this: the error message includes the encoding name --- ALTER COLLATION alt_coll1 RENAME TO alt_coll2; -- failed (name conflict) -ALTER COLLATION alt_coll1 RENAME TO alt_coll3; -- OK -ERROR: collation "alt_coll1" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: collation "alt_coll2" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user3; -- OK -ERROR: collation "alt_coll2" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll2 SET SCHEMA alt_nsp2; -- OK -ERROR: collation "alt_coll2" for encoding "SQL_ASCII" does not exist -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE COLLATION alt_coll1 (locale = 'C'); -ERROR: nondefault collations are not supported on this platform -CREATE COLLATION alt_coll2 (locale = 'C'); -ERROR: nondefault collations are not supported on this platform -ALTER COLLATION alt_coll3 RENAME TO alt_coll4; -- failed (not owner) -ERROR: collation "alt_coll3" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll1 RENAME TO alt_coll4; -- OK -ERROR: collation "alt_coll1" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll3 OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: collation "alt_coll3" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: collation "alt_coll2" for encoding "SQL_ASCII" does not exist -ALTER COLLATION alt_coll3 SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: collation "alt_coll3" for encoding "SQL_ASCII" does not exist --- can't test this: the error message includes the encoding name --- ALTER COLLATION alt_coll2 SET SCHEMA alt_nsp2; -- failed (name conflict) -RESET SESSION AUTHORIZATION; -SELECT n.nspname, c.collname, a.rolname - FROM pg_collation c, pg_namespace n, pg_authid a - WHERE c.collnamespace = n.oid AND c.collowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY n.nspname, c.collname; - nspname | collname | rolname ----------+----------+--------- -(0 rows) - --- --- Conversion --- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE CONVERSION alt_conv1 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; -CREATE CONVERSION alt_conv2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; -ALTER CONVERSION alt_conv1 RENAME TO alt_conv2; -- failed (name conflict) -ERROR: conversion "alt_conv2" already exists in schema "alt_nsp1" -ALTER CONVERSION alt_conv1 RENAME TO alt_conv3; -- OK -ALTER CONVERSION alt_conv2 OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER CONVERSION alt_conv2 OWNER TO regtest_alter_user3; -- OK -ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE CONVERSION alt_conv1 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; -CREATE CONVERSION alt_conv2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; -ALTER CONVERSION alt_conv3 RENAME TO alt_conv4; -- failed (not owner) -ERROR: must be owner of conversion alt_conv3 -ALTER CONVERSION alt_conv1 RENAME TO alt_conv4; -- OK -ALTER CONVERSION alt_conv3 OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of conversion alt_conv3 -ALTER CONVERSION alt_conv2 OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER CONVERSION alt_conv3 SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of conversion alt_conv3 -ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- failed (name conflict) -ERROR: conversion alt_conv2 already exists in schema "alt_nsp2" -RESET SESSION AUTHORIZATION; -SELECT n.nspname, c.conname, a.rolname - FROM pg_conversion c, pg_namespace n, pg_authid a - WHERE c.connamespace = n.oid AND c.conowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY nspname, conname; - nspname | conname | rolname -----------+-----------+--------------------- - alt_nsp1 | alt_conv2 | regtest_alter_user2 - alt_nsp1 | alt_conv3 | regtest_alter_user1 - alt_nsp1 | alt_conv4 | regtest_alter_user2 - alt_nsp2 | alt_conv2 | regtest_alter_user3 -(4 rows) - --- --- Foreign Data Wrapper and Foreign Server --- -CREATE FOREIGN DATA WRAPPER alt_fdw1; -CREATE FOREIGN DATA WRAPPER alt_fdw2; -CREATE SERVER alt_fserv1 FOREIGN DATA WRAPPER alt_fdw1; -CREATE SERVER alt_fserv2 FOREIGN DATA WRAPPER alt_fdw2; -ALTER FOREIGN DATA WRAPPER alt_fdw1 RENAME TO alt_fdw2; -- failed (name conflict) -ERROR: foreign-data wrapper "alt_fdw2" already exists -ALTER FOREIGN DATA WRAPPER alt_fdw1 RENAME TO alt_fdw3; -- OK -ALTER SERVER alt_fserv1 RENAME TO alt_fserv2; -- failed (name conflict) -ERROR: server "alt_fserv2" already exists -ALTER SERVER alt_fserv1 RENAME TO alt_fserv3; -- OK -SELECT fdwname FROM pg_foreign_data_wrapper WHERE fdwname like 'alt_fdw%'; - fdwname ----------- - alt_fdw2 - alt_fdw3 -(2 rows) - -SELECT srvname FROM pg_foreign_server WHERE srvname like 'alt_fserv%'; - srvname ------------- - alt_fserv2 - alt_fserv3 -(2 rows) - --- --- Procedural Language --- -CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler; -CREATE LANGUAGE alt_lang2 HANDLER plpgsql_call_handler; -ALTER LANGUAGE alt_lang1 OWNER TO regtest_alter_user1; -- OK -ALTER LANGUAGE alt_lang2 OWNER TO regtest_alter_user2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user1; -ALTER LANGUAGE alt_lang1 RENAME TO alt_lang2; -- failed (name conflict) -ERROR: language "alt_lang2" already exists -ALTER LANGUAGE alt_lang2 RENAME TO alt_lang3; -- failed (not owner) -ERROR: must be owner of language alt_lang2 -ALTER LANGUAGE alt_lang1 RENAME TO alt_lang3; -- OK -ALTER LANGUAGE alt_lang2 OWNER TO regtest_alter_user3; -- failed (not owner) -ERROR: must be owner of language alt_lang2 -ALTER LANGUAGE alt_lang3 OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER LANGUAGE alt_lang3 OWNER TO regtest_alter_user3; -- OK -RESET SESSION AUTHORIZATION; -SELECT lanname, a.rolname - FROM pg_language l, pg_authid a - WHERE l.lanowner = a.oid AND l.lanname like 'alt_lang%' - ORDER BY lanname; - lanname | rolname ------------+--------------------- - alt_lang2 | regtest_alter_user2 - alt_lang3 | regtest_alter_user3 -(2 rows) - --- --- Operator --- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi ); -CREATE OPERATOR @+@ ( leftarg = int4, rightarg = int4, procedure = int4pl ); -ALTER OPERATOR @+@(int4, int4) OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER OPERATOR @+@(int4, int4) OWNER TO regtest_alter_user3; -- OK -ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi ); -ALTER OPERATOR @+@(int4, int4) OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of operator @+@ -ALTER OPERATOR @-@(int4, int4) OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER OPERATOR @+@(int4, int4) SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of operator @+@ --- can't test this: the error message includes the raw oid of namespace --- ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- failed (name conflict) -RESET SESSION AUTHORIZATION; -SELECT n.nspname, oprname, a.rolname, - oprleft::regtype, oprright::regtype, oprcode::regproc - FROM pg_operator o, pg_namespace n, pg_authid a - WHERE o.oprnamespace = n.oid AND o.oprowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY nspname, oprname; - nspname | oprname | rolname | oprleft | oprright | oprcode -----------+---------+---------------------+---------+----------+--------- - alt_nsp1 | @+@ | regtest_alter_user3 | integer | integer | int4pl - alt_nsp1 | @-@ | regtest_alter_user2 | integer | integer | int4mi - alt_nsp2 | @-@ | regtest_alter_user1 | integer | integer | int4mi -(3 rows) - --- --- OpFamily and OpClass --- -CREATE OPERATOR FAMILY alt_opf1 USING hash; -CREATE OPERATOR FAMILY alt_opf2 USING hash; -ALTER OPERATOR FAMILY alt_opf1 USING hash OWNER TO regtest_alter_user1; -ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user1; -CREATE OPERATOR CLASS alt_opc1 FOR TYPE uuid USING hash AS STORAGE uuid; -CREATE OPERATOR CLASS alt_opc2 FOR TYPE uuid USING hash AS STORAGE uuid; -ALTER OPERATOR CLASS alt_opc1 USING hash OWNER TO regtest_alter_user1; -ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user1; -SET SESSION AUTHORIZATION regtest_alter_user1; -ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf2; -- failed (name conflict) -ERROR: operator family "alt_opf2" for access method "hash" already exists in schema "alt_nsp1" -ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf3; -- OK -ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user3; -- OK -ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- OK -ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc2; -- failed (name conflict) -ERROR: operator class "alt_opc2" for access method "hash" already exists in schema "alt_nsp1" -ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc3; -- OK -ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user3; -- OK -ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- OK -RESET SESSION AUTHORIZATION; -CREATE OPERATOR FAMILY alt_opf1 USING hash; -CREATE OPERATOR FAMILY alt_opf2 USING hash; -ALTER OPERATOR FAMILY alt_opf1 USING hash OWNER TO regtest_alter_user2; -ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user2; -CREATE OPERATOR CLASS alt_opc1 FOR TYPE macaddr USING hash AS STORAGE macaddr; -CREATE OPERATOR CLASS alt_opc2 FOR TYPE macaddr USING hash AS STORAGE macaddr; -ALTER OPERATOR CLASS alt_opc1 USING hash OWNER TO regtest_alter_user2; -ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user2; -SET SESSION AUTHORIZATION regtest_alter_user2; -ALTER OPERATOR FAMILY alt_opf3 USING hash RENAME TO alt_opf4; -- failed (not owner) -ERROR: must be owner of operator family alt_opf3 -ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf4; -- OK -ALTER OPERATOR FAMILY alt_opf3 USING hash OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of operator family alt_opf3 -ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER OPERATOR FAMILY alt_opf3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of operator family alt_opf3 --- ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) -ALTER OPERATOR CLASS alt_opc3 USING hash RENAME TO alt_opc4; -- failed (not owner) -ERROR: must be owner of operator class alt_opc3 -ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc4; -- OK -ALTER OPERATOR CLASS alt_opc3 USING hash OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of operator class alt_opc3 -ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER OPERATOR CLASS alt_opc3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of operator class alt_opc3 --- ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) -RESET SESSION AUTHORIZATION; -SELECT nspname, opfname, amname, rolname - FROM pg_opfamily o, pg_am m, pg_namespace n, pg_authid a - WHERE o.opfmethod = m.oid AND o.opfnamespace = n.oid AND o.opfowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - AND NOT opfname LIKE 'alt_opc%' - ORDER BY nspname, opfname; - nspname | opfname | amname | rolname -----------+----------+--------+--------------------- - alt_nsp1 | alt_opf2 | hash | regtest_alter_user2 - alt_nsp1 | alt_opf3 | hash | regtest_alter_user1 - alt_nsp1 | alt_opf4 | hash | regtest_alter_user2 - alt_nsp2 | alt_opf2 | hash | regtest_alter_user3 -(4 rows) - -SELECT nspname, opcname, amname, rolname - FROM pg_opclass o, pg_am m, pg_namespace n, pg_authid a - WHERE o.opcmethod = m.oid AND o.opcnamespace = n.oid AND o.opcowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY nspname, opcname; - nspname | opcname | amname | rolname -----------+----------+--------+--------------------- - alt_nsp1 | alt_opc2 | hash | regtest_alter_user2 - alt_nsp1 | alt_opc3 | hash | regtest_alter_user1 - alt_nsp1 | alt_opc4 | hash | regtest_alter_user2 - alt_nsp2 | alt_opc2 | hash | regtest_alter_user3 -(4 rows) - --- --- Text Search Dictionary --- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE TEXT SEARCH DICTIONARY alt_ts_dict1 (template=simple); -CREATE TEXT SEARCH DICTIONARY alt_ts_dict2 (template=simple); -ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict2; -- failed (name conflict) -ERROR: text search dictionary "alt_ts_dict2" already exists -ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict3; -- OK -ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regtest_alter_user3; -- OK -ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE TEXT SEARCH DICTIONARY alt_ts_dict1 (template=simple); -CREATE TEXT SEARCH DICTIONARY alt_ts_dict2 (template=simple); -ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 RENAME TO alt_ts_dict4; -- failed (not owner) -ERROR: must be owner of text search dictionary alt_ts_dict3 -ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict4; -- OK -ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of text search dictionary alt_ts_dict3 -ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of text search dictionary alt_ts_dict3 -ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- failed (name conflict) -ERROR: text search dictionary alt_ts_dict2 already exists in schema "alt_nsp2" -RESET SESSION AUTHORIZATION; -SELECT nspname, dictname, rolname - FROM pg_ts_dict t, pg_namespace n, pg_authid a - WHERE t.dictnamespace = n.oid AND t.dictowner = a.oid - AND n.nspname in ('alt_nsp1', 'alt_nsp2') - ORDER BY nspname, dictname; - nspname | dictname | rolname -----------+--------------+--------------------- - alt_nsp1 | alt_ts_dict2 | regtest_alter_user2 - alt_nsp1 | alt_ts_dict3 | regtest_alter_user1 - alt_nsp1 | alt_ts_dict4 | regtest_alter_user2 - alt_nsp2 | alt_ts_dict2 | regtest_alter_user3 -(4 rows) - --- --- Text Search Configuration --- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english); -CREATE TEXT SEARCH CONFIGURATION alt_ts_conf2 (copy=english); -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf2; -- failed (name conflict) -ERROR: text search configuration "alt_ts_conf2" already exists -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf3; -- OK -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regtest_alter_user2; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user2" -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regtest_alter_user3; -- OK -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- OK -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english); -CREATE TEXT SEARCH CONFIGURATION alt_ts_conf2 (copy=english); -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 RENAME TO alt_ts_conf4; -- failed (not owner) -ERROR: must be owner of text search configuration alt_ts_conf3 -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf4; -- OK -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 OWNER TO regtest_alter_user2; -- failed (not owner) -ERROR: must be owner of text search configuration alt_ts_conf3 -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regtest_alter_user3; -- failed (no role membership) -ERROR: must be member of role "regtest_alter_user3" -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 SET SCHEMA alt_nsp2; -- failed (not owner) -ERROR: must be owner of text search configuration alt_ts_conf3 -ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- failed (name conflict) -ERROR: text search configuration alt_ts_conf2 already exists in schema "alt_nsp2" -RESET SESSION AUTHORIZATION; -SELECT nspname, cfgname, rolname - FROM pg_ts_config t, pg_namespace n, pg_authid a - WHERE t.cfgnamespace = n.oid AND t.cfgowner = a.oid - AND n.nspname in ('alt_nsp1', 'alt_nsp2') - ORDER BY nspname, cfgname; - nspname | cfgname | rolname -----------+--------------+--------------------- - alt_nsp1 | alt_ts_conf2 | regtest_alter_user2 - alt_nsp1 | alt_ts_conf3 | regtest_alter_user1 - alt_nsp1 | alt_ts_conf4 | regtest_alter_user2 - alt_nsp2 | alt_ts_conf2 | regtest_alter_user3 -(4 rows) - --- --- Text Search Template --- -CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize); -CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize); -ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp2; -- failed (name conflict) -ERROR: text search template "alt_ts_temp2" already exists -ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp3; -- OK -ALTER TEXT SEARCH TEMPLATE alt_ts_temp2 SET SCHEMA alt_nsp2; -- OK -CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize); -ALTER TEXT SEARCH TEMPLATE alt_ts_temp2 SET SCHEMA alt_nsp2; -- failed (name conflict) -ERROR: text search template alt_ts_temp2 already exists in schema "alt_nsp2" -SELECT nspname, tmplname - FROM pg_ts_template t, pg_namespace n - WHERE t.tmplnamespace = n.oid AND nspname like 'alt_nsp%' - ORDER BY nspname, tmplname; - nspname | tmplname -----------+-------------- - alt_nsp1 | alt_ts_temp2 - alt_nsp1 | alt_ts_temp3 - alt_nsp2 | alt_ts_temp2 -(3 rows) - --- --- Text Search Parser --- -CREATE TEXT SEARCH PARSER alt_ts_prs1 - (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); -CREATE TEXT SEARCH PARSER alt_ts_prs2 - (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); -ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs2; -- failed (name conflict) -ERROR: text search parser "alt_ts_prs2" already exists -ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs3; -- OK -ALTER TEXT SEARCH PARSER alt_ts_prs2 SET SCHEMA alt_nsp2; -- OK -CREATE TEXT SEARCH PARSER alt_ts_prs2 - (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); -ALTER TEXT SEARCH PARSER alt_ts_prs2 SET SCHEMA alt_nsp2; -- failed (name conflict) -ERROR: text search parser alt_ts_prs2 already exists in schema "alt_nsp2" -SELECT nspname, prsname - FROM pg_ts_parser t, pg_namespace n - WHERE t.prsnamespace = n.oid AND nspname like 'alt_nsp%' - ORDER BY nspname, prsname; - nspname | prsname -----------+------------- - alt_nsp1 | alt_ts_prs2 - alt_nsp1 | alt_ts_prs3 - alt_nsp2 | alt_ts_prs2 -(3 rows) - ---- ---- Cleanup resources ---- -DROP FOREIGN DATA WRAPPER alt_fdw2 CASCADE; -NOTICE: drop cascades to server alt_fserv2 -DROP FOREIGN DATA WRAPPER alt_fdw3 CASCADE; -NOTICE: drop cascades to server alt_fserv3 -DROP LANGUAGE alt_lang2 CASCADE; -DROP LANGUAGE alt_lang3 CASCADE; -DROP LANGUAGE alt_lang4 CASCADE; -ERROR: language "alt_lang4" does not exist -DROP SCHEMA alt_nsp1 CASCADE; -NOTICE: drop cascades to 26 other objects -DETAIL: drop cascades to function alt_func3(integer) -drop cascades to function alt_agg3(integer) -drop cascades to function alt_func4(integer) -drop cascades to function alt_func2(integer) -drop cascades to function alt_agg4(integer) -drop cascades to function alt_agg2(integer) -drop cascades to conversion alt_conv3 -drop cascades to conversion alt_conv4 -drop cascades to conversion alt_conv2 -drop cascades to operator @+@(integer,integer) -drop cascades to operator @-@(integer,integer) -drop cascades to operator family alt_opf3 for access method hash -drop cascades to operator family alt_opc1 for access method hash -drop cascades to operator family alt_opc2 for access method hash -drop cascades to operator family alt_opf4 for access method hash -drop cascades to operator family alt_opf2 for access method hash -drop cascades to text search dictionary alt_ts_dict3 -drop cascades to text search dictionary alt_ts_dict4 -drop cascades to text search dictionary alt_ts_dict2 -drop cascades to text search configuration alt_ts_conf3 -drop cascades to text search configuration alt_ts_conf4 -drop cascades to text search configuration alt_ts_conf2 -drop cascades to text search template alt_ts_temp3 -drop cascades to text search template alt_ts_temp2 -drop cascades to text search parser alt_ts_prs3 -drop cascades to text search parser alt_ts_prs2 -DROP SCHEMA alt_nsp2 CASCADE; -NOTICE: drop cascades to 9 other objects -DETAIL: drop cascades to function alt_nsp2.alt_func2(integer) -drop cascades to function alt_nsp2.alt_agg2(integer) -drop cascades to conversion alt_conv2 -drop cascades to operator alt_nsp2.@-@(integer,integer) -drop cascades to operator family alt_nsp2.alt_opf2 for access method hash -drop cascades to text search dictionary alt_ts_dict2 -drop cascades to text search configuration alt_ts_conf2 -drop cascades to text search template alt_ts_temp2 -drop cascades to text search parser alt_ts_prs2 -DROP USER regtest_alter_user1; -DROP USER regtest_alter_user2; -DROP USER regtest_alter_user3; diff --git a/src/test/regress/sql/alter_generic.sql b/src/test/regress/sql/alter_generic.sql index 0ce0368f29..6313ed9415 100644 --- a/src/test/regress/sql/alter_generic.sql +++ b/src/test/regress/sql/alter_generic.sql @@ -87,38 +87,9 @@ SELECT n.nspname, proname, prorettype::regtype, proisagg, a.rolname ORDER BY nspname, proname; -- --- Collation +-- We would test collations here, but it's not possible because the error +-- messages tend to be nonportable. -- -SET SESSION AUTHORIZATION regtest_alter_user1; -CREATE COLLATION alt_coll1 (locale = 'C'); -CREATE COLLATION alt_coll2 (locale = 'C'); - --- can't test this: the error message includes the encoding name --- ALTER COLLATION alt_coll1 RENAME TO alt_coll2; -- failed (name conflict) -ALTER COLLATION alt_coll1 RENAME TO alt_coll3; -- OK -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user2; -- failed (no role membership) -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user3; -- OK -ALTER COLLATION alt_coll2 SET SCHEMA alt_nsp2; -- OK - -SET SESSION AUTHORIZATION regtest_alter_user2; -CREATE COLLATION alt_coll1 (locale = 'C'); -CREATE COLLATION alt_coll2 (locale = 'C'); - -ALTER COLLATION alt_coll3 RENAME TO alt_coll4; -- failed (not owner) -ALTER COLLATION alt_coll1 RENAME TO alt_coll4; -- OK -ALTER COLLATION alt_coll3 OWNER TO regtest_alter_user2; -- failed (not owner) -ALTER COLLATION alt_coll2 OWNER TO regtest_alter_user3; -- failed (no role membership) -ALTER COLLATION alt_coll3 SET SCHEMA alt_nsp2; -- failed (not owner) --- can't test this: the error message includes the encoding name --- ALTER COLLATION alt_coll2 SET SCHEMA alt_nsp2; -- failed (name conflict) - -RESET SESSION AUTHORIZATION; - -SELECT n.nspname, c.collname, a.rolname - FROM pg_collation c, pg_namespace n, pg_authid a - WHERE c.collnamespace = n.oid AND c.collowner = a.oid - AND n.nspname IN ('alt_nsp1', 'alt_nsp2') - ORDER BY n.nspname, c.collname; -- -- Conversion @@ -271,6 +242,7 @@ ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf4; -- OK ALTER OPERATOR FAMILY alt_opf3 USING hash OWNER TO regtest_alter_user2; -- failed (not owner) ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regtest_alter_user3; -- failed (no role membership) ALTER OPERATOR FAMILY alt_opf3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) +-- can't test this: the error message includes the raw oid of namespace -- ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) ALTER OPERATOR CLASS alt_opc3 USING hash RENAME TO alt_opc4; -- failed (not owner) @@ -278,6 +250,7 @@ ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc4; -- OK ALTER OPERATOR CLASS alt_opc3 USING hash OWNER TO regtest_alter_user2; -- failed (not owner) ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regtest_alter_user3; -- failed (no role membership) ALTER OPERATOR CLASS alt_opc3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) +-- can't test this: the error message includes the raw oid of namespace -- ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) RESET SESSION AUTHORIZATION; -- 2.40.0