diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
commit | 293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch) | |
tree | fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /src/test/regress/expected/alter_generic.out | |
parent | Initial commit. (diff) | |
download | postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip |
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/test/regress/expected/alter_generic.out')
-rw-r--r-- | src/test/regress/expected/alter_generic.out | 755 |
1 files changed, 755 insertions, 0 deletions
diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out new file mode 100644 index 0000000..ae54cb2 --- /dev/null +++ b/src/test/regress/expected/alter_generic.out @@ -0,0 +1,755 @@ +-- +-- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA} +-- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX +\set regresslib :libdir '/regress' :dlsuffix +CREATE FUNCTION test_opclass_options_func(internal) + RETURNS void + AS :'regresslib', 'test_opclass_options_func' + LANGUAGE C; +-- Clean up in case a prior regression run failed +SET client_min_messages TO 'warning'; +DROP ROLE IF EXISTS regress_alter_generic_user1; +DROP ROLE IF EXISTS regress_alter_generic_user2; +DROP ROLE IF EXISTS regress_alter_generic_user3; +RESET client_min_messages; +CREATE USER regress_alter_generic_user3; +CREATE USER regress_alter_generic_user2; +CREATE USER regress_alter_generic_user1 IN ROLE regress_alter_generic_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 regress_alter_generic_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 regress_alter_generic_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 regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_generic_user3; -- OK +ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp1; -- OK, already there +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 regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_generic_user3; -- OK +ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of function alt_func3 +ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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(integer) 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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of function alt_agg3 +ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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(integer) already exists in schema "alt_nsp2" +RESET SESSION AUTHORIZATION; +SELECT n.nspname, proname, prorettype::regtype, prokind, 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 | prokind | rolname +----------+-----------+------------+---------+----------------------------- + alt_nsp1 | alt_agg2 | integer | a | regress_alter_generic_user2 + alt_nsp1 | alt_agg3 | integer | a | regress_alter_generic_user1 + alt_nsp1 | alt_agg4 | integer | a | regress_alter_generic_user2 + alt_nsp1 | alt_func2 | integer | f | regress_alter_generic_user2 + alt_nsp1 | alt_func3 | integer | f | regress_alter_generic_user1 + alt_nsp1 | alt_func4 | integer | f | regress_alter_generic_user2 + alt_nsp2 | alt_agg2 | integer | a | regress_alter_generic_user3 + alt_nsp2 | alt_func2 | integer | f | regress_alter_generic_user3 +(8 rows) + +-- +-- We would test collations here, but it's not possible because the error +-- messages tend to be nonportable. +-- +-- +-- Conversion +-- +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER CONVERSION alt_conv2 OWNER TO regress_alter_generic_user3; -- OK +ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of conversion alt_conv3 +ALTER CONVERSION alt_conv2 OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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 | regress_alter_generic_user2 + alt_nsp1 | alt_conv3 | regress_alter_generic_user1 + alt_nsp1 | alt_conv4 | regress_alter_generic_user2 + alt_nsp2 | alt_conv2 | regress_alter_generic_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 regress_alter_generic_user1; -- OK +ALTER LANGUAGE alt_lang2 OWNER TO regress_alter_generic_user2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user3; -- failed (not owner) +ERROR: must be owner of language alt_lang2 +ALTER LANGUAGE alt_lang3 OWNER TO regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER LANGUAGE alt_lang3 OWNER TO regress_alter_generic_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 | regress_alter_generic_user2 + alt_lang3 | regress_alter_generic_user3 +(2 rows) + +-- +-- Operator +-- +SET SESSION AUTHORIZATION regress_alter_generic_user1; +CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi ); +CREATE OPERATOR @+@ ( leftarg = int4, rightarg = int4, procedure = int4pl ); +ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_generic_user3; -- OK +ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_user2; +CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi ); +ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of operator @+@ +ALTER OPERATOR @-@(int4, int4) OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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 | @+@ | regress_alter_generic_user3 | integer | integer | int4pl + alt_nsp1 | @-@ | regress_alter_generic_user2 | integer | integer | int4mi + alt_nsp2 | @-@ | regress_alter_generic_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 regress_alter_generic_user1; +ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_generic_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 regress_alter_generic_user1; +ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_generic_user1; +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_generic_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 regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_generic_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 regress_alter_generic_user2; +ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_generic_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 regress_alter_generic_user2; +ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_generic_user2; +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of operator family alt_opf3 +ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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) +ERROR: operator family "alt_opf2" for access method "hash" already exists in schema "alt_nsp2" +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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of operator class alt_opc3 +ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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) +ERROR: operator class "alt_opc2" for access method "hash" already exists in schema "alt_nsp2" +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 | regress_alter_generic_user2 + alt_nsp1 | alt_opf3 | hash | regress_alter_generic_user1 + alt_nsp1 | alt_opf4 | hash | regress_alter_generic_user2 + alt_nsp2 | alt_opf2 | hash | regress_alter_generic_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 | regress_alter_generic_user2 + alt_nsp1 | alt_opc3 | hash | regress_alter_generic_user1 + alt_nsp1 | alt_opc4 | hash | regress_alter_generic_user2 + alt_nsp2 | alt_opc2 | hash | regress_alter_generic_user3 +(4 rows) + +-- ALTER OPERATOR FAMILY ... ADD/DROP +-- Should work. Textbook case of CREATE / ALTER ADD / ALTER DROP / DROP +BEGIN TRANSACTION; +CREATE OPERATOR FAMILY alt_opf4 USING btree; +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD + -- int4 vs int2 + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2); +ALTER OPERATOR FAMILY alt_opf4 USING btree DROP + -- int4 vs int2 + OPERATOR 1 (int4, int2) , + OPERATOR 2 (int4, int2) , + OPERATOR 3 (int4, int2) , + OPERATOR 4 (int4, int2) , + OPERATOR 5 (int4, int2) , + FUNCTION 1 (int4, int2) ; +DROP OPERATOR FAMILY alt_opf4 USING btree; +ROLLBACK; +-- Should fail. Invalid values for ALTER OPERATOR FAMILY .. ADD / DROP +CREATE OPERATOR FAMILY alt_opf4 USING btree; +ALTER OPERATOR FAMILY alt_opf4 USING invalid_index_method ADD OPERATOR 1 < (int4, int2); -- invalid indexing_method +ERROR: access method "invalid_index_method" does not exist +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 6 < (int4, int2); -- operator number should be between 1 and 5 +ERROR: invalid operator number 6, must be between 1 and 5 +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 0 < (int4, int2); -- operator number should be between 1 and 5 +ERROR: invalid operator number 0, must be between 1 and 5 +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types +ERROR: operator argument types must be specified in ALTER OPERATOR FAMILY +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- invalid options parsing function +ERROR: invalid function number 0, must be between 1 and 5 +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5 +ERROR: invalid function number 6, must be between 1 and 5 +ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY +ERROR: STORAGE cannot be specified in ALTER OPERATOR FAMILY +DROP OPERATOR FAMILY alt_opf4 USING btree; +-- Should fail. Need to be SUPERUSER to do ALTER OPERATOR FAMILY .. ADD / DROP +BEGIN TRANSACTION; +CREATE ROLE regress_alter_generic_user5 NOSUPERUSER; +CREATE OPERATOR FAMILY alt_opf5 USING btree; +SET ROLE regress_alter_generic_user5; +ALTER OPERATOR FAMILY alt_opf5 USING btree ADD OPERATOR 1 < (int4, int2), FUNCTION 1 btint42cmp(int4, int2); +ERROR: must be superuser to alter an operator family +RESET ROLE; +ERROR: current transaction is aborted, commands ignored until end of transaction block +DROP OPERATOR FAMILY alt_opf5 USING btree; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- Should fail. Need rights to namespace for ALTER OPERATOR FAMILY .. ADD / DROP +BEGIN TRANSACTION; +CREATE ROLE regress_alter_generic_user6; +CREATE SCHEMA alt_nsp6; +REVOKE ALL ON SCHEMA alt_nsp6 FROM regress_alter_generic_user6; +CREATE OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree; +SET ROLE regress_alter_generic_user6; +ALTER OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree ADD OPERATOR 1 < (int4, int2); +ERROR: permission denied for schema alt_nsp6 +ROLLBACK; +-- Should fail. Only two arguments required for ALTER OPERATOR FAMILY ... DROP OPERATOR +CREATE OPERATOR FAMILY alt_opf7 USING btree; +ALTER OPERATOR FAMILY alt_opf7 USING btree ADD OPERATOR 1 < (int4, int2); +ALTER OPERATOR FAMILY alt_opf7 USING btree DROP OPERATOR 1 (int4, int2, int8); +ERROR: one or two argument types must be specified +DROP OPERATOR FAMILY alt_opf7 USING btree; +-- Should work. During ALTER OPERATOR FAMILY ... DROP OPERATOR +-- when left type is the same as right type, a DROP with only one argument type should work +CREATE OPERATOR FAMILY alt_opf8 USING btree; +ALTER OPERATOR FAMILY alt_opf8 USING btree ADD OPERATOR 1 < (int4, int4); +DROP OPERATOR FAMILY alt_opf8 USING btree; +-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY +CREATE OPERATOR FAMILY alt_opf9 USING gist; +ALTER OPERATOR FAMILY alt_opf9 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops; +DROP OPERATOR FAMILY alt_opf9 USING gist; +-- Should fail. Ensure correct ordering methods in ALTER OPERATOR FAMILY ... ADD OPERATOR .. FOR ORDER BY +CREATE OPERATOR FAMILY alt_opf10 USING btree; +ALTER OPERATOR FAMILY alt_opf10 USING btree ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops; +ERROR: access method "btree" does not support ordering operators +DROP OPERATOR FAMILY alt_opf10 USING btree; +-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY +CREATE OPERATOR FAMILY alt_opf11 USING gist; +ALTER OPERATOR FAMILY alt_opf11 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops; +ALTER OPERATOR FAMILY alt_opf11 USING gist DROP OPERATOR 1 (int4, int4); +DROP OPERATOR FAMILY alt_opf11 USING gist; +-- Should fail. btree comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION +BEGIN TRANSACTION; +CREATE OPERATOR FAMILY alt_opf12 USING btree; +CREATE FUNCTION fn_opf12 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; +ALTER OPERATOR FAMILY alt_opf12 USING btree ADD FUNCTION 1 fn_opf12(int4, int2); +ERROR: btree comparison functions must return integer +DROP OPERATOR FAMILY alt_opf12 USING btree; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- Should fail. hash comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION +BEGIN TRANSACTION; +CREATE OPERATOR FAMILY alt_opf13 USING hash; +CREATE FUNCTION fn_opf13 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; +ALTER OPERATOR FAMILY alt_opf13 USING hash ADD FUNCTION 1 fn_opf13(int4); +ERROR: hash function 1 must return integer +DROP OPERATOR FAMILY alt_opf13 USING hash; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- Should fail. btree comparison functions should have two arguments in ALTER OPERATOR FAMILY ... ADD FUNCTION +BEGIN TRANSACTION; +CREATE OPERATOR FAMILY alt_opf14 USING btree; +CREATE FUNCTION fn_opf14 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; +ALTER OPERATOR FAMILY alt_opf14 USING btree ADD FUNCTION 1 fn_opf14(int4); +ERROR: btree comparison functions must have two arguments +DROP OPERATOR FAMILY alt_opf14 USING btree; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- Should fail. hash comparison functions should have one argument in ALTER OPERATOR FAMILY ... ADD FUNCTION +BEGIN TRANSACTION; +CREATE OPERATOR FAMILY alt_opf15 USING hash; +CREATE FUNCTION fn_opf15 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; +ALTER OPERATOR FAMILY alt_opf15 USING hash ADD FUNCTION 1 fn_opf15(int4, int2); +ERROR: hash function 1 must have one argument +DROP OPERATOR FAMILY alt_opf15 USING hash; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- Should fail. In gist throw an error when giving different data types for function argument +-- without defining left / right type in ALTER OPERATOR FAMILY ... ADD FUNCTION +CREATE OPERATOR FAMILY alt_opf16 USING gist; +ALTER OPERATOR FAMILY alt_opf16 USING gist ADD FUNCTION 1 btint42cmp(int4, int2); +ERROR: associated data types must be specified for index support function +DROP OPERATOR FAMILY alt_opf16 USING gist; +-- Should fail. duplicate operator number / function number in ALTER OPERATOR FAMILY ... ADD FUNCTION +CREATE OPERATOR FAMILY alt_opf17 USING btree; +ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4), OPERATOR 1 < (int4, int4); -- operator # appears twice in same statement +ERROR: operator number 1 for (integer,integer) appears more than once +ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested first-time +ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested again in separate statement +ERROR: operator 1(integer,integer) already exists in operator family "alt_opf17" +ALTER OPERATOR FAMILY alt_opf17 USING btree ADD + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2) , + FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears twice in same statement +ERROR: function number 1 for (integer,smallint) appears more than once +ALTER OPERATOR FAMILY alt_opf17 USING btree ADD + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears first time +ALTER OPERATOR FAMILY alt_opf17 USING btree ADD + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 requested again in separate statement +ERROR: operator 1(integer,smallint) already exists in operator family "alt_opf17" +DROP OPERATOR FAMILY alt_opf17 USING btree; +-- Should fail. Ensure that DROP requests for missing OPERATOR / FUNCTIONS +-- return appropriate message in ALTER OPERATOR FAMILY ... DROP OPERATOR / FUNCTION +CREATE OPERATOR FAMILY alt_opf18 USING btree; +ALTER OPERATOR FAMILY alt_opf18 USING btree DROP OPERATOR 1 (int4, int4); +ERROR: operator 1(integer,integer) does not exist in operator family "alt_opf18" +ALTER OPERATOR FAMILY alt_opf18 USING btree ADD + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2); +-- Should fail. Not allowed to have cross-type equalimage function. +ALTER OPERATOR FAMILY alt_opf18 USING btree + ADD FUNCTION 4 (int4, int2) btequalimage(oid); +ERROR: btree equal image functions must not be cross-type +ALTER OPERATOR FAMILY alt_opf18 USING btree DROP FUNCTION 2 (int4, int4); +ERROR: function 2(integer,integer) does not exist in operator family "alt_opf18" +DROP OPERATOR FAMILY alt_opf18 USING btree; +-- Should fail. Invalid opclass options function (#5) specifications. +CREATE OPERATOR FAMILY alt_opf19 USING btree; +ALTER OPERATOR FAMILY alt_opf19 USING btree ADD FUNCTION 5 test_opclass_options_func(internal, text[], bool); +ERROR: function test_opclass_options_func(internal, text[], boolean) does not exist +ALTER OPERATOR FAMILY alt_opf19 USING btree ADD FUNCTION 5 (int4) btint42cmp(int4, int2); +ERROR: invalid operator class options parsing function +HINT: Valid signature of operator class options parsing function is (internal) RETURNS void. +ALTER OPERATOR FAMILY alt_opf19 USING btree ADD FUNCTION 5 (int4, int2) btint42cmp(int4, int2); +ERROR: left and right associated data types for operator class options parsing functions must match +ALTER OPERATOR FAMILY alt_opf19 USING btree ADD FUNCTION 5 (int4) test_opclass_options_func(internal); -- Ok +ALTER OPERATOR FAMILY alt_opf19 USING btree DROP FUNCTION 5 (int4, int4); +DROP OPERATOR FAMILY alt_opf19 USING btree; +-- +-- Statistics +-- +SET SESSION AUTHORIZATION regress_alter_generic_user1; +CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER); +CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1; +CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1; +ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict) +ERROR: statistics object "alt_stat2" already exists in schema "alt_nsp1" +ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- OK +ALTER STATISTICS alt_stat2 OWNER TO regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER STATISTICS alt_stat2 OWNER TO regress_alter_generic_user3; -- OK +ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_user2; +CREATE TABLE alt_regress_2 (a INTEGER, b INTEGER); +CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_2; +CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_2; +ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner) +ERROR: must be owner of statistics object alt_stat3 +ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK +ALTER STATISTICS alt_stat3 OWNER TO regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of statistics object alt_stat3 +ALTER STATISTICS alt_stat2 OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user3" +ALTER STATISTICS alt_stat3 SET SCHEMA alt_nsp2; -- failed (not owner) +ERROR: must be owner of statistics object alt_stat3 +ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- failed (name conflict) +ERROR: statistics object "alt_stat2" already exists in schema "alt_nsp2" +RESET SESSION AUTHORIZATION; +SELECT nspname, stxname, rolname + FROM pg_statistic_ext s, pg_namespace n, pg_authid a + WHERE s.stxnamespace = n.oid AND s.stxowner = a.oid + AND n.nspname in ('alt_nsp1', 'alt_nsp2') + ORDER BY nspname, stxname; + nspname | stxname | rolname +----------+-----------+----------------------------- + alt_nsp1 | alt_stat2 | regress_alter_generic_user2 + alt_nsp1 | alt_stat3 | regress_alter_generic_user1 + alt_nsp1 | alt_stat4 | regress_alter_generic_user2 + alt_nsp2 | alt_stat2 | regress_alter_generic_user3 +(4 rows) + +-- +-- Text Search Dictionary +-- +SET SESSION AUTHORIZATION regress_alter_generic_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 in schema "alt_nsp1" +ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict3; -- OK +ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_generic_user3; -- OK +ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of text search dictionary alt_ts_dict3 +ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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 | regress_alter_generic_user2 + alt_nsp1 | alt_ts_dict3 | regress_alter_generic_user1 + alt_nsp1 | alt_ts_dict4 | regress_alter_generic_user2 + alt_nsp2 | alt_ts_dict2 | regress_alter_generic_user3 +(4 rows) + +-- +-- Text Search Configuration +-- +SET SESSION AUTHORIZATION regress_alter_generic_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 in schema "alt_nsp1" +ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf3; -- OK +ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_generic_user2; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_user2" +ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_generic_user3; -- OK +ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- OK +SET SESSION AUTHORIZATION regress_alter_generic_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 regress_alter_generic_user2; -- failed (not owner) +ERROR: must be owner of text search configuration alt_ts_conf3 +ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_generic_user3; -- failed (no role membership) +ERROR: must be able to SET ROLE "regress_alter_generic_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 | regress_alter_generic_user2 + alt_nsp1 | alt_ts_conf3 | regress_alter_generic_user1 + alt_nsp1 | alt_ts_conf4 | regress_alter_generic_user2 + alt_nsp2 | alt_ts_conf2 | regress_alter_generic_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 in schema "alt_nsp1" +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" +-- invalid: non-lowercase quoted identifiers +CREATE TEXT SEARCH TEMPLATE tstemp_case ("Init" = init_function); +ERROR: text search template parameter "Init" not recognized +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 in schema "alt_nsp1" +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" +-- invalid: non-lowercase quoted identifiers +CREATE TEXT SEARCH PARSER tspars_case ("Start" = start_function); +ERROR: text search parser parameter "Start" not recognized +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 SCHEMA alt_nsp1 CASCADE; +NOTICE: drop cascades to 28 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 table alt_regress_1 +drop cascades to table alt_regress_2 +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_nsp2.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_nsp2.alt_ts_dict2 +drop cascades to text search configuration alt_nsp2.alt_ts_conf2 +drop cascades to text search template alt_nsp2.alt_ts_temp2 +drop cascades to text search parser alt_nsp2.alt_ts_prs2 +DROP USER regress_alter_generic_user1; +DROP USER regress_alter_generic_user2; +DROP USER regress_alter_generic_user3; |