/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/create_schema.out
326 строк
15 KB
Tom Lane
psql: Tighten heuristics for BEGIN/END within CREATE SCHEMA.
23 июн 2026, 21:12
23 июн 2026, 21:12
049b742
Код
Авторство
О чём код?
-- -- CREATE_SCHEMA -- -- Schema creation with elements. CREATE ROLE regress_create_schema_role SUPERUSER; -- Cases where schema creation fails as objects are qualified with a schema -- that does not match with what's expected. -- This checks most object types that include schema qualifications. CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE SEQUENCE schema_not_existing.seq; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE SEQUENCE schema_not_existing.seq; ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE TABLE schema_not_existing.tab (id int); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE TABLE schema_not_existing.tab (id int); ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE VIEW schema_not_existing.view AS SELECT 1; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1; ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE INDEX ON schema_not_existing.tab (id); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE INDEX ON schema_not_existing.tab (id); ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab EXECUTE FUNCTION schema_trig.no_func(); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi... ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE FUNCTION schema_not_existing.func(int) RETURNS int AS 'SELECT $1' LANGUAGE sql; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) -- Again, with a role specification and no schema names. SET ROLE regress_create_schema_role; CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE SEQUENCE schema_not_existing.seq; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE SEQUENCE schema_not_existing.seq; ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE TABLE schema_not_existing.tab (id int); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE TABLE schema_not_existing.tab (id int); ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE VIEW schema_not_existing.view AS SELECT 1; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1; ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE INDEX ON schema_not_existing.tab (id); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE INDEX ON schema_not_existing.tab (id); ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab EXECUTE FUNCTION schema_trig.no_func(); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi... ^ -- Again, with a schema name and a role specification. CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE SEQUENCE schema_not_existing.seq; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) LINE 2: CREATE SEQUENCE schema_not_existing.seq; ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE TABLE schema_not_existing.tab (id int); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) LINE 2: CREATE TABLE schema_not_existing.tab (id int); ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE VIEW schema_not_existing.view AS SELECT 1; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1; ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE INDEX ON schema_not_existing.tab (id); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) LINE 2: CREATE INDEX ON schema_not_existing.tab (id); ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab EXECUTE FUNCTION schema_trig.no_func(); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi... ^ RESET ROLE; -- Forward references no longer work in general. CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE VIEW abcd_view AS SELECT a FROM abcd CREATE TABLE abcd (a int); ERROR: relation "abcd" does not exist LINE 2: CREATE VIEW abcd_view AS SELECT a FROM abcd ^ -- Cases where the schema creation succeeds. -- The schema created matches the role name. CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE TABLE regress_create_schema_role.tab (id int); \d regress_create_schema_role.tab Table "regress_create_schema_role.tab" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | DROP SCHEMA regress_create_schema_role CASCADE; NOTICE: drop cascades to table regress_create_schema_role.tab -- Again, with a different role specification and no schema names. SET ROLE regress_create_schema_role; CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE TABLE regress_create_schema_role.tab (id int); \d regress_create_schema_role.tab Table "regress_create_schema_role.tab" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | DROP SCHEMA regress_create_schema_role CASCADE; NOTICE: drop cascades to table tab -- Again, with a schema name and a role specification. CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE TABLE regress_schema_1.tab (id int); \d regress_schema_1.tab Table "regress_schema_1.tab" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | DROP SCHEMA regress_schema_1 CASCADE; NOTICE: drop cascades to table regress_schema_1.tab RESET ROLE; -- Test forward-referencing foreign key clauses. CREATE SCHEMA regress_schema_fk CREATE TABLE regress_schema_fk.t2 ( b int, a int REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED REFERENCES t3 DEFERRABLE INITIALLY DEFERRED, CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 NOT DEFERRABLE) CREATE TABLE regress_schema_fk.t1 (a int PRIMARY KEY) CREATE TABLE t3 (a int PRIMARY KEY) CREATE TABLE t4 ( b int, a int REFERENCES t5 NOT DEFERRABLE ENFORCED REFERENCES t6 DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED) CREATE TABLE t5 (a int, b int, PRIMARY KEY (a)) CREATE TABLE t6 (a int, b int, PRIMARY KEY (a)); \d regress_schema_fk.t2 Table "regress_schema_fk.t2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- b | integer | | | a | integer | | | Foreign-key constraints: "fk" FOREIGN KEY (a) REFERENCES regress_schema_fk.t1(a) "t2_a_fkey" FOREIGN KEY (a) REFERENCES regress_schema_fk.t1(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED "t2_a_fkey1" FOREIGN KEY (a) REFERENCES regress_schema_fk.t3(a) DEFERRABLE INITIALLY DEFERRED \d regress_schema_fk.t4 Table "regress_schema_fk.t4" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- b | integer | | | a | integer | | | Foreign-key constraints: "fk" FOREIGN KEY (a) REFERENCES regress_schema_fk.t6(a) DEFERRABLE INITIALLY DEFERRED "t4_a_fkey" FOREIGN KEY (a) REFERENCES regress_schema_fk.t5(a) "t4_a_fkey1" FOREIGN KEY (a) REFERENCES regress_schema_fk.t6(a) DEFERRABLE DROP SCHEMA regress_schema_fk CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to table regress_schema_fk.t2 drop cascades to table regress_schema_fk.t1 drop cascades to table regress_schema_fk.t3 drop cascades to table regress_schema_fk.t4 drop cascades to table regress_schema_fk.t5 drop cascades to table regress_schema_fk.t6 -- Test miscellaneous object types within CREATE SCHEMA. CREATE SCHEMA regress_schema_misc CREATE AGGREGATE cs_sum(int4) ( SFUNC = int4_sum(int8, int4), STYPE = int8, INITCOND = '0' ) CREATE COLLATION cs_builtin_c ( PROVIDER = builtin, LOCALE = "C" ) CREATE DOMAIN cs_positive AS integer CHECK (VALUE > 0) CREATE FUNCTION cs_add(int4, int4) returns int4 language sql as 'select $1 + $2' CREATE OPERATOR + (function = cs_add, leftarg = int4, rightarg = int4) CREATE PROCEDURE cs_proc(int4, int4) BEGIN ATOMIC SELECT cs_add($1,$2); END -- this checks that psql is not fooled by an irrelevant BEGIN CREATE VIEW begin AS SELECT 1 AS one CREATE TEXT SEARCH CONFIGURATION cs_ts_conf (copy=english) CREATE TEXT SEARCH DICTIONARY cs_ts_dict (template=simple) CREATE TEXT SEARCH PARSER cs_ts_prs (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype) CREATE TEXT SEARCH TEMPLATE cs_ts_temp (lexize=dsimple_lexize) CREATE TYPE regress_schema_misc.cs_enum AS ENUM ('red', 'orange') CREATE TYPE cs_composite AS (a int, b float8) CREATE TYPE cs_range AS RANGE (subtype = float8, subtype_diff = float8mi) -- demonstrate creation of a base type with its I/O functions CREATE TYPE cs_type CREATE FUNCTION cs_type_in(cstring) RETURNS cs_type LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'int4in' CREATE FUNCTION cs_type_out(cs_type) RETURNS cstring LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'int4out' CREATE TYPE cs_type ( INPUT = cs_type_in, OUTPUT = cs_type_out, LIKE = int4 ) GRANT USAGE ON TYPE cs_type TO public ; NOTICE: return type cs_type is only a shell NOTICE: argument type cs_type is only a shell LINE 33: CREATE FUNCTION cs_type_out(cs_type) ^ \df regress_schema_misc.cs_add List of functions Schema | Name | Result data type | Argument data types | Type ---------------------+--------+------------------+---------------------+------ regress_schema_misc | cs_add | integer | integer, integer | func (1 row) \df regress_schema_misc.cs_proc List of functions Schema | Name | Result data type | Argument data types | Type ---------------------+---------+------------------+------------------------+------ regress_schema_misc | cs_proc | | IN integer, IN integer | proc (1 row) \da regress_schema_misc.cs_sum List of aggregate functions Schema | Name | Result data type | Argument data types | Description ---------------------+--------+------------------+---------------------+------------- regress_schema_misc | cs_sum | bigint | integer | (1 row) \do regress_schema_misc.+ List of operators Schema | Name | Left arg type | Right arg type | Result type | Description ---------------------+------+---------------+----------------+-------------+------------- regress_schema_misc | + | integer | integer | integer | (1 row) \dO regress_schema_misc.* List of collations Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? ---------------------+--------------+----------+---------+-------+--------+-----------+---------------- regress_schema_misc | cs_builtin_c | builtin | | | C | | yes (1 row) \dT regress_schema_misc.* List of data types Schema | Name | Description ---------------------+-----------------------------------+------------- regress_schema_misc | regress_schema_misc.cs_composite | regress_schema_misc | regress_schema_misc.cs_enum | regress_schema_misc | regress_schema_misc.cs_multirange | regress_schema_misc | regress_schema_misc.cs_positive | regress_schema_misc | regress_schema_misc.cs_range | regress_schema_misc | regress_schema_misc.cs_type | (6 rows) \dF regress_schema_misc.* List of text search configurations Schema | Name | Description ---------------------+------------+------------- regress_schema_misc | cs_ts_conf | (1 row) \dFd regress_schema_misc.* List of text search dictionaries Schema | Name | Description ---------------------+------------+------------- regress_schema_misc | cs_ts_dict | (1 row) \dFp regress_schema_misc.* List of text search parsers Schema | Name | Description ---------------------+-----------+------------- regress_schema_misc | cs_ts_prs | (1 row) \dFt regress_schema_misc.* List of text search templates Schema | Name | Description ---------------------+------------+------------- regress_schema_misc | cs_ts_temp | (1 row) DROP SCHEMA regress_schema_misc CASCADE; NOTICE: drop cascades to 17 other objects DETAIL: drop cascades to function regress_schema_misc.cs_sum(integer) drop cascades to collation regress_schema_misc.cs_builtin_c drop cascades to type regress_schema_misc.cs_positive drop cascades to function regress_schema_misc.cs_add(integer,integer) drop cascades to operator regress_schema_misc.+(integer,integer) drop cascades to function regress_schema_misc.cs_proc(integer,integer) drop cascades to view regress_schema_misc.begin drop cascades to text search configuration regress_schema_misc.cs_ts_conf drop cascades to text search dictionary regress_schema_misc.cs_ts_dict drop cascades to text search parser regress_schema_misc.cs_ts_prs drop cascades to text search template regress_schema_misc.cs_ts_temp drop cascades to type regress_schema_misc.cs_enum drop cascades to type regress_schema_misc.cs_composite drop cascades to type regress_schema_misc.cs_range drop cascades to function regress_schema_misc.cs_type_out(regress_schema_misc.cs_type) drop cascades to type regress_schema_misc.cs_type drop cascades to function regress_schema_misc.cs_type_in(cstring) -- Clean up DROP ROLE regress_create_schema_role;