pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgpartman/pg_partman] PR #886: Copy exclusion constraints from the template table to child tables
4+ messages / 2 participants
[nested] [flat]

* [pgpartman/pg_partman] PR #886: Copy exclusion constraints from the template table to child tables
@ 2026-11-09 19:22  "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
  0 siblings, 0 replies; 4+ messages in thread

From: ritojoao (@ritojoao) @ 2026-11-09 19:22 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

Closes #861

When the partition key is an expression, Postgres doesn't allow a primary key or an exclusion constraint on the parent, so both have to go on the template table. `inherit_template_properties()` copied the primary key but not the exclusion constraint, because it only looks at indexes with `indisunique`.

Changes:
- `inherit_template_properties()` now reads exclusion constraints (`contype = 'x'`) from the template and adds them to each child with `ALTER TABLE ... ADD <pg_get_constraintdef>`. A constraint the child already has is skipped. If the template index has a tablespace, it is kept with `USING INDEX TABLESPACE`.
- The inheritance table in `doc/pg_partman.md` gets an `exclusion constraint` row.
- New test `test/test-id-template-exclusion.sql`: expression partition key, primary key and exclusion constraint on the template only. It checks the constraint on the initial children, the default and children made by `run_maintenance()`, and that an overlapping insert fails with `23P01`.

Tested with the CI steps (pgxn-tools, `pg_prove --ext .sql test`) on PG 14, 15, 16, 17 and 18. The new test fails on `development` without the change.

I know the template table is meant to be phased out. For expression partition keys it is still the only place these constraints can live, so this seemed worth closing. If you'd rather not extend the template feature, feel free to close this.

I left `CHANGELOG.md` and the `updates/` script out since those depend on the release. Happy to add them if you want.


