From 0172b4c9449e92a3988f669d9e7e9000454d16ce Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Thu, 12 Dec 2024 11:16:45 +0900 Subject: [PATCH] Add some regression tests for missing DDL patterns MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit The following commands gain increased coverage for some of the errors they can trigger: - ALTER TABLE .. ALTER COLUMN - CREATE DOMAIN - CREATE TYPE (LIKE) This has come up while discussing the possibility to add more information about the location of the error in such queries, and it is useful on its own as there was no coverage until now for the patterns added in this commit. Author: Jian He, Kirill Reshke Reviewed-By: Álvaro Herrera, Michael Paquier Discussion: https://postgr.es/m/CALdSSPhqfvKbDwqJaY=yEePi_aq61GmMpW88i6ZH7CMG_2Z4Cg@mail.gmail.com --- src/test/regress/expected/alter_table.out | 7 ++++++ src/test/regress/expected/domain.out | 27 +++++++++++++++++++++++ src/test/regress/expected/float8.out | 2 ++ src/test/regress/expected/identity.out | 2 ++ src/test/regress/sql/alter_table.sql | 5 +++++ src/test/regress/sql/domain.sql | 14 ++++++++++++ src/test/regress/sql/float8.sql | 1 + src/test/regress/sql/identity.sql | 1 + 8 files changed, 59 insertions(+) diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 2212c8dbb5..12852aa612 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3413,6 +3413,13 @@ ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int; ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int; ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint; +-- Some error cases. +ALTER TABLE comment_test ALTER COLUMN xmin SET DATA TYPE x; +ERROR: cannot alter system column "xmin" +ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE x; +ERROR: type "x" does not exist +ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int COLLATE "C"; +ERROR: collations are not supported by type integer -- Check that the comments are intact. SELECT col_description('comment_test'::regclass, 1) as comment; comment diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 42b6559f9c..d03ab128e4 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -15,6 +15,33 @@ NOTICE: drop cascades to type dependenttypetest -- this should fail because already gone drop domain domaindroptest cascade; ERROR: type "domaindroptest" does not exist +-- some error cases +create domain d_fail as no_such_type; +ERROR: type "no_such_type" does not exist +create domain d_fail as int constraint cc REFERENCES this_table_not_exists(i); +ERROR: foreign key constraints not possible for domains +create domain d_fail as int4 not null no inherit; +ERROR: not-null constraints for domains cannot be marked NO INHERIT +create domain d_fail as int4 not null null; +ERROR: conflicting NULL/NOT NULL constraints +create domain d_fail as int4 not null default 3 default 3; +ERROR: multiple default expressions +create domain d_fail int4 DEFAULT 3 + 'h'; +ERROR: invalid input syntax for type integer: "h" +create domain d_fail int4 collate "C"; +ERROR: collations are not supported by type integer +create domain d_fail as anyelement; +ERROR: "anyelement" is not a valid base type for a domain +create domain d_fail as int4 unique; +ERROR: unique constraints not possible for domains +create domain d_fail as int4 PRIMARY key; +ERROR: primary key constraints not possible for domains +create domain d_fail as int4 constraint cc generated by default as identity; +ERROR: specifying GENERATED not supported for domains +create domain d_fail as int4 constraint cc check (values > 1) no inherit; +ERROR: check constraints for domains cannot be marked NO INHERIT +create domain d_fail as int4 constraint cc check (values > 1) deferrable; +ERROR: specifying constraint deferrability not supported for domains -- Test domain input. -- Note: the point of checking both INSERT and COPY FROM is that INSERT -- exercises CoerceToDomain while COPY exercises domain_in. diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out index de56998f5c..4965ee5554 100644 --- a/src/test/regress/expected/float8.out +++ b/src/test/regress/expected/float8.out @@ -1024,6 +1024,8 @@ create function xfloat8out(xfloat8) returns cstring immutable strict NOTICE: argument type xfloat8 is only a shell LINE 1: create function xfloat8out(xfloat8) returns cstring immutabl... ^ +create type xfloat8 (input = xfloat8in, output = xfloat8out, like = no_such_type); +ERROR: type "no_such_type" does not exist create type xfloat8 (input = xfloat8in, output = xfloat8out, like = float8); create cast (xfloat8 as float8) without function; create cast (float8 as xfloat8) without function; diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 2a2b777c89..0398a19484 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -43,6 +43,8 @@ CREATE TABLE itest4 (a int, b text); ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; +ALTER TABLE itest4 ALTER COLUMN c ADD GENERATED ALWAYS AS IDENTITY; -- error, column c does not exist +ERROR: column "c" of relation "itest4" does not exist ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed ERROR: column "a" of relation "itest4" is an identity column diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 637e3dac38..c88f9eaab0 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2145,6 +2145,11 @@ ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int; ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint; +-- Some error cases. +ALTER TABLE comment_test ALTER COLUMN xmin SET DATA TYPE x; +ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE x; +ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int COLLATE "C"; + -- Check that the comments are intact. SELECT col_description('comment_test'::regclass, 1) as comment; SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index ee07b03174..ad14de355a 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -16,6 +16,20 @@ drop domain domaindroptest cascade; -- this should fail because already gone drop domain domaindroptest cascade; +-- some error cases +create domain d_fail as no_such_type; +create domain d_fail as int constraint cc REFERENCES this_table_not_exists(i); +create domain d_fail as int4 not null no inherit; +create domain d_fail as int4 not null null; +create domain d_fail as int4 not null default 3 default 3; +create domain d_fail int4 DEFAULT 3 + 'h'; +create domain d_fail int4 collate "C"; +create domain d_fail as anyelement; +create domain d_fail as int4 unique; +create domain d_fail as int4 PRIMARY key; +create domain d_fail as int4 constraint cc generated by default as identity; +create domain d_fail as int4 constraint cc check (values > 1) no inherit; +create domain d_fail as int4 constraint cc check (values > 1) deferrable; -- Test domain input. diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql index 98e9926c9e..81a35e0bf1 100644 --- a/src/test/regress/sql/float8.sql +++ b/src/test/regress/sql/float8.sql @@ -328,6 +328,7 @@ create function xfloat8in(cstring) returns xfloat8 immutable strict language internal as 'int8in'; create function xfloat8out(xfloat8) returns cstring immutable strict language internal as 'int8out'; +create type xfloat8 (input = xfloat8in, output = xfloat8out, like = no_such_type); create type xfloat8 (input = xfloat8in, output = xfloat8out, like = float8); create cast (xfloat8 as float8) without function; create cast (float8 as xfloat8) without function; diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index cb0e05a2f1..45992a3d89 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -19,6 +19,7 @@ SELECT pg_get_serial_sequence('itest1', 'a'); CREATE TABLE itest4 (a int, b text); ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; +ALTER TABLE itest4 ALTER COLUMN c ADD GENERATED ALWAYS AS IDENTITY; -- error, column c does not exist ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set -- 2.30.2