Hello:
I’m working on developing a testing harness for the DDL Deparser being
worked on in [1], please apply the patches in [1] before apply this
patch. I think the testing harness needs to achieve the following
goals:
1. The deparsed JSON output is as expected.
2. The SQL commands re-formed from deparsed JSON should make the same
schema change as the original SQL commands.
3. Any DDL change without modifying the deparser should fail the
testing harness.
Based on these 3 goals, we think the deparser testing harness should
have 2 parts: the first part is unit testing to cover the first two
goals and the second part is integrating deparser test with pg_regress
to cover the third goal.
I think the unit test part can be based on
https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=src/test/modules/test_ddl_deparse;hb=HEAD.
We can improve upon this test by using it to validate the output JSON
and the re-formed SQL from the deparsed JSON.
[2] and [3] proposed using regression tests to test deparser and
provided an implementation using TAP framework. I made some changes
which enables testing any SQL file under the folder provided in
$inputdir variable. This implementation enables us to run test cases
under regression tests folder, or just any test cases using a SQL
file.
I came up with some ideas during the investigation and want to collect
some feedback:
1, Currently we want to utilize the test cases from regression tests.
However you will find that many test cases end with DROP commands. In
current deparser testing approach proposed in [2] and [3], we compare
the pg_dump schema results between the original SQL scripts and
deparser generated commands. Because most test cases end with DROP
command, the schema will not be shown in pg_dump, so the test coverage
is vastly reduced. Any suggestion to this problem?
2, We found that DROP command are not returned by
pg_event_trigger_ddl_commands() fucntion in ddl_command_end trigger,
but it’s caught by ddl_command_end trigger. Currently, we catch DROP
command in sql_drop trigger. It’s unclear why
pg_event_trigger_ddl_commands() function is designed to not return
DROP command.
3, For unsupported DDL commands by the deparser, the current
implementation just skips them silently. So we cannot detect
unsupported DDL commands easily. Customers may also want the deparser
related features like logical replication to be executed in a strict
mode, so that the system can warn them when deparser can not deparse
some DDL command. So I propose to introduce a new GUC such as
“StopOnDeparserUnsupportedCommand = true/false” to allow the deparser
to execute in strict mode, in which an unsupported DDL command will
raise an error.
4, We found that the event trigger function
pg_event_trigger_ddl_commands() only returns subcommands, and deparser
is deparsing subcommands returned by this function. The deparser works
on subcommand level by using this function, but the deparser is
designed to deparse the complete command to JSON output. So there is a
mismatch here, what do you think about this problem? Should the
deparser work at subcommand level? Or should we provide some event
trigger function which can return the complete command instead of
subcommands?
Your feedback is appreciated.
Regards,
Runqi Tian
Amazon RDS/Aurora for PostgreSQL
[1]
https://www.postgresql.org/message-id/CALDaNm0VnaCg__huSDW%3Dn%3D_rSGGES90cpOtqwZeWnA6muoz3oA%40mail.gmail.com
[2]
https://www.postgresql.org/message-id/CAD21AoBVCoPPRKvU_5-%3DwEXsa92GsNJFJOcYyXzvoSEJCx5dKw%40mail.gmail.com
[3] https://www.postgresql.org/message-id/20150215044814.gl3...@alvh.no-ip.org
diff --git a/src/backend/commands/ddl_deparse.c b/src/backend/commands/ddl_deparse.c
index 5bfa800fc2..8cb946f7f1 100755
--- a/src/backend/commands/ddl_deparse.c
+++ b/src/backend/commands/ddl_deparse.c
@@ -6508,6 +6508,42 @@ deparse_DefElem(DefElem *elem, bool is_reset)
return set;
}
+/*
+ * Given object_identity and object_type of dropped object, return a JSON representation of DROP command.
+ */
+Datum
+deparse_drop_ddl(PG_FUNCTION_ARGS)
+{
+ text *objidentity = PG_GETARG_TEXT_P(0);
+ const char *objidentity_str = text_to_cstring(objidentity);
+ text *objecttype = PG_GETARG_TEXT_P(1);
+ const char *objecttype_str = text_to_cstring(objecttype);
+
+ char *command;
+
+ // constraint is part of alter table command, no need to drop in DROP command
+ if (strcmp(objecttype_str, "table constraint") == 0) {
+ PG_RETURN_NULL();
+ } else if (strcmp(objecttype_str, "toast table") == 0) {
+ objecttype_str = "table";
+ } else if (strcmp(objecttype_str, "default value") == 0) {
+ PG_RETURN_NULL();
+ } else if (strcmp(objecttype_str, "operator of access method") == 0) {
+ PG_RETURN_NULL();
+ } else if (strcmp(objecttype_str, "function of access method") == 0) {
+ PG_RETURN_NULL();
+ } else if (strcmp(objecttype_str, "table column") == 0) {
+ PG_RETURN_NULL();
+ }
+
+ command = deparse_drop_command(objidentity_str, objecttype_str, DROP_CASCADE);
+
+ if (command)
+ PG_RETURN_TEXT_P(cstring_to_text(command));
+
+ PG_RETURN_NULL();
+}
+
/*
* Handle deparsing of DROP commands.
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 81fbfd4aac..b097acfdba 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11841,4 +11841,7 @@
{ oid => '4647', descr => 'trigger for ddl command deparse table init',
proname => 'publication_deparse_table_init_write', prorettype => 'event_trigger',
proargtypes => '', prosrc => 'publication_deparse_table_init_write' },
+{ oid => '4648', descr => 'deparse the DROP DDL command into json format string',
+ proname => 'deparse_drop_ddl', prorettype => 'text',
+ proargtypes => 'text text', prosrc => 'deparse_drop_ddl' },
]
diff --git a/src/test/modules/test_ddl_deparse_regress/Makefile b/src/test/modules/test_ddl_deparse_regress/Makefile
new file mode 100644
index 0000000000..d12b552b09
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/Makefile
@@ -0,0 +1,30 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/test/modules/test_ddl_deparse_regress
+#
+# Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/test/modules/test_ddl_deparse_regress/Makefile
+#
+#-------------------------------------------------------------------------
+
+subdir = src/test/modules/test_deparser
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+
+EXTRA_INSTALL = contrib/hstore
+
+export with_icu
+
+REGRESS_SHLIB=$(abs_top_builddir)/src/test/regress/regress$(DLSUFFIX)
+export REGRESS_SHLIB
+
+check:
+ $(prove_check)
+
+installcheck:
+ $(prove_installcheck)
+
+clean distclean maintainer-clean:
+ rm -rf tmp_check
diff --git a/src/test/modules/test_ddl_deparse_regress/expected/create_table.out b/src/test/modules/test_ddl_deparse_regress/expected/create_table.out
new file mode 100644
index 0000000000..039205616b
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/expected/create_table.out
@@ -0,0 +1,1116 @@
+--
+-- CREATE_TABLE
+--
+-- Error cases
+CREATE TABLE unknowntab (
+ u unknown -- fail
+);
+ERROR: column "u" has pseudo-type unknown
+CREATE TYPE unknown_comptype AS (
+ u unknown -- fail
+);
+ERROR: column "u" has pseudo-type unknown
+-- invalid: non-lowercase quoted reloptions identifiers
+CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a;
+ERROR: unrecognized parameter "Fillfactor"
+CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK
+CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
+ relname | relkind | relpersistence
+----------------+---------+----------------
+ unlogged1 | r | u
+ unlogged1_pkey | i | u
+ unlogged2 | r | t
+ unlogged2_pkey | i | t
+(4 rows)
+
+REINDEX INDEX unlogged1_pkey;
+REINDEX INDEX unlogged2_pkey;
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
+ relname | relkind | relpersistence
+----------------+---------+----------------
+ unlogged1 | r | u
+ unlogged1_pkey | i | u
+ unlogged2 | r | t
+ unlogged2_pkey | i | t
+(4 rows)
+
+DROP TABLE unlogged2;
+INSERT INTO unlogged1 VALUES (42);
+CREATE UNLOGGED TABLE public.unlogged2 (a int primary key); -- also OK
+CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); -- not OK
+ERROR: only temporary relations may be created in temporary schemas
+LINE 1: CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key);
+ ^
+CREATE TABLE pg_temp.implicitly_temp (a int primary key); -- OK
+CREATE TEMP TABLE explicitly_temp (a int primary key); -- also OK
+CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key); -- also OK
+CREATE TEMP TABLE public.temp_to_perm (a int primary key); -- not OK
+ERROR: cannot create temporary relation in non-temporary schema
+LINE 1: CREATE TEMP TABLE public.temp_to_perm (a int primary key);
+ ^
+DROP TABLE unlogged1, public.unlogged2;
+CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
+CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
+ERROR: relation "as_select1" already exists
+CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
+NOTICE: relation "as_select1" already exists, skipping
+DROP TABLE as_select1;
+PREPARE select1 AS SELECT 1 as a;
+CREATE TABLE as_select1 AS EXECUTE select1;
+CREATE TABLE as_select1 AS EXECUTE select1;
+ERROR: relation "as_select1" already exists
+SELECT * FROM as_select1;
+ a
+---
+ 1
+(1 row)
+
+CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1;
+NOTICE: relation "as_select1" already exists, skipping
+DROP TABLE as_select1;
+DEALLOCATE select1;
+-- create an extra wide table to test for issues related to that
+-- (temporarily hide query, to avoid the long CREATE TABLE stmt)
+\set ECHO none
+INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col');
+SELECT firstc, lastc FROM extra_wide_table;
+ firstc | lastc
+-----------+----------
+ first col | last col
+(1 row)
+
+-- check that tables with oids cannot be created anymore
+CREATE TABLE withoid() WITH OIDS;
+ERROR: syntax error at or near "OIDS"
+LINE 1: CREATE TABLE withoid() WITH OIDS;
+ ^
+CREATE TABLE withoid() WITH (oids);
+ERROR: tables declared WITH OIDS are not supported
+CREATE TABLE withoid() WITH (oids = true);
+ERROR: tables declared WITH OIDS are not supported
+-- but explicitly not adding oids is still supported
+-- CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
+-- CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
+-- check restriction with default expressions
+-- invalid use of column reference in default expressions
+CREATE TABLE default_expr_column (id int DEFAULT (id));
+ERROR: cannot use column reference in DEFAULT expression
+LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (id));
+ ^
+CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
+ERROR: cannot use column reference in DEFAULT expression
+LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
+ ^
+CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
+ERROR: cannot use column reference in DEFAULT expression
+LINE 1: ...TE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
+ ^
+-- invalid column definition
+CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
+ERROR: cannot use column reference in DEFAULT expression
+LINE 1: ...TABLE default_expr_non_column (a int DEFAULT (avg(non_existe...
+ ^
+-- invalid use of aggregate
+CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
+ERROR: aggregate functions are not allowed in DEFAULT expressions
+LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
+ ^
+-- invalid use of subquery
+CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
+ERROR: cannot use subquery in DEFAULT expression
+LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
+ ^
+-- invalid use of set-returning function
+CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
+ERROR: set-returning functions are not allowed in DEFAULT expressions
+LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (generate_serie...
+ ^
+-- Verify that subtransaction rollback restores rd_createSubid.
+BEGIN;
+CREATE TABLE remember_create_subid (c int);
+SAVEPOINT q; DROP TABLE remember_create_subid; ROLLBACK TO q;
+COMMIT;
+DROP TABLE remember_create_subid;
+-- Verify that subtransaction rollback restores rd_firstRelfilenodeSubid.
+CREATE TABLE remember_node_subid (c int);
+BEGIN;
+ALTER TABLE remember_node_subid ALTER c TYPE bigint;
+SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q;
+COMMIT;
+DROP TABLE remember_node_subid;
+--
+-- Partitioned tables
+--
+-- cannot combine INHERITS and PARTITION BY (although grammar allows)
+CREATE TABLE partitioned (
+ a int
+) INHERITS (some_table) PARTITION BY LIST (a);
+ERROR: cannot create partitioned table as inheritance child
+-- cannot use more than 1 column as partition key for list partitioned table
+CREATE TABLE partitioned (
+ a1 int,
+ a2 int
+) PARTITION BY LIST (a1, a2); -- fail
+ERROR: cannot use "list" partition strategy with more than one column
+-- unsupported constraint type for partitioned tables
+CREATE TABLE partitioned (
+ a int,
+ EXCLUDE USING gist (a WITH &&)
+) PARTITION BY RANGE (a);
+ERROR: exclusion constraints are not supported on partitioned tables
+LINE 3: EXCLUDE USING gist (a WITH &&)
+ ^
+-- prevent using prohibited expressions in the key
+CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (retset(a));
+ERROR: set-returning functions are not allowed in partition key expressions
+DROP FUNCTION retset(int);
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE ((avg(a)));
+ERROR: aggregate functions are not allowed in partition key expressions
+CREATE TABLE partitioned (
+ a int,
+ b int
+) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b)));
+ERROR: window functions are not allowed in partition key expressions
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY LIST ((a LIKE (SELECT 1)));
+ERROR: cannot use subquery in partition key expression
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE ((42));
+ERROR: cannot use constant expression as partition key
+CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (const_func());
+ERROR: cannot use constant expression as partition key
+DROP FUNCTION const_func();
+-- only accept valid partitioning strategy
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY MAGIC (a);
+ERROR: unrecognized partitioning strategy "magic"
+-- specified column must be present in the table
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (b);
+ERROR: column "b" named in partition key does not exist
+LINE 3: ) PARTITION BY RANGE (b);
+ ^
+-- cannot use system columns in partition key
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (xmin);
+ERROR: cannot use system column "xmin" in partition key
+LINE 3: ) PARTITION BY RANGE (xmin);
+ ^
+-- cannot use pseudotypes
+CREATE TABLE partitioned (
+ a int,
+ b int
+) PARTITION BY RANGE (((a, b)));
+ERROR: partition key column 1 has pseudo-type record
+CREATE TABLE partitioned (
+ a int,
+ b int
+) PARTITION BY RANGE (a, ('unknown'));
+ERROR: partition key column 2 has pseudo-type unknown
+-- functions in key must be immutable
+CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL;
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (immut_func(a));
+ERROR: functions in partition key expression must be marked IMMUTABLE
+DROP FUNCTION immut_func(int);
+-- prevent using columns of unsupported types in key (type must have a btree operator class)
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY LIST (a);
+ERROR: data type point has no default operator class for access method "btree"
+HINT: You must specify a btree operator class or define a default btree operator class for the data type.
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY LIST (a point_ops);
+ERROR: operator class "point_ops" does not exist for access method "btree"
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY RANGE (a);
+ERROR: data type point has no default operator class for access method "btree"
+HINT: You must specify a btree operator class or define a default btree operator class for the data type.
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY RANGE (a point_ops);
+ERROR: operator class "point_ops" does not exist for access method "btree"
+-- cannot add NO INHERIT constraints to partitioned tables
+CREATE TABLE partitioned (
+ a int,
+ CONSTRAINT check_a CHECK (a > 0) NO INHERIT
+) PARTITION BY RANGE (a);
+ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned"
+-- some checks after successful creation of a partitioned table
+CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL;
+CREATE TABLE partitioned (
+ a int,
+ b int,
+ c text,
+ d text
+) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C");
+-- check relkind
+SELECT relkind FROM pg_class WHERE relname = 'partitioned';
+ relkind
+---------
+ p
+(1 row)
+
+-- prevent a function referenced in partition key from being dropped
+DROP FUNCTION plusone(int);
+ERROR: cannot drop function plusone(integer) because other objects depend on it
+DETAIL: table partitioned depends on function plusone(integer)
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- partitioned table cannot participate in regular inheritance
+CREATE TABLE partitioned2 (
+ a int,
+ b text
+) PARTITION BY RANGE ((a+1), substr(b, 1, 5));
+CREATE TABLE fail () INHERITS (partitioned2);
+ERROR: cannot inherit from partitioned table "partitioned2"
+-- Partition key in describe output
+\d partitioned
+ Partitioned table "public.partitioned"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+ d | text | | |
+Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")
+Number of partitions: 0
+
+\d+ partitioned2
+ Partitioned table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | integer | | | | plain | |
+ b | text | | | | extended | |
+Partition key: RANGE (((a + 1)), substr(b, 1, 5))
+Number of partitions: 0
+
+INSERT INTO partitioned2 VALUES (1, 'hello');
+ERROR: no partition of relation "partitioned2" found for row
+DETAIL: Partition key of the failing row contains ((a + 1), substr(b, 1, 5)) = (2, hello).
+CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc');
+\d+ part2_1
+ Table "public.part2_1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | integer | | | | plain | |
+ b | text | | | | extended | |
+Partition of: partitioned2 FOR VALUES FROM ('-1', 'aaaaa') TO (100, 'ccccc')
+Partition constraint: (((a + 1) IS NOT NULL) AND (substr(b, 1, 5) IS NOT NULL) AND (((a + 1) > '-1'::integer) OR (((a + 1) = '-1'::integer) AND (substr(b, 1, 5) >= 'aaaaa'::text))) AND (((a + 1) < 100) OR (((a + 1) = 100) AND (substr(b, 1, 5) < 'ccccc'::text))))
+
+DROP TABLE partitioned, partitioned2;
+-- check reference to partitioned table's rowtype in partition descriptor
+create table partitioned (a int, b int)
+ partition by list ((row(a, b)::partitioned));
+create table partitioned1
+ partition of partitioned for values in ('(1,2)'::partitioned);
+create table partitioned2
+ partition of partitioned for values in ('(2,4)'::partitioned);
+explain (costs off)
+select * from partitioned where row(a,b)::partitioned = '(1,2)'::partitioned;
+ QUERY PLAN
+-----------------------------------------------------------
+ Seq Scan on partitioned1 partitioned
+ Filter: (ROW(a, b)::partitioned = '(1,2)'::partitioned)
+(2 rows)
+
+drop table partitioned;
+-- whole-row Var in partition key works too
+create table partitioned (a int, b int)
+ partition by list ((partitioned));
+create table partitioned1
+ partition of partitioned for values in ('(1,2)');
+create table partitioned2
+ partition of partitioned for values in ('(2,4)');
+explain (costs off)
+select * from partitioned where partitioned = '(1,2)'::partitioned;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on partitioned1 partitioned
+ Filter: ((partitioned.*)::partitioned = '(1,2)'::partitioned)
+(2 rows)
+
+\d+ partitioned1
+ Table "public.partitioned1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+Partition of: partitioned FOR VALUES IN ('(1,2)')
+Partition constraint: (((partitioned1.*)::partitioned IS DISTINCT FROM NULL) AND ((partitioned1.*)::partitioned = '(1,2)'::partitioned))
+
+drop table partitioned;
+-- check that dependencies of partition columns are handled correctly
+create domain intdom1 as int;
+create table partitioned (
+ a intdom1,
+ b text
+) partition by range (a);
+alter table partitioned drop column a; -- fail
+ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
+drop domain intdom1; -- fail, requires cascade
+ERROR: cannot drop type intdom1 because other objects depend on it
+DETAIL: table partitioned depends on type intdom1
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+drop domain intdom1 cascade;
+NOTICE: drop cascades to table partitioned
+table partitioned; -- gone
+ERROR: relation "partitioned" does not exist
+LINE 1: table partitioned;
+ ^
+-- likewise for columns used in partition expressions
+create domain intdom1 as int;
+create table partitioned (
+ a intdom1,
+ b text
+) partition by range (plusone(a));
+alter table partitioned drop column a; -- fail
+ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
+drop domain intdom1; -- fail, requires cascade
+ERROR: cannot drop type intdom1 because other objects depend on it
+DETAIL: table partitioned depends on type intdom1
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+drop domain intdom1 cascade;
+NOTICE: drop cascades to table partitioned
+table partitioned; -- gone
+ERROR: relation "partitioned" does not exist
+LINE 1: table partitioned;
+ ^
+--
+-- Partitions
+--
+-- check partition bound syntax
+CREATE TABLE list_parted (
+ a int
+) PARTITION BY LIST (a);
+CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1');
+CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2);
+CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1));
+CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
+\d+ list_parted
+ Partitioned table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: LIST (a)
+Partitions: part_null FOR VALUES IN (NULL),
+ part_p1 FOR VALUES IN (1),
+ part_p2 FOR VALUES IN (2),
+ part_p3 FOR VALUES IN (3)
+
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
+ERROR: cannot use column reference in partition bound expression
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename);
+ERROR: cannot use column reference in partition bound expression
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename.s...
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
+ERROR: cannot use column reference in partition bound expression
+LINE 1: ..._bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
+ERROR: cannot use column reference in partition bound expression
+LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename));
+ERROR: cannot use column reference in partition bound expression
+LINE 1: ..._fail PARTITION OF list_parted FOR VALUES IN (sum(somename))...
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
+ERROR: aggregate functions are not allowed in partition bound
+LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1));
+ERROR: cannot use subquery in partition bound
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1)...
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (generate_series(4, 6));
+ERROR: set-returning functions are not allowed in partition bound
+LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (generate_s...
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "POSIX");
+ERROR: collations are not supported by type integer
+LINE 1: ...ail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "P...
+ ^
+-- syntax does not allow empty list of values for list partitions
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
+ERROR: syntax error at or near ")"
+LINE 1: ...E TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
+ ^
+-- trying to specify range for list partitioned table
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
+ERROR: invalid bound specification for a list partition
+LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) T...
+ ^
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+ERROR: invalid bound specification for a list partition
+LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODU...
+ ^
+-- check default partition cannot be created more than once
+CREATE TABLE part_default PARTITION OF list_parted DEFAULT;
+CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+ERROR: partition "fail_default_part" conflicts with existing default partition "part_default"
+LINE 1: ...TE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+ ^
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE bools (
+ a bool
+) PARTITION BY LIST (a);
+CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
+ERROR: specified value cannot be cast to type boolean for column "a"
+LINE 1: ...REATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
+ ^
+DROP TABLE bools;
+-- specified literal can be cast, and the cast might not be immutable
+CREATE TABLE moneyp (
+ a money
+) PARTITION BY LIST (a);
+CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
+CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11');
+CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int);
+DROP TABLE moneyp;
+-- cast is immutable
+CREATE TABLE bigintp (
+ a bigint
+) PARTITION BY LIST (a);
+CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10);
+-- fails due to overlap:
+CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10');
+ERROR: partition "bigintp_10_2" would overlap partition "bigintp_10"
+LINE 1: ...ABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10');
+ ^
+DROP TABLE bigintp;
+CREATE TABLE range_parted (
+ a date
+) PARTITION BY RANGE (a);
+-- forbidden expressions for partition bounds with range partitioned table
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (somename) TO ('2019-01-01');
+ERROR: cannot use column reference in partition bound expression
+LINE 2: FOR VALUES FROM (somename) TO ('2019-01-01');
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (somename.somename) TO ('2019-01-01');
+ERROR: cannot use column reference in partition bound expression
+LINE 2: FOR VALUES FROM (somename.somename) TO ('2019-01-01');
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (a) TO ('2019-01-01');
+ERROR: cannot use column reference in partition bound expression
+LINE 2: FOR VALUES FROM (a) TO ('2019-01-01');
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (max(a)) TO ('2019-01-01');
+ERROR: cannot use column reference in partition bound expression
+LINE 2: FOR VALUES FROM (max(a)) TO ('2019-01-01');
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (max(somename)) TO ('2019-01-01');
+ERROR: cannot use column reference in partition bound expression
+LINE 2: FOR VALUES FROM (max(somename)) TO ('2019-01-01');
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01');
+ERROR: aggregate functions are not allowed in partition bound
+LINE 2: FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01'...
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM ((select 1)) TO ('2019-01-01');
+ERROR: cannot use subquery in partition bound
+LINE 2: FOR VALUES FROM ((select 1)) TO ('2019-01-01');
+ ^
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01');
+ERROR: set-returning functions are not allowed in partition bound
+LINE 2: FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01');
+ ^
+-- trying to specify list for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
+ERROR: invalid bound specification for a range partition
+LINE 1: ...BLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
+ ^
+-- trying to specify modulus and remainder for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+ERROR: invalid bound specification for a range partition
+LINE 1: ...LE fail_part PARTITION OF range_parted FOR VALUES WITH (MODU...
+ ^
+-- each of start and end bounds must have same number of values as the
+-- length of the partition key
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
+ERROR: FROM must specify exactly one value per partitioning column
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
+ERROR: TO must specify exactly one value per partitioning column
+-- cannot specify null values in range bounds
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue);
+ERROR: cannot specify NULL in range bound
+-- trying to specify modulus and remainder for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+ERROR: invalid bound specification for a range partition
+LINE 1: ...LE fail_part PARTITION OF range_parted FOR VALUES WITH (MODU...
+ ^
+-- check partition bound syntax for the hash partition
+CREATE TABLE hash_parted (
+ a int
+) PARTITION BY HASH (a);
+CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0);
+CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1);
+CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2);
+CREATE TABLE hpart_4 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 3);
+-- modulus 25 is factor of modulus of 50 but 10 is not a factor of 25.
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 25, REMAINDER 3);
+ERROR: every hash partition modulus must be a factor of the next larger modulus
+DETAIL: The new modulus 25 is not divisible by 10, the modulus of existing partition "hpart_4".
+-- previous modulus 50 is factor of 150 but this modulus is not a factor of next modulus 200.
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3);
+ERROR: every hash partition modulus must be a factor of the next larger modulus
+DETAIL: The new modulus 150 is not a factor of 200, the modulus of existing partition "hpart_3".
+-- overlapping remainders
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 100, REMAINDER 3);
+ERROR: partition "fail_part" would overlap partition "hpart_4"
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODU...
+ ^
+-- trying to specify range for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+ERROR: invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a',...
+ ^
+-- trying to specify list value for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ERROR: invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ ^
+-- trying to create default partition for the hash partitioned table
+CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT;
+ERROR: a hash-partitioned table may not have a default partition
+-- check if compatible with the specified parent
+-- cannot create as partition of a non-partitioned table
+CREATE TABLE unparted (
+ a int
+);
+CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
+ERROR: "unparted" is not partitioned
+CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1);
+ERROR: "unparted" is not partitioned
+DROP TABLE unparted;
+-- cannot create a permanent rel as partition of a temp rel
+CREATE TEMP TABLE temp_parted (
+ a int
+) PARTITION BY LIST (a);
+CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
+ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
+DROP TABLE temp_parted;
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE list_parted2 (
+ a varchar
+) PARTITION BY LIST (a);
+CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z');
+CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b');
+CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
+ERROR: partition "fail_part" would overlap partition "part_null_z"
+LINE 1: ...LE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
+ ^
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
+ERROR: partition "fail_part" would overlap partition "part_ab"
+LINE 1: ...ail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
+ ^
+-- check default partition overlap
+INSERT INTO list_parted2 VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
+ERROR: updated partition constraint for default partition "list_parted2_def" would be violated by some row
+CREATE TABLE range_parted2 (
+ a int
+) PARTITION BY RANGE (a);
+-- trying to create range partition with empty range
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
+ERROR: empty range bound specified for partition "fail_part"
+LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
+ ^
+DETAIL: Specified lower bound (1) is greater than or equal to upper bound (0).
+-- note that the range '[1, 1)' has no elements
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
+ERROR: empty range bound specified for partition "fail_part"
+LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
+ ^
+DETAIL: Specified lower bound (1) is greater than or equal to upper bound (1).
+CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2);
+ERROR: partition "fail_part" would overlap partition "part0"
+LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) ...
+ ^
+CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1);
+ERROR: partition "fail_part" would overlap partition "part0"
+LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1)...
+ ^
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue);
+ERROR: partition "fail_part" would overlap partition "part1"
+LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (max...
+ ^
+CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
+CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
+ERROR: partition "fail_part" would overlap partition "part2"
+LINE 1: ...art PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
+ ^
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
+ERROR: partition "fail_part" would overlap partition "part2"
+LINE 1: ...art PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
+ ^
+-- Create a default partition for range partitioned table
+CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT;
+-- More than one default partition is not allowed, so this should give error
+CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT;
+ERROR: partition "fail_default_part" conflicts with existing default partition "range2_default"
+LINE 1: ... TABLE fail_default_part PARTITION OF range_parted2 DEFAULT;
+ ^
+-- Check if the range for default partitions overlap
+INSERT INTO range_parted2 VALUES (85);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90);
+ERROR: updated partition constraint for default partition "range2_default" would be violated by some row
+CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100);
+-- now check for multi-column range partition key
+CREATE TABLE range_parted3 (
+ a int,
+ b int
+) PARTITION BY RANGE (a, (b+1));
+CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1);
+ERROR: partition "fail_part" would overlap partition "part00"
+LINE 1: ..._part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalu...
+ ^
+CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1);
+CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
+CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
+ERROR: partition "fail_part" would overlap partition "part12"
+LINE 1: ...rt PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1,...
+ ^
+CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT;
+-- cannot create a partition that says column b is allowed to range
+-- from -infinity to +infinity, while there exist partitions that have
+-- more specific ranges
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue);
+ERROR: partition "fail_part" would overlap partition "part10"
+LINE 1: ..._part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalu...
+ ^
+-- check for partition bound overlap and other invalid specifications for the hash partition
+CREATE TABLE hash_parted2 (
+ a varchar
+) PARTITION BY HASH (a);
+CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
+CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0);
+CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4);
+CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5);
+-- overlap with part_4
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
+ERROR: partition "fail_part" would overlap partition "h2part_4"
+LINE 1: ...LE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODU...
+ ^
+-- modulus must be greater than zero
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1);
+ERROR: modulus for hash partition must be an integer value greater than zero
+-- remainder must be greater than or equal to zero and less than modulus
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8);
+ERROR: remainder for hash partition must be less than modulus
+-- check schema propagation from parent
+CREATE TABLE parted (
+ a text,
+ b int NOT NULL DEFAULT 0,
+ CONSTRAINT check_a CHECK (length(a) > 0)
+) PARTITION BY LIST (a);
+CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a');
+-- only inherited attributes (never local ones)
+SELECT attname, attislocal, attinhcount FROM pg_attribute
+ WHERE attrelid = 'part_a'::regclass and attnum > 0
+ ORDER BY attnum;
+ attname | attislocal | attinhcount
+---------+------------+-------------
+ a | f | 1
+ b | f | 1
+(2 rows)
+
+-- able to specify column default, column constraint, and table constraint
+-- first check the "column specified more than once" error
+CREATE TABLE part_b PARTITION OF parted (
+ b NOT NULL,
+ b DEFAULT 1,
+ b CHECK (b >= 0),
+ CONSTRAINT check_a CHECK (length(a) > 0)
+) FOR VALUES IN ('b');
+ERROR: column "b" specified more than once
+CREATE TABLE part_b PARTITION OF parted (
+ b NOT NULL DEFAULT 1,
+ CONSTRAINT check_a CHECK (length(a) > 0),
+ CONSTRAINT check_b CHECK (b >= 0)
+) FOR VALUES IN ('b');
+NOTICE: merging constraint "check_a" with inherited definition
+-- conislocal should be false for any merged constraints, true otherwise
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
+ conislocal | coninhcount
+------------+-------------
+ f | 1
+ t | 0
+(2 rows)
+
+-- Once check_b is added to the parent, it should be made non-local for part_b
+ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
+NOTICE: merging constraint "check_b" with inherited definition
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+ conislocal | coninhcount
+------------+-------------
+ f | 1
+ f | 1
+(2 rows)
+
+-- Neither check_a nor check_b are droppable from part_b
+ALTER TABLE part_b DROP CONSTRAINT check_a;
+ERROR: cannot drop inherited constraint "check_a" of relation "part_b"
+ALTER TABLE part_b DROP CONSTRAINT check_b;
+ERROR: cannot drop inherited constraint "check_b" of relation "part_b"
+-- And dropping it from parted should leave no trace of them on part_b, unlike
+-- traditional inheritance where they will be left behind, because they would
+-- be local constraints.
+ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+ conislocal | coninhcount
+------------+-------------
+(0 rows)
+
+-- specify PARTITION BY for a partition
+CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
+ERROR: column "c" named in partition key does not exist
+LINE 1: ...TITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
+ ^
+CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+-- create a level-2 partition
+CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+-- check that NOT NULL and default value are inherited correctly
+create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a);
+create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1);
+insert into parted_notnull_inh_test (b) values (null);
+ERROR: null value in column "b" of relation "parted_notnull_inh_test1" violates not-null constraint
+DETAIL: Failing row contains (1, null).
+-- note that while b's default is overridden, a's default is preserved
+\d parted_notnull_inh_test1
+ Table "public.parted_notnull_inh_test1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null | 1
+ b | integer | | not null | 1
+Partition of: parted_notnull_inh_test FOR VALUES IN (1)
+
+drop table parted_notnull_inh_test;
+-- check that collations are assigned in partition bound expressions
+create table parted_boolean_col (a bool, b text) partition by list(a);
+create table parted_boolean_less partition of parted_boolean_col
+ for values in ('foo' < 'bar');
+create table parted_boolean_greater partition of parted_boolean_col
+ for values in ('foo' > 'bar');
+drop table parted_boolean_col;
+-- check for a conflicting COLLATE clause
+create table parted_collate_must_match (a text collate "C", b text collate "C")
+ partition by range (a);
+-- on the partition key
+create table parted_collate_must_match1 partition of parted_collate_must_match
+ (a collate "POSIX") for values from ('a') to ('m');
+-- on another column
+create table parted_collate_must_match2 partition of parted_collate_must_match
+ (b collate "POSIX") for values from ('m') to ('z');
+drop table parted_collate_must_match;
+-- check that non-matching collations for partition bound
+-- expressions are coerced to the right collation
+create table test_part_coll_posix (a text) partition by range (a collate "POSIX");
+-- ok, collation is implicitly coerced
+create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g');
+-- ok
+create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m');
+-- ok, collation is implicitly coerced
+create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s');
+-- ok; partition collation silently overrides the default collation of type 'name'
+create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z');
+drop table test_part_coll_posix;
+-- Partition bound in describe output
+\d+ part_b
+ Table "public.part_b"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 1 | plain | |
+Partition of: parted FOR VALUES IN ('b')
+Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
+
+-- Both partition bound and partition key in describe output
+\d+ part_c
+ Partitioned table "public.part_c"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 0 | plain | |
+Partition of: parted FOR VALUES IN ('c')
+Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
+Partition key: RANGE (b)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+ Table "public.part_c_1_10"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 0 | plain | |
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
+
+-- Show partition count in the parent's describe output
+-- Tempted to include \d+ output listing partitions with bound info but
+-- output could vary depending on the order in which partition oids are
+-- returned.
+\d parted
+ Partitioned table "public.parted"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | text | | |
+ b | integer | | not null | 0
+Partition key: LIST (a)
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d hash_parted
+ Partitioned table "public.hash_parted"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: HASH (a)
+Number of partitions: 4 (Use \d+ to list them.)
+
+-- check that we get the expected partition constraints
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE);
+\d+ unbounded_range_part
+ Table "public.unbounded_range_part"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL))
+
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE);
+\d+ range_parted4_1
+ Table "public.range_parted4_1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))
+
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
+\d+ range_parted4_2
+ Table "public.range_parted4_2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))
+
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE);
+\d+ range_parted4_3
+ Table "public.range_parted4_3"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+ c | integer | | | | plain | |
+Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE)
+Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))
+
+DROP TABLE range_parted4;
+-- user-defined operator class in partition key
+CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql
+ AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$;
+CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS
+ OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4),
+ OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4),
+ OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4);
+CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops);
+CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000);
+INSERT INTO partkey_t VALUES (100);
+INSERT INTO partkey_t VALUES (200);
+-- cleanup
+DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
+DROP TABLE partkey_t, hash_parted, hash_parted2;
+DROP OPERATOR CLASS test_int4_ops USING btree;
+DROP FUNCTION my_int4_sort(int4,int4);
+-- comments on partitioned tables columns
+CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
+COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
+COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
+SELECT obj_description('parted_col_comment'::regclass);
+ obj_description
+----------------------
+ Am partitioned table
+(1 row)
+
+\d+ parted_col_comment
+ Partitioned table "public.parted_col_comment"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+---------------
+ a | integer | | | | plain | | Partition key
+ b | text | | | | extended | |
+Partition key: LIST (a)
+Number of partitions: 0
+
+DROP TABLE parted_col_comment;
+-- list partitioning on array type column
+CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
+CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
+\d+ arrlp12
+ Table "public.arrlp12"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-----------+-----------+----------+---------+----------+--------------+-------------
+ a | integer[] | | | | extended | |
+Partition of: arrlp FOR VALUES IN ('{1}', '{2}')
+Partition constraint: ((a IS NOT NULL) AND ((a = '{1}'::integer[]) OR (a = '{2}'::integer[])))
+
+DROP TABLE arrlp;
+-- partition on boolean column
+create table boolspart (a bool) partition by list (a);
+create table boolspart_t partition of boolspart for values in (true);
+create table boolspart_f partition of boolspart for values in (false);
+\d+ boolspart
+ Partitioned table "public.boolspart"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | boolean | | | | plain | |
+Partition key: LIST (a)
+Partitions: boolspart_f FOR VALUES IN (false),
+ boolspart_t FOR VALUES IN (true)
+
+drop table boolspart;
+-- partitions mixing temporary and permanent relations
+create table perm_parted (a int) partition by list (a);
+create temporary table temp_parted (a int) partition by list (a);
+create table perm_part partition of temp_parted default; -- error
+ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
+create temp table temp_part partition of perm_parted default; -- error
+ERROR: cannot create a temporary relation as partition of permanent relation "perm_parted"
+create temp table temp_part partition of temp_parted default; -- ok
+drop table perm_parted cascade;
+drop table temp_parted cascade;
+-- check that adding partitions to a table while it is being used is prevented
+create table tab_part_create (a int) partition by list (a);
+create or replace function func_part_create() returns trigger
+ language plpgsql as $$
+ begin
+ execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)';
+ return null;
+ end $$;
+create trigger trig_part_create before insert on tab_part_create
+ for each statement execute procedure func_part_create();
+insert into tab_part_create values (1);
+ERROR: cannot CREATE TABLE .. PARTITION OF "tab_part_create" because it is being used by active queries in this session
+CONTEXT: SQL statement "create table tab_part_create_1 partition of tab_part_create for values in (1)"
+PL/pgSQL function func_part_create() line 3 at EXECUTE
+drop table tab_part_create;
+drop function func_part_create();
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by range (partkey);
+create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp);
+create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue);
+-- this should go into the partition volatile_partbound_test2
+insert into volatile_partbound_test values (current_timestamp);
+select tableoid::regclass from volatile_partbound_test;
+ tableoid
+--------------------------
+ volatile_partbound_test2
+(1 row)
+
+drop table volatile_partbound_test;
+-- test the case where a check constraint on default partition allows
+-- to avoid scanning it when adding a new partition
+create table defcheck (a int, b int) partition by list (b);
+create table defcheck_def (a int, c int, b int);
+alter table defcheck_def drop c;
+alter table defcheck attach partition defcheck_def default;
+alter table defcheck_def add check (b <= 0 and b is not null);
+create table defcheck_1 partition of defcheck for values in (1, null);
+-- test that complex default partition constraints are enforced correctly
+insert into defcheck_def values (0, 0);
+create table defcheck_0 partition of defcheck for values in (0);
+ERROR: updated partition constraint for default partition "defcheck_def" would be violated by some row
+drop table defcheck;
+-- tests of column drop with partition tables and indexes using
+-- predicates and expressions.
+create table part_column_drop (
+ useless_1 int,
+ id int,
+ useless_2 int,
+ d int,
+ b int,
+ useless_3 int
+) partition by range (id);
+alter table part_column_drop drop column useless_1;
+alter table part_column_drop drop column useless_2;
+alter table part_column_drop drop column useless_3;
+create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
+create index part_column_drop_b_expr on part_column_drop((b = 1));
+create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
+create index part_column_drop_d_expr on part_column_drop((d = 2));
+create table part_column_drop_1_10 partition of
+ part_column_drop for values from (1) to (10);
+\d part_column_drop
+ Partitioned table "public.part_column_drop"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | |
+ d | integer | | |
+ b | integer | | |
+Partition key: RANGE (id)
+Indexes:
+ "part_column_drop_b_expr" btree ((b = 1))
+ "part_column_drop_b_pred" btree (b) WHERE b = 1
+ "part_column_drop_d_expr" btree ((d = 2))
+ "part_column_drop_d_pred" btree (d) WHERE d = 2
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d part_column_drop_1_10
+ Table "public.part_column_drop_1_10"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | |
+ d | integer | | |
+ b | integer | | |
+Partition of: part_column_drop FOR VALUES FROM (1) TO (10)
+Indexes:
+ "part_column_drop_1_10_b_idx" btree (b) WHERE b = 1
+ "part_column_drop_1_10_d_idx" btree (d) WHERE d = 2
+ "part_column_drop_1_10_expr_idx" btree ((b = 1))
+ "part_column_drop_1_10_expr_idx1" btree ((d = 2))
+
+drop table part_column_drop;
diff --git a/src/test/modules/test_ddl_deparse_regress/expected/customized_tests.out b/src/test/modules/test_ddl_deparse_regress/expected/customized_tests.out
new file mode 100644
index 0000000000..0f701bd0ef
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/expected/customized_tests.out
@@ -0,0 +1,4 @@
+CREATE TABLE hello (
+ id int,
+ name varchar(100)
+);
diff --git a/src/test/modules/test_ddl_deparse_regress/included_tests b/src/test/modules/test_ddl_deparse_regress/included_tests
new file mode 100644
index 0000000000..9ec58058c6
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/included_tests
@@ -0,0 +1,2 @@
+create_table
+customized_tests
diff --git a/src/test/modules/test_ddl_deparse_regress/sql/create_table.sql b/src/test/modules/test_ddl_deparse_regress/sql/create_table.sql
new file mode 100644
index 0000000000..ac9e91aeed
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/sql/create_table.sql
@@ -0,0 +1,735 @@
+--
+-- CREATE_TABLE
+--
+
+-- Error cases
+CREATE TABLE unknowntab (
+ u unknown -- fail
+);
+
+CREATE TYPE unknown_comptype AS (
+ u unknown -- fail
+);
+
+-- invalid: non-lowercase quoted reloptions identifiers
+CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a;
+
+CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK
+CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
+REINDEX INDEX unlogged1_pkey;
+REINDEX INDEX unlogged2_pkey;
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
+DROP TABLE unlogged2;
+INSERT INTO unlogged1 VALUES (42);
+CREATE UNLOGGED TABLE public.unlogged2 (a int primary key); -- also OK
+CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); -- not OK
+CREATE TABLE pg_temp.implicitly_temp (a int primary key); -- OK
+CREATE TEMP TABLE explicitly_temp (a int primary key); -- also OK
+CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key); -- also OK
+CREATE TEMP TABLE public.temp_to_perm (a int primary key); -- not OK
+DROP TABLE unlogged1, public.unlogged2;
+
+CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
+CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
+CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
+DROP TABLE as_select1;
+
+PREPARE select1 AS SELECT 1 as a;
+CREATE TABLE as_select1 AS EXECUTE select1;
+CREATE TABLE as_select1 AS EXECUTE select1;
+SELECT * FROM as_select1;
+CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1;
+DROP TABLE as_select1;
+DEALLOCATE select1;
+
+-- create an extra wide table to test for issues related to that
+-- (temporarily hide query, to avoid the long CREATE TABLE stmt)
+\set ECHO none
+SELECT 'CREATE TABLE extra_wide_table(firstc text, '|| array_to_string(array_agg('c'||i||' bool'),',')||', lastc text);'
+FROM generate_series(1, 1100) g(i)
+\gexec
+\set ECHO all
+INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col');
+SELECT firstc, lastc FROM extra_wide_table;
+
+-- check that tables with oids cannot be created anymore
+CREATE TABLE withoid() WITH OIDS;
+CREATE TABLE withoid() WITH (oids);
+CREATE TABLE withoid() WITH (oids = true);
+
+-- but explicitly not adding oids is still supported
+-- CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
+-- CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
+
+-- check restriction with default expressions
+-- invalid use of column reference in default expressions
+CREATE TABLE default_expr_column (id int DEFAULT (id));
+CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
+CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
+-- invalid column definition
+CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
+-- invalid use of aggregate
+CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
+-- invalid use of subquery
+CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
+-- invalid use of set-returning function
+CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
+
+-- Verify that subtransaction rollback restores rd_createSubid.
+BEGIN;
+CREATE TABLE remember_create_subid (c int);
+SAVEPOINT q; DROP TABLE remember_create_subid; ROLLBACK TO q;
+COMMIT;
+DROP TABLE remember_create_subid;
+
+-- Verify that subtransaction rollback restores rd_firstRelfilenodeSubid.
+CREATE TABLE remember_node_subid (c int);
+BEGIN;
+ALTER TABLE remember_node_subid ALTER c TYPE bigint;
+SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q;
+COMMIT;
+DROP TABLE remember_node_subid;
+
+--
+-- Partitioned tables
+--
+
+-- cannot combine INHERITS and PARTITION BY (although grammar allows)
+CREATE TABLE partitioned (
+ a int
+) INHERITS (some_table) PARTITION BY LIST (a);
+
+-- cannot use more than 1 column as partition key for list partitioned table
+CREATE TABLE partitioned (
+ a1 int,
+ a2 int
+) PARTITION BY LIST (a1, a2); -- fail
+
+-- unsupported constraint type for partitioned tables
+CREATE TABLE partitioned (
+ a int,
+ EXCLUDE USING gist (a WITH &&)
+) PARTITION BY RANGE (a);
+
+-- prevent using prohibited expressions in the key
+CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (retset(a));
+DROP FUNCTION retset(int);
+
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE ((avg(a)));
+
+CREATE TABLE partitioned (
+ a int,
+ b int
+) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b)));
+
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY LIST ((a LIKE (SELECT 1)));
+
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE ((42));
+
+CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (const_func());
+DROP FUNCTION const_func();
+
+-- only accept valid partitioning strategy
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY MAGIC (a);
+
+-- specified column must be present in the table
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (b);
+
+-- cannot use system columns in partition key
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (xmin);
+
+-- cannot use pseudotypes
+CREATE TABLE partitioned (
+ a int,
+ b int
+) PARTITION BY RANGE (((a, b)));
+CREATE TABLE partitioned (
+ a int,
+ b int
+) PARTITION BY RANGE (a, ('unknown'));
+
+-- functions in key must be immutable
+CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL;
+CREATE TABLE partitioned (
+ a int
+) PARTITION BY RANGE (immut_func(a));
+DROP FUNCTION immut_func(int);
+
+-- prevent using columns of unsupported types in key (type must have a btree operator class)
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY LIST (a);
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY LIST (a point_ops);
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY RANGE (a);
+CREATE TABLE partitioned (
+ a point
+) PARTITION BY RANGE (a point_ops);
+
+-- cannot add NO INHERIT constraints to partitioned tables
+CREATE TABLE partitioned (
+ a int,
+ CONSTRAINT check_a CHECK (a > 0) NO INHERIT
+) PARTITION BY RANGE (a);
+
+-- some checks after successful creation of a partitioned table
+CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL;
+
+CREATE TABLE partitioned (
+ a int,
+ b int,
+ c text,
+ d text
+) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C");
+
+-- check relkind
+SELECT relkind FROM pg_class WHERE relname = 'partitioned';
+
+-- prevent a function referenced in partition key from being dropped
+DROP FUNCTION plusone(int);
+
+-- partitioned table cannot participate in regular inheritance
+CREATE TABLE partitioned2 (
+ a int,
+ b text
+) PARTITION BY RANGE ((a+1), substr(b, 1, 5));
+CREATE TABLE fail () INHERITS (partitioned2);
+
+-- Partition key in describe output
+\d partitioned
+\d+ partitioned2
+
+INSERT INTO partitioned2 VALUES (1, 'hello');
+CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc');
+\d+ part2_1
+
+DROP TABLE partitioned, partitioned2;
+
+-- check reference to partitioned table's rowtype in partition descriptor
+create table partitioned (a int, b int)
+ partition by list ((row(a, b)::partitioned));
+create table partitioned1
+ partition of partitioned for values in ('(1,2)'::partitioned);
+create table partitioned2
+ partition of partitioned for values in ('(2,4)'::partitioned);
+explain (costs off)
+select * from partitioned where row(a,b)::partitioned = '(1,2)'::partitioned;
+drop table partitioned;
+
+-- whole-row Var in partition key works too
+create table partitioned (a int, b int)
+ partition by list ((partitioned));
+create table partitioned1
+ partition of partitioned for values in ('(1,2)');
+create table partitioned2
+ partition of partitioned for values in ('(2,4)');
+explain (costs off)
+select * from partitioned where partitioned = '(1,2)'::partitioned;
+\d+ partitioned1
+drop table partitioned;
+
+-- check that dependencies of partition columns are handled correctly
+create domain intdom1 as int;
+
+create table partitioned (
+ a intdom1,
+ b text
+) partition by range (a);
+
+alter table partitioned drop column a; -- fail
+
+drop domain intdom1; -- fail, requires cascade
+
+drop domain intdom1 cascade;
+
+table partitioned; -- gone
+
+-- likewise for columns used in partition expressions
+create domain intdom1 as int;
+
+create table partitioned (
+ a intdom1,
+ b text
+) partition by range (plusone(a));
+
+alter table partitioned drop column a; -- fail
+
+drop domain intdom1; -- fail, requires cascade
+
+drop domain intdom1 cascade;
+
+table partitioned; -- gone
+
+
+--
+-- Partitions
+--
+
+-- check partition bound syntax
+
+CREATE TABLE list_parted (
+ a int
+) PARTITION BY LIST (a);
+CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1');
+CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2);
+CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1));
+CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
+\d+ list_parted
+
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename);
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (generate_series(4, 6));
+CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "POSIX");
+
+-- syntax does not allow empty list of values for list partitions
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
+-- trying to specify range for list partitioned table
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+
+-- check default partition cannot be created more than once
+CREATE TABLE part_default PARTITION OF list_parted DEFAULT;
+CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE bools (
+ a bool
+) PARTITION BY LIST (a);
+CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
+DROP TABLE bools;
+
+-- specified literal can be cast, and the cast might not be immutable
+CREATE TABLE moneyp (
+ a money
+) PARTITION BY LIST (a);
+CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
+CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11');
+CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int);
+DROP TABLE moneyp;
+
+-- cast is immutable
+CREATE TABLE bigintp (
+ a bigint
+) PARTITION BY LIST (a);
+CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10);
+-- fails due to overlap:
+CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10');
+DROP TABLE bigintp;
+
+CREATE TABLE range_parted (
+ a date
+) PARTITION BY RANGE (a);
+
+-- forbidden expressions for partition bounds with range partitioned table
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (somename) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (somename.somename) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (a) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (max(a)) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (max(somename)) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM ((select 1)) TO ('2019-01-01');
+CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
+ FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01');
+
+-- trying to specify list for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
+-- trying to specify modulus and remainder for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+-- each of start and end bounds must have same number of values as the
+-- length of the partition key
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
+
+-- cannot specify null values in range bounds
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue);
+
+-- trying to specify modulus and remainder for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+
+-- check partition bound syntax for the hash partition
+CREATE TABLE hash_parted (
+ a int
+) PARTITION BY HASH (a);
+CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0);
+CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1);
+CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2);
+CREATE TABLE hpart_4 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 3);
+-- modulus 25 is factor of modulus of 50 but 10 is not a factor of 25.
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 25, REMAINDER 3);
+-- previous modulus 50 is factor of 150 but this modulus is not a factor of next modulus 200.
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3);
+-- overlapping remainders
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 100, REMAINDER 3);
+-- trying to specify range for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+-- trying to specify list value for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+
+-- trying to create default partition for the hash partitioned table
+CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT;
+
+-- check if compatible with the specified parent
+
+-- cannot create as partition of a non-partitioned table
+CREATE TABLE unparted (
+ a int
+);
+CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
+CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1);
+DROP TABLE unparted;
+
+-- cannot create a permanent rel as partition of a temp rel
+CREATE TEMP TABLE temp_parted (
+ a int
+) PARTITION BY LIST (a);
+CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
+DROP TABLE temp_parted;
+
+-- check for partition bound overlap and other invalid specifications
+
+CREATE TABLE list_parted2 (
+ a varchar
+) PARTITION BY LIST (a);
+CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z');
+CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b');
+CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
+
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
+-- check default partition overlap
+INSERT INTO list_parted2 VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
+
+CREATE TABLE range_parted2 (
+ a int
+) PARTITION BY RANGE (a);
+
+-- trying to create range partition with empty range
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
+-- note that the range '[1, 1)' has no elements
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
+
+CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2);
+CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue);
+CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
+CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
+
+-- Create a default partition for range partitioned table
+CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT;
+
+-- More than one default partition is not allowed, so this should give error
+CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT;
+
+-- Check if the range for default partitions overlap
+INSERT INTO range_parted2 VALUES (85);
+CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90);
+CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100);
+
+-- now check for multi-column range partition key
+CREATE TABLE range_parted3 (
+ a int,
+ b int
+) PARTITION BY RANGE (a, (b+1));
+
+CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1);
+
+CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1);
+CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
+CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue);
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
+CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT;
+
+-- cannot create a partition that says column b is allowed to range
+-- from -infinity to +infinity, while there exist partitions that have
+-- more specific ranges
+CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue);
+
+-- check for partition bound overlap and other invalid specifications for the hash partition
+CREATE TABLE hash_parted2 (
+ a varchar
+) PARTITION BY HASH (a);
+CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
+CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0);
+CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4);
+CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5);
+-- overlap with part_4
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
+-- modulus must be greater than zero
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1);
+-- remainder must be greater than or equal to zero and less than modulus
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8);
+
+-- check schema propagation from parent
+
+CREATE TABLE parted (
+ a text,
+ b int NOT NULL DEFAULT 0,
+ CONSTRAINT check_a CHECK (length(a) > 0)
+) PARTITION BY LIST (a);
+
+CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a');
+
+-- only inherited attributes (never local ones)
+SELECT attname, attislocal, attinhcount FROM pg_attribute
+ WHERE attrelid = 'part_a'::regclass and attnum > 0
+ ORDER BY attnum;
+
+-- able to specify column default, column constraint, and table constraint
+
+-- first check the "column specified more than once" error
+CREATE TABLE part_b PARTITION OF parted (
+ b NOT NULL,
+ b DEFAULT 1,
+ b CHECK (b >= 0),
+ CONSTRAINT check_a CHECK (length(a) > 0)
+) FOR VALUES IN ('b');
+
+CREATE TABLE part_b PARTITION OF parted (
+ b NOT NULL DEFAULT 1,
+ CONSTRAINT check_a CHECK (length(a) > 0),
+ CONSTRAINT check_b CHECK (b >= 0)
+) FOR VALUES IN ('b');
+-- conislocal should be false for any merged constraints, true otherwise
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
+
+-- Once check_b is added to the parent, it should be made non-local for part_b
+ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+
+-- Neither check_a nor check_b are droppable from part_b
+ALTER TABLE part_b DROP CONSTRAINT check_a;
+ALTER TABLE part_b DROP CONSTRAINT check_b;
+
+-- And dropping it from parted should leave no trace of them on part_b, unlike
+-- traditional inheritance where they will be left behind, because they would
+-- be local constraints.
+ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+
+-- specify PARTITION BY for a partition
+CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
+CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+
+-- create a level-2 partition
+CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+
+-- check that NOT NULL and default value are inherited correctly
+create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a);
+create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1);
+insert into parted_notnull_inh_test (b) values (null);
+-- note that while b's default is overridden, a's default is preserved
+\d parted_notnull_inh_test1
+drop table parted_notnull_inh_test;
+
+-- check that collations are assigned in partition bound expressions
+create table parted_boolean_col (a bool, b text) partition by list(a);
+create table parted_boolean_less partition of parted_boolean_col
+ for values in ('foo' < 'bar');
+create table parted_boolean_greater partition of parted_boolean_col
+ for values in ('foo' > 'bar');
+drop table parted_boolean_col;
+
+-- check for a conflicting COLLATE clause
+create table parted_collate_must_match (a text collate "C", b text collate "C")
+ partition by range (a);
+-- on the partition key
+create table parted_collate_must_match1 partition of parted_collate_must_match
+ (a collate "POSIX") for values from ('a') to ('m');
+-- on another column
+create table parted_collate_must_match2 partition of parted_collate_must_match
+ (b collate "POSIX") for values from ('m') to ('z');
+drop table parted_collate_must_match;
+
+-- check that non-matching collations for partition bound
+-- expressions are coerced to the right collation
+
+create table test_part_coll_posix (a text) partition by range (a collate "POSIX");
+-- ok, collation is implicitly coerced
+create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g');
+-- ok
+create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m');
+-- ok, collation is implicitly coerced
+create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s');
+-- ok; partition collation silently overrides the default collation of type 'name'
+create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z');
+
+drop table test_part_coll_posix;
+
+-- Partition bound in describe output
+\d+ part_b
+
+-- Both partition bound and partition key in describe output
+\d+ part_c
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+
+-- Show partition count in the parent's describe output
+-- Tempted to include \d+ output listing partitions with bound info but
+-- output could vary depending on the order in which partition oids are
+-- returned.
+\d parted
+\d hash_parted
+
+-- check that we get the expected partition constraints
+CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
+CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE);
+\d+ unbounded_range_part
+DROP TABLE unbounded_range_part;
+CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE);
+\d+ range_parted4_1
+CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
+\d+ range_parted4_2
+CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE);
+\d+ range_parted4_3
+DROP TABLE range_parted4;
+
+-- user-defined operator class in partition key
+CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql
+ AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$;
+CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS
+ OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4),
+ OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4),
+ OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4);
+CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops);
+CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000);
+INSERT INTO partkey_t VALUES (100);
+INSERT INTO partkey_t VALUES (200);
+
+-- cleanup
+DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
+DROP TABLE partkey_t, hash_parted, hash_parted2;
+DROP OPERATOR CLASS test_int4_ops USING btree;
+DROP FUNCTION my_int4_sort(int4,int4);
+
+-- comments on partitioned tables columns
+CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
+COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
+COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
+SELECT obj_description('parted_col_comment'::regclass);
+\d+ parted_col_comment
+DROP TABLE parted_col_comment;
+
+-- list partitioning on array type column
+CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
+CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
+\d+ arrlp12
+DROP TABLE arrlp;
+
+-- partition on boolean column
+create table boolspart (a bool) partition by list (a);
+create table boolspart_t partition of boolspart for values in (true);
+create table boolspart_f partition of boolspart for values in (false);
+\d+ boolspart
+drop table boolspart;
+
+-- partitions mixing temporary and permanent relations
+create table perm_parted (a int) partition by list (a);
+create temporary table temp_parted (a int) partition by list (a);
+create table perm_part partition of temp_parted default; -- error
+create temp table temp_part partition of perm_parted default; -- error
+create temp table temp_part partition of temp_parted default; -- ok
+drop table perm_parted cascade;
+drop table temp_parted cascade;
+
+-- check that adding partitions to a table while it is being used is prevented
+create table tab_part_create (a int) partition by list (a);
+create or replace function func_part_create() returns trigger
+ language plpgsql as $$
+ begin
+ execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)';
+ return null;
+ end $$;
+create trigger trig_part_create before insert on tab_part_create
+ for each statement execute procedure func_part_create();
+insert into tab_part_create values (1);
+drop table tab_part_create;
+drop function func_part_create();
+
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by range (partkey);
+create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp);
+create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue);
+-- this should go into the partition volatile_partbound_test2
+insert into volatile_partbound_test values (current_timestamp);
+select tableoid::regclass from volatile_partbound_test;
+drop table volatile_partbound_test;
+
+-- test the case where a check constraint on default partition allows
+-- to avoid scanning it when adding a new partition
+create table defcheck (a int, b int) partition by list (b);
+create table defcheck_def (a int, c int, b int);
+alter table defcheck_def drop c;
+alter table defcheck attach partition defcheck_def default;
+alter table defcheck_def add check (b <= 0 and b is not null);
+create table defcheck_1 partition of defcheck for values in (1, null);
+
+-- test that complex default partition constraints are enforced correctly
+insert into defcheck_def values (0, 0);
+create table defcheck_0 partition of defcheck for values in (0);
+drop table defcheck;
+
+-- tests of column drop with partition tables and indexes using
+-- predicates and expressions.
+create table part_column_drop (
+ useless_1 int,
+ id int,
+ useless_2 int,
+ d int,
+ b int,
+ useless_3 int
+) partition by range (id);
+alter table part_column_drop drop column useless_1;
+alter table part_column_drop drop column useless_2;
+alter table part_column_drop drop column useless_3;
+create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
+create index part_column_drop_b_expr on part_column_drop((b = 1));
+create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
+create index part_column_drop_d_expr on part_column_drop((d = 2));
+create table part_column_drop_1_10 partition of
+ part_column_drop for values from (1) to (10);
+\d part_column_drop
+\d part_column_drop_1_10
+drop table part_column_drop;
diff --git a/src/test/modules/test_ddl_deparse_regress/sql/customized_tests.sql b/src/test/modules/test_ddl_deparse_regress/sql/customized_tests.sql
new file mode 100644
index 0000000000..0f701bd0ef
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/sql/customized_tests.sql
@@ -0,0 +1,4 @@
+CREATE TABLE hello (
+ id int,
+ name varchar(100)
+);
diff --git a/src/test/modules/test_ddl_deparse_regress/t/001_deparse_regress.pl b/src/test/modules/test_ddl_deparse_regress/t/001_deparse_regress.pl
new file mode 100644
index 0000000000..fed9044229
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse_regress/t/001_deparse_regress.pl
@@ -0,0 +1,200 @@
+use strict;
+use warnings;
+use Env;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use File::Basename;
+
+sub execute_regress_test {
+ my $test_name = $_[0];
+ my $dbname = "postgres";
+ my $dlpath = dirname($ENV{REGRESS_SHLIB});
+ my $inputdir = ".";
+ # I copied create_table.sql and comment the failed test case line 62-63, to show how this testing framework works
+ # Change $inputdir to regress folder after deparser issue is fixed or you can also add your own test cases under this folder
+ # my $inputdir = "../../regress";
+ my $outputdir = $PostgreSQL::Test::Utils::tmp_check;
+
+ # Create and start pub sub nodes
+ my $pub_node = init_pub_node($test_name);
+ my $sub_node = init_sub_node($test_name);
+ $pub_node -> start;
+ $sub_node -> start;
+
+ # Set up deparse testing resources
+ create_deparse_testing_resources_on_pub_node($pub_node, $dbname);
+
+ # Execute regression test for $test_name on pub node
+ my $rc = system($ENV{PG_REGRESS}
+ . " "
+ . "--dlpath=\"$dlpath\" "
+ . "--dbname="
+ . $dbname . " "
+ . "--use-existing "
+ . "--host="
+ . $pub_node->host . " "
+ . "--port="
+ . $pub_node->port . " "
+ . "--inputdir=$inputdir "
+ . "--outputdir=\"$outputdir\" "
+ . $test_name);
+ if ($rc != 0)
+ {
+ # If regression test fails, dump out the regression diffs file
+ my $diffs = "${outputdir}/regression/${test_name}.diffs";
+ if (-e $diffs)
+ {
+ print "=== dumping $diffs ===\n";
+ print slurp_file($diffs);
+ print "=== EOF ===\n";
+ }
+ die "Regression test failed";
+ }
+
+ # Retrieve SQL commands generated from deparsed DDLs on pub node
+ my $ddl_sql = '';
+ $pub_node -> psql($dbname,q(
+ select ddl_deparse_expand_command(ddl) || ';' from deparsed_ddls ORDER BY id ASC),
+ stdout => \$ddl_sql);
+
+ # Execute SQL commands on sub node
+ $sub_node -> psql($dbname, $ddl_sql);
+
+ # Clean up deparse testing resources
+ clean_deparse_testing_resources_on_pub_node($pub_node, $dbname);
+
+ # Dump from pub node and sub node
+ mkdir ${outputdir}."/dumps", 0755;
+ my $pub_dump = ${outputdir}."/dumps/${test_name}_pub.dump";
+ my $sub_dump = ${outputdir}."/dumps/${test_name}_sub.dump";
+ system("pg_dumpall "
+ . "-s "
+ . "-f "
+ . $pub_dump . " "
+ . "--no-sync "
+ . '-p '
+ . $pub_node->port) == 0 or die "Dump pub node failed";
+ system("pg_dumpall "
+ . "-s "
+ . "-f "
+ . $sub_dump . " "
+ . "--no-sync "
+ . '-p '
+ . $sub_node->port) == 0 or die "Dump sub node failed";
+
+ # Compare dumped results
+ is(system("diff "
+ . $pub_dump . " "
+ . $sub_dump), 0, "Comparing dumped output");
+
+ # Close nodes
+ $pub_node->stop;
+ $sub_node->stop;
+}
+
+sub init_node {
+ my $node_name = $_[0];
+ my $node = PostgreSQL::Test::Cluster->new($node_name);
+ $node->init;
+ # Increase some settings that Cluster->new makes too low by default.
+ $node->adjust_conf('postgresql.conf', 'max_connections', '25');
+ $node->append_conf('postgresql.conf',
+ 'max_prepared_transactions = 10');
+ return $node;
+}
+
+sub init_pub_node {
+ my $node_name = $_[0]."_pub";
+ return init_node($node_name)
+}
+
+sub init_sub_node {
+ my $node_name = $_[0]."_sub";
+ return init_node($node_name)
+}
+
+sub create_deparse_testing_resources_on_pub_node {
+ my $node = $_[0];
+ my $dbname = $_[1];
+ $node -> psql($dbname, q(
+ begin;
+ create table deparsed_ddls(id SERIAL PRIMARY KEY, tag text, object_identity text, ddl text);
+
+ create or replace function deparse_to_json()
+ returns event_trigger language plpgsql as
+ $$
+ declare
+ r record;
+ begin
+ for r in select * from pg_event_trigger_ddl_commands()
+ loop
+ insert into deparsed_ddls(tag, object_identity, ddl) values (r.command_tag, r.object_identity, pg_catalog.ddl_deparse_to_json(r.command));
+ end loop;
+ END;
+ $$;
+
+ create or replace function deparse_drops_to_json()
+ returns event_trigger language plpgsql as
+ $$
+ declare
+ r record;
+ begin
+ for r in select * from pg_event_trigger_dropped_objects()
+ loop
+ insert into deparsed_ddls(tag, object_identity, ddl) values (r.object_type, r.object_identity, pg_catalog.deparse_drop_ddl(r.object_identity, r.object_type));
+ end loop;
+ END;
+ $$;
+
+ create event trigger ddl_deparse_trig
+ on ddl_command_end execute procedure deparse_to_json();
+
+ create event trigger ddl_drops_deparse_trig
+ on sql_drop execute procedure deparse_drops_to_json();
+
+ commit;
+ ));
+}
+
+sub clean_deparse_testing_resources_on_pub_node {
+ my $node = $_[0];
+ my $dbname = $_[1];
+ # Drop the event trigger and the function before taking a logical dump.
+ $node -> safe_psql($dbname,q(
+ drop event trigger ddl_deparse_trig;
+ drop event trigger ddl_drops_deparse_trig;
+ drop function deparse_to_json();
+ drop function deparse_drops_to_json();
+ drop table deparsed_ddls;
+ ));
+}
+
+sub trim {
+ my @out = @_;
+ for (@out) {
+ s/^\s+//;
+ s/\s+$//;
+ }
+ return wantarray ? @out : $out[0];
+}
+
+my $included_regress_tests_file = "./included_tests";
+open(FH, $included_regress_tests_file) or die "File ${included_regress_tests_file} can not be opened";
+while(<FH>)
+{
+ my $test_name = trim($_);
+ if ($test_name eq "")
+ {
+ next;
+ }
+ eval {execute_regress_test($test_name);};
+ print $@;
+ if ($@ ne "")
+ {
+ fail($@);
+ }
+}
+close;
+
+done_testing();