inline patch 1: doc/pg_partman.md, sql/functions/inherit_template_properties.sql, test/test-id-template-exclusion.sql (+96/-1 lines) download inline-1.patchdiff --git a/doc/pg_partman.md b/doc/pg_partman.md index 03405e3..4b809e2 100644 --- a/doc/pg_partman.md +++ b/doc/pg_partman.md @@ -80,6 +80,7 @@ For this extension, most of the attributes of the child partitions are all obtai | non-partition column primary key | | 14+ | | non-partition column unique index | | 14+ | | non-partition column unique index tablespace | | 14+ | +| exclusion constraint | | 14+ | | relation-specific options (autovac, etc) | | 14+ | unlogged table state* | | 14+ | | non-unique indexes | 14+ | | diff --git a/sql/functions/inherit_template_properties.sql b/sql/functions/inherit_template_properties.sql index 65bdf79..440e42c 100644 --- a/sql/functions/inherit_template_properties.sql +++ b/sql/functions/inherit_template_properties.sql @@ -14,6 +14,7 @@ v_child_schema text; v_child_tablename text; v_child_unlogged char; v_dupe_found boolean := false; +v_excl_list record; v_index_list record; v_parent_index_list record; v_parent_oid oid; @@ -30,7 +31,7 @@ v_toast_table_oid oid; BEGIN /* * Function to inherit the properties of the template table to newly created child tables. - * For PG14+, used to inherit non-partition-key unique indexes & primary keys, unlogged status, and relation options + * For PG14+, used to inherit non-partition-key unique indexes & primary keys, exclusion constraints, unlogged status, and relation options */ SELECT parent_table, template_table @@ -176,6 +177,46 @@ LOOP END LOOP; -- End index creation +-- Exclusion constraint creation +FOR v_excl_list IN + SELECT pg_get_constraintdef(con.oid) AS statement + , ts.spcname AS tablespace_name + FROM pg_catalog.pg_constraint con + JOIN pg_catalog.pg_class c ON con.conindid = c.oid + LEFT OUTER JOIN pg_catalog.pg_tablespace ts ON c.reltablespace = ts.oid + WHERE con.conrelid = v_template_oid + AND con.contype = 'x' + ORDER BY 1 +LOOP + -- Skip if the child already has it, for example when it was inherited from the parent + IF EXISTS ( + SELECT 1 + FROM pg_catalog.pg_constraint child_con + JOIN pg_catalog.pg_class c ON child_con.conrelid = c.oid + JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid + WHERE n.nspname = v_child_schema + AND c.relname = v_child_tablename + AND child_con.contype = 'x' + AND pg_get_constraintdef(child_con.oid) = v_excl_list.statement + ) THEN + RAISE DEBUG 'inherit_template_properties: Ignoring duplicate exclusion constraint on template table: %', v_excl_list.statement; + CONTINUE; + END IF; + + v_sql := format('ALTER TABLE %I.%I ADD %s', v_child_schema, v_child_tablename, v_excl_list.statement); + IF v_excl_list.tablespace_name IS NOT NULL THEN + IF (ARRAY_length(regexp_matches(v_sql, ' WHERE ', 'i'), 1) > 0) THEN + v_sql := regexp_replace(v_sql, ' WHERE ', format(' USING INDEX TABLESPACE %I WHERE ', v_excl_list.tablespace_name), 'i'); + ELSE + v_sql := v_sql || format(' USING INDEX TABLESPACE %I', v_excl_list.tablespace_name); + END IF; + END IF; + + RAISE DEBUG 'inherit_template_properties: Create exclusion constraint: %', v_sql; + EXECUTE v_sql; +END LOOP; +-- End exclusion constraint creation + /* UNLOGGED status. As of PG12, the unlogged/logged status of a parent table cannot be changed via an ALTER TABLE in order to affect its children. diff --git a/test/test-id-template-exclusion.sql b/test/test-id-template-exclusion.sql new file mode 100644 index 0000000..78f5800 --- /dev/null +++ b/test/test-id-template-exclusion.sql @@ -0,0 +1,53 @@ +-- ########## ID TEMPLATE EXCLUSION CONSTRAINT TESTS ########## +-- Additional tests: + -- partition key is an expression, so primary key and exclusion constraint can only be set on the template table + -- exclusion constraint on the template table is created on initial children, the default and children made by maintenance + -- exclusion constraint is enforced on the child tables + +\set ON_ERROR_ROLLBACK 1 +\set ON_ERROR_STOP true + +BEGIN; +SELECT set_config('search_path','partman, public',false); + +SELECT plan(14); +CREATE SCHEMA partman_test; + +CREATE TABLE partman_test.id_taptest_table + (col1 text NOT NULL + , col2 int4range NOT NULL CHECK (NOT isempty(col2)) + , col3 int4 GENERATED ALWAYS AS (upper(col2)) STORED) + PARTITION BY RANGE (upper(col2)); + +CREATE TABLE partman_test.template_id_taptest_table + (LIKE partman_test.id_taptest_table INCLUDING ALL + , PRIMARY KEY (col1, col2) + , EXCLUDE USING gist (col2 WITH &&)); + +SELECT create_partition('partman_test.id_taptest_table', 'col3', '30', p_premake := 2, p_control_not_null := false, p_template_table := 'partman_test.template_id_taptest_table'); + +SELECT has_table('partman_test', 'id_taptest_table_p0', 'Check id_taptest_table_p0 exists'); +SELECT has_table('partman_test', 'id_taptest_table_p30', 'Check id_taptest_table_p30 exists'); +SELECT has_table('partman_test', 'id_taptest_table_p60', 'Check id_taptest_table_p60 exists'); +SELECT has_table('partman_test', 'id_taptest_table_default', 'Check id_taptest_table_default exists'); + +SELECT col_is_pk('partman_test', 'id_taptest_table_p0', ARRAY['col1','col2'], 'Check for primary key in id_taptest_table_p0'); + +SELECT is((SELECT count(*)::int FROM pg_catalog.pg_constraint WHERE conrelid = 'partman_test.id_taptest_table_p0'::regclass AND contype = 'x'), 1, 'Check for exclusion constraint in id_taptest_table_p0'); +SELECT is((SELECT count(*)::int FROM pg_catalog.pg_constraint WHERE conrelid = 'partman_test.id_taptest_table_p30'::regclass AND contype = 'x'), 1, 'Check for exclusion constraint in id_taptest_table_p30'); +SELECT is((SELECT count(*)::int FROM pg_catalog.pg_constraint WHERE conrelid = 'partman_test.id_taptest_table_p60'::regclass AND contype = 'x'), 1, 'Check for exclusion constraint in id_taptest_table_p60'); +SELECT is((SELECT count(*)::int FROM pg_catalog.pg_constraint WHERE conrelid = 'partman_test.id_taptest_table_default'::regclass AND contype = 'x'), 1, 'Check for exclusion constraint in id_taptest_table_default'); + +INSERT INTO partman_test.id_taptest_table (col1, col2) VALUES ('stuff', int4range(1, 10)); +SELECT throws_ok($$INSERT INTO partman_test.id_taptest_table (col1, col2) VALUES ('other stuff', int4range(5, 15))$$, '23P01', NULL, 'Check that exclusion constraint is enforced in id_taptest_table_p0'); + +INSERT INTO partman_test.id_taptest_table (col1, col2) VALUES ('stuff', int4range(61, 70)); +SELECT run_maintenance(); + +SELECT has_table('partman_test', 'id_taptest_table_p90', 'Check id_taptest_table_p90 exists'); +SELECT has_table('partman_test', 'id_taptest_table_p120', 'Check id_taptest_table_p120 exists'); +SELECT is((SELECT count(*)::int FROM pg_catalog.pg_constraint WHERE conrelid = 'partman_test.id_taptest_table_p90'::regclass AND contype = 'x'), 1, 'Check for exclusion constraint in id_taptest_table_p90'); +SELECT is((SELECT count(*)::int FROM pg_catalog.pg_constraint WHERE conrelid = 'partman_test.id_taptest_table_p120'::regclass AND contype = 'x'), 1, 'Check for exclusion constraint in id_taptest_table_p120'); + +SELECT * FROM finish(); +ROLLBACK;
^ permalink raw [nested|flat] 4+ messages in thread

* Re: [pgpartman/pg_partman] PR #886: Copy exclusion constraints from the template table to child tables
@ 2026-11-09 19:49  "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  2 siblings, 0 replies; 4+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-09 19:49 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 applied label "pending review".

^ permalink  raw  [nested|flat] 4+ messages in thread

* Re: [pgpartman/pg_partman] PR #886: Copy exclusion constraints from the template table to child tables
@ 2026-11-09 19:49  "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  2 siblings, 0 replies; 4+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-09 19:49 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 applied label "feature request".

^ permalink  raw  [nested|flat] 4+ messages in thread

* Re: [pgpartman/pg_partman] PR #886: Copy exclusion constraints from the template table to child tables
@ 2026-11-09 19:49  "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  2 siblings, 0 replies; 4+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-09 19:49 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 set milestone to "Future".

^ permalink  raw  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2026-11-09 19:49 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-09 19:22 [pgpartman/pg_partman] PR #886: Copy exclusion constraints from the template table to child tables "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-09 19:49 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-09 19:49 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-09 19:49 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox