pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
7+ messages / 2 participants
[nested] [flat]

* [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-06 09:50 "onno-vos-dev (@onno-vos-dev)" <noreply+onno-vos-dev@github.com>
  0 siblings, 0 replies; 7+ messages in thread

From: onno-vos-dev (@onno-vos-dev) @ 2026-11-06 09:50 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

## Request for feedback
This is a request for feedback and consider this an initial proposal for discussion. It will require a bit of polishing and some config values to be finalised 👍 We'll handle this if we can agree that this is a feature that we'd like in `pg_partman`. 

## Problem description
At Klarna we prefer to perform any and all DDL as lock-free as possible. One of the challenges we see with `pg_partman` today is that indexes are by default ATTACHED to the parent partition. This has the consequence that these indexes cannot be dropped with `CONCURRENTLY` but can only be dropped non-concurrently which takes an `ACCESS EXCLUSIVE` lock. This appears to be not the fault of `pg_partman` but rather how native Postgres behaves when there's an index on the parent table. There's also no functionality today to `DETACH` an index from the parent so there's literally no way to work around this issue. 

There's an alternative approach to basically not create indexes on the parent but rather have some `pg_cron` type of job create them on the newly created partitions based on a trigger but this feels convoluted and quite clunky in our view. 

## Proposal
Add the ability to create indexes on child tables only so that they can be dropped `CONCURRENTLY` when they're no longer needed or don't serve the intended purpose that was aimed for. 

There's effectively two options we can go with:
1. Create them based on the `template` table. As a consequence any index should always be added to the template so taht newly created partitions get it from the template and the child index can be created. 
2. Look up which existing indexes exist on partitions and build some form of `set` that would be applied to newly created partitions. 

In this PR, we've gone with option 1. If you'd prefer to go with option 2, we can close this PR and open a new one which implements that option instead.

## To verify (sorry for the lengthy part here...):
To verify, run below with and without the patch in a local (Dockerized) Postgres: `psql -f <file with this sql>` 
This would be how one would typically do this today to ensure new partitions get the newly added indexes:

```sql
-- Create schemas and install pg_partman extension
DROP SCHEMA IF EXISTS demo_opt6 CASCADE;
CREATE SCHEMA demo_opt6;
SET search_path = demo_opt6;

DROP SCHEMA IF EXISTS partman CASCADE;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Create a dummy table with a CHECK constraint and an index on `payload`
CREATE TABLE events (id bigint, created_at timestamp with time zone NOT NULL, payload text, PRIMARY KEY (id, created_at))
  PARTITION BY RANGE (created_at);
ALTER TABLE events ADD CHECK (left(payload, 3) = 'row' AND payload IS NOT NULL);
CREATE INDEX events_payload_idx ON ONLY events (payload);

CREATE TABLE partman.events_template (LIKE demo_opt6.events);

SELECT partman.create_parent('demo_opt6.events',
                             p_control := 'created_at',
                             p_type := 'range',
                             p_interval := '1 minute',
                             p_template_table := 'partman.events_template',
                             p_premake := 4,
                             p_start_partition := CURRENT_TIMESTAMP::text,
                             p_default_table := false);

UPDATE partman.part_config
SET infinite_time_partitions = true,
    retention = '30 minutes',
    retention_keep_table=false
WHERE parent_table = 'demo_opt6.events';

-- Create index on `created_at` on the template
CREATE INDEX events_created_at_idx ON ONLY events (created_at);

-- Now let's create some indexes on existing partitions on `created_at`
\set AUTOCOMMIT on
\set ON_ERROR_STOP on
SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %I.%I (%I)',
    c.relname || '_' || v.column_name || '_idx',
    n.nspname,
    c.relname,
    v.column_name
)
FROM pg_partition_tree('demo_opt6.events'::regclass) AS t
JOIN pg_class AS c ON c.oid = t.relid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
CROSS JOIN (VALUES ('created_at')) AS v(column_name)
WHERE t.isleaf
ORDER BY n.nspname, c.relname, v.column_name
\gexec

-- Sleep so that we can call maintenance to trigger new partitions to be created
SELECT pg_sleep(65);

CALL partman.run_maintenance_proc();

-- Verify that SOME of the indexes are ATTACHED
WITH check_attached AS (
  SELECT c.relname AS idx, i.indisvalid AS valid, (SELECT inhparent::regclass FROM pg_inherits WHERE inhrelid = i.indexrelid) AS attached_to
  FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
  WHERE c.relnamespace = 'demo_opt6'::regnamespace AND c.relname LIKE '%idx' ORDER BY 1
)

SELECT * FROM check_attached WHERE attached_to IS NOT NULL;
```

Result without the patch:

| idx | valid | attached_to |
|---|---|---|
| events_p20261106_090900_payload_idx | t | events_payload_idx |
| ... | t | events_payload_idx |
| events_p20261106_094200_payload_idx | t | events_payload_idx |
| events_p20261106_094300_created_at_idx | t | events_created_at_idx |
| events_p20261106_094300_payload_idx | t | events_payload_idx |

All are attached 😢 

With a patched flow, the process would look like this:
```sql
-- Create schemas and install pg_partman extension
DROP SCHEMA IF EXISTS demo_opt6 CASCADE;
CREATE SCHEMA demo_opt6;
SET search_path = demo_opt6;

DROP SCHEMA IF EXISTS partman CASCADE;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Create a dummy table with a CHECK constraint and an index on `payload`
CREATE TABLE events (id bigint, created_at timestamp with time zone NOT NULL, payload text, PRIMARY KEY (id, created_at))
  PARTITION BY RANGE (created_at);
ALTER TABLE events ADD CHECK (left(payload, 3) = 'row' AND payload IS NOT NULL);

CREATE TABLE partman.events_template (LIKE demo_opt6.events);
CREATE INDEX events_template_payload_idx ON partman.events_template (payload);

SELECT partman.create_parent('demo_opt6.events',
                             p_control := 'created_at',
                             p_type := 'range',
                             p_interval := '1 minute',
                             p_template_table := 'partman.events_template',
                             p_premake := 4,
                             p_start_partition := CURRENT_TIMESTAMP::text,
                             p_default_table := false);

UPDATE partman.part_config
SET infinite_time_partitions = true,
    retention = '30 minutes',
    retention_keep_table=false
WHERE parent_table = 'demo_opt6.events';

-- Now let's create some indexes on existing partitions on `payload`
\set AUTOCOMMIT on
\set ON_ERROR_STOP on
SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %I.%I (%I)',
    c.relname || '_' || v.column_name || '_idx',
    n.nspname,
    c.relname,
    v.column_name
)
FROM pg_partition_tree('demo_opt6.events'::regclass) AS t
JOIN pg_class AS c ON c.oid = t.relid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
CROSS JOIN (VALUES ('payload')) AS v(column_name)
WHERE t.isleaf
ORDER BY n.nspname, c.relname, v.column_name
\gexec

-- Create index on `created_at` on the template
CREATE INDEX events_template_created_at_idx ON partman.events_template (created_at);

-- Now let's create some indexes on existing partitions on `created_at`
\set AUTOCOMMIT on
\set ON_ERROR_STOP on
SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %I.%I (%I)',
    c.relname || '_' || v.column_name || '_idx',
    n.nspname,
    c.relname,
    v.column_name
)
FROM pg_partition_tree('demo_opt6.events'::regclass) AS t
JOIN pg_class AS c ON c.oid = t.relid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
CROSS JOIN (VALUES ('created_at')) AS v(column_name)
WHERE t.isleaf
ORDER BY n.nspname, c.relname, v.column_name
\gexec

-- Sleep so that we can call maintenance to trigger new partitions to be created
SELECT pg_sleep(65);

CALL partman.run_maintenance_proc();

-- Verify that none of the indexes are ATTACHED
WITH check AS (
  SELECT c.relname AS idx, i.indisvalid AS valid, (SELECT inhparent::regclass FROM pg_inherits WHERE inhrelid = i.indexrelid) AS attached_to
  FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
  WHERE c.relnamespace = 'demo_opt6'::regnamespace AND c.relname LIKE '%idx' ORDER BY 1
)

SELECT * FROM check WHERE attached_to IS NOT NULL;
```

Result with the patch:
0 rows. None are attached 🎉 

inline patch 1: .github/workflows/ci.yml, CHANGELOG.md, doc/pg_partman.md and 5 more (+201/-19 lines) download inline-1.patchdiff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml index 7c152f9..ac5fada 100644 --- a/.github/workflows/ci.yml +++ b/.github/workflows/ci.yml @@ -19,3 +19,4 @@ jobs: - run: make install - run: psql -U postgres -c 'CREATE SCHEMA partman; CREATE EXTENSION pg_partman SCHEMA partman; CREATE EXTENSION pgtap' - run: pg_prove --username postgres --ext .sql --comments --verbose --failures test + - run: psql -U postgres -v ON_ERROR_STOP=1 -f test/test_manual/test-template-indexes-concurrently.sql diff --git a/CHANGELOG.md b/CHANGELOG.md index c5f3e73..8b51572 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -12,6 +12,7 @@ DOCUMENTATION NEW FEATURES ------------ + - Copy non-unique indexes from the template table to new child tables. When no matching parent index exists, these child indexes remain independent and can be dropped concurrently. - Allow retention system to properly handle dropping child tables when the partition table is referenced by another table via a foreign key. Child tables must be detached first before dropping, so a new flag in the `part_config` table has been added: `detach_before_drop`. Set this value to true when using retention on a partition set referenced by another table. Note that all data in the referencing table must still be removed from that table first before the child table can be detached or dropped. - Added a new column for allowing Row Level Security (RLS) management on the `part_config` and `part_config_sub` tables: maintenance_role. See documentation for examples of how to set row level security policies on the configuration tables to restrict access to performing maintenance operations on partition sets owned by other roles. - Inherit per column statistic target from the parent table diff --git a/doc/pg_partman.md b/doc/pg_partman.md index 03405e3..19a0983 100644 --- a/doc/pg_partman.md +++ b/doc/pg_partman.md @@ -82,12 +82,18 @@ For this extension, most of the attributes of the child partitions are all obtai | non-partition column unique index tablespace | | 14+ | | relation-specific options (autovac, etc) | | 14+ | unlogged table state* | | 14+ | -| non-unique indexes | 14+ | | +| non-unique indexes | 14+ | 14+ | | privileges/ownership | 14+ | | | GENERATED ALWAYS AS IDENTITY column | 17+ | | If a property is managed via the template table, it likely will not be retroactively applied to all existing child tables if that property is changed. It will apply to any newly created children, but will have to be manually applied to any existing children. +To keep non-unique indexes independent of the parent's partitioned indexes, create them on the configured template table and leave matching indexes off the parent. pg_partman copies valid template indexes to new child tables, including the default partition. It skips indexes already supplied by the parent. Exclusion constraints are not copied from the template. + +New non-default partitions receive their template indexes before attachment. These builds use ordinary `CREATE INDEX` on the empty tables. PostgreSQL does not allow `CREATE INDEX CONCURRENTLY` inside pg_partman's partition-creation functions. For existing populated partitions, run `CREATE INDEX CONCURRENTLY` separately on each child, outside a transaction block. + +An independent child index can be removed with `DROP INDEX CONCURRENTLY`, outside a transaction block, provided it does not support a constraint. PostgreSQL attaches matching child indexes when a parent index exists, even if the child index came from the template. Those attached indexes cannot be dropped independently. This feature does not detach existing indexes. Remove an index from the template to stop copying it to future partitions; existing children keep their indexes. + Privileges & ownership are NOT inherited by default. If enabled by pg_partman, note that this inheritance is only at child table creation and isn't automatically retroactive when changed (see `reapply_privileges()`). Unless you need direct access to the child tables, this should not be needed. You can set the `inherit_privileges` option if this is needed (see config table information below). If you are using the IDENTITY feature for sequences, the automatic generation of new sequence values using this feature is only supported when data is inserted through the parent table, not directly into the children. diff --git a/sql/functions/inherit_template_properties.sql b/sql/functions/inherit_template_properties.sql index 65bdf79..5839a2f 100644 --- a/sql/functions/inherit_template_properties.sql +++ b/sql/functions/inherit_template_properties.sql @@ -30,7 +30,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 + * Inherit indexes, primary keys, unlogged status, and relation options. */ SELECT parent_table, template_table @@ -85,7 +85,7 @@ AND c.relname = v_template_tablename; END IF; --- Index creation (Only for unique, non-partition key indexes) +-- Copy template indexes that are not supplied by the parent. FOR v_index_list IN SELECT array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement @@ -104,7 +104,7 @@ FOR v_index_list IN LEFT OUTER JOIN pg_catalog.pg_tablespace ts ON c.reltablespace = ts.oid WHERE i.indrelid = v_template_oid AND i.indisvalid - AND (i.indisprimary OR i.indisunique) + AND NOT i.indisexclusion ORDER BY 1 LOOP v_dupe_found := false; @@ -113,6 +113,7 @@ LOOP SELECT array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement , i.indisprimary + , i.indisunique , ( SELECT array_agg( a.attname ORDER by x.r ) FROM pg_catalog.pg_attribute a JOIN ( SELECT k, row_number() over () as r @@ -133,8 +134,9 @@ LOOP END IF; END IF; - IF v_parent_index_list.statement = v_index_list.statement THEN - RAISE DEBUG 'inherit_template_properties: Ignoring duplicate unique index on template table: %', v_index_list.statement; + IF v_parent_index_list.statement = v_index_list.statement + AND v_parent_index_list.indisunique = v_index_list.indisunique THEN + RAISE DEBUG 'inherit_template_properties: Ignoring duplicate index on template table: %', v_index_list.statement; v_dupe_found := true; CONTINUE; -- only continue within this nested loop END IF; @@ -156,9 +158,13 @@ LOOP END IF; RAISE DEBUG 'inherit_template_properties: Create pk: %', v_sql; EXECUTE v_sql; - ELSIF v_index_list.indisunique THEN + ELSE -- statement column should be just the portion of the index definition that defines what it actually is - v_sql := format('CREATE UNIQUE INDEX ON %I.%I %s', v_child_schema, v_child_tablename, v_index_list.statement); + v_sql := format('CREATE %sINDEX ON %I.%I %s' + , CASE WHEN v_index_list.indisunique THEN 'UNIQUE ' ELSE '' END + , v_child_schema + , v_child_tablename + , v_index_list.statement); IF v_index_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(' TABLESPACE %I WHERE ', v_index_list.tablespace_name), 'i'); @@ -169,8 +175,6 @@ LOOP RAISE DEBUG 'inherit_template_properties: Create index: %', v_sql; EXECUTE v_sql; - ELSE - RAISE EXCEPTION 'inherit_template_properties: Unexpected code path in unique index creation. Please report the steps that lead to this error to extension maintainers.'; END IF; END LOOP; diff --git a/test/README_test.md b/test/README_test.md index 7ad0a37..f657014 100644 --- a/test/README_test.md +++ b/test/README_test.md @@ -38,4 +38,10 @@ Tests for the Background Worker can be found in the test/test_bgw folder. Please Tests for procedures are in their own folders. These must be run in stages with manual commands between them since pgTap cannot handle distinct commits within a single test run. +The template-index concurrent-drop test runs in autocommit mode because `DROP INDEX CONCURRENTLY` cannot run inside a transaction block. CI runs it separately after the pgTAP suite. To run it locally: + +```sh +psql -v ON_ERROR_STOP=1 -f test/test_manual/test-template-indexes-concurrently.sql +``` + Tests for the reindexing script are in development and will return once the reindexing feature has been updated. They can be found in the test/test_reindex folder. These tests cannot just be run all at once and are not run within rolled back transactions. They must be run in order, one at a time, and there are explicit instructions at the end of each test for what to do next. diff --git a/test/test-template-indexes.sql b/test/test-template-indexes.sql new file mode 100644 index 0000000..5a7e7ad --- /dev/null +++ b/test/test-template-indexes.sql @@ -0,0 +1,108 @@ +-- Template indexes stay independent unless the parent supplies a matching index. +\set ON_ERROR_STOP true + +BEGIN; +SELECT set_config('search_path', 'partman, public', false); +SELECT plan(15); +CREATE SCHEMA partman_test; + +CREATE TABLE partman_test.template_index_parent ( + id bigint NOT NULL UNIQUE, payload text NOT NULL, extra integer, tags text[], span int4range +) PARTITION BY RANGE (id); +CREATE INDEX ON partman_test.template_index_parent (extra); +CREATE TABLE partman_test.template_indexes (LIKE partman_test.template_index_parent); +ALTER TABLE partman_test.template_indexes ADD PRIMARY KEY (payload); +CREATE INDEX ON partman_test.template_indexes (extra); +CREATE UNIQUE INDEX ON partman_test.template_indexes (extra); +CREATE INDEX ON partman_test.template_indexes (id); +CREATE INDEX ON partman_test.template_indexes (lower(payload) text_pattern_ops) + INCLUDE (extra) WITH (fillfactor = 70) TABLESPACE pg_default WHERE extra > 0; +CREATE INDEX ON partman_test.template_indexes USING gin (tags) WITH (fastupdate = off); +ALTER TABLE partman_test.template_indexes ADD EXCLUDE USING gist (span WITH &&); +CREATE INDEX template_invalid ON partman_test.template_indexes (extra, payload); +UPDATE pg_index SET indisvalid = false +WHERE indexrelid = 'partman_test.template_invalid'::regclass; + +SELECT create_partition('partman_test.template_index_parent', 'id', '10', + p_start_partition := '0', p_premake := 1, + p_template_table := 'partman_test.template_indexes', p_jobmon := false); + +SELECT is((SELECT count(*)::integer FROM pg_index + WHERE indrelid = 'partman_test.template_index_parent_p0'::regclass), 7, + 'Range partition has template indexes and no duplicate parent indexes'); +SELECT is((SELECT count(*)::integer FROM pg_index + WHERE indrelid = 'partman_test.template_index_parent_default'::regclass), 7, + 'Default partition has template indexes and no duplicate parent indexes'); +SELECT is((SELECT count(*)::integer FROM pg_index i JOIN pg_inherits h ON h.inhrelid = i.indexrelid + WHERE i.indrelid = 'partman_test.template_index_parent_p0'::regclass), 2, + 'Only the two parent indexes are attached'); +SELECT is((SELECT count(*)::integer FROM pg_index + WHERE indrelid = 'partman_test.template_index_parent_p0'::regclass AND indisunique), 3, + 'Template primary key and unique index survive alongside the parent unique index'); + +CREATE TEMP VIEW expected_template_indexes AS +SELECT indisunique, substring(pg_get_indexdef(indexrelid) FROM ' USING .*') AS definition +FROM pg_index WHERE indrelid = 'partman_test.template_indexes'::regclass + AND indisvalid AND NOT indisexclusion +UNION +SELECT indisunique, substring(pg_get_indexdef(indexrelid) FROM ' USING .*') +FROM pg_index WHERE indrelid = 'partman_test.template_index_parent'::regclass; + +SELECT results_eq($$SELECT indisunique, substring(pg_get_indexdef(indexrelid) FROM ' USING .*') + FROM pg_index WHERE indrelid = 'partman_test.template_index_parent_p0'::regclass + ORDER BY 1, 2$$, $$SELECT * FROM expected_template_indexes ORDER BY 1, 2$$, + 'Index methods, expressions, opclasses, INCLUDE, predicates and storage options are preserved'); +SELECT results_eq($$SELECT indisunique, substring(pg_get_indexdef(indexrelid) FROM ' USING .*') + FROM pg_index WHERE indrelid = 'partman_test.template_index_parent_default'::regclass + ORDER BY 1, 2$$, $$SELECT * FROM expected_template_indexes ORDER BY 1, 2$$, + 'Default partition preserves index definitions'); + +INSERT INTO partman_test.template_index_parent (id, payload, extra) VALUES (19, 'one', 1); +SELECT run_maintenance('partman_test.template_index_parent', p_jobmon := false); +SELECT has_table('partman_test', 'template_index_parent_p20', 'Maintenance creates a partition'); +SELECT results_eq($$SELECT indisunique, substring(pg_get_indexdef(indexrelid) FROM ' USING .*') + FROM pg_index WHERE indrelid = 'partman_test.template_index_parent_p20'::regclass + ORDER BY 1, 2$$, $$SELECT * FROM expected_template_indexes ORDER BY 1, 2$$, + 'Maintenance copies template indexes'); + +CREATE TABLE partman_test.template_list_parent (id bigint NOT NULL, payload text) + PARTITION BY LIST (id); +CREATE TABLE partman_test.template_list (LIKE partman_test.template_list_parent); +CREATE INDEX ON partman_test.template_list (payload); +SELECT create_partition('partman_test.template_list_parent', 'id', '1', 'list', + p_start_partition := '0', p_premake := 1, + p_template_table := 'partman_test.template_list', p_jobmon := false); +SELECT is_indexed('partman_test', 'template_list_parent_p0', 'payload', + 'List partition copies non-unique template index'); +SELECT is((SELECT count(*)::integer FROM pg_index i JOIN pg_inherits h ON h.inhrelid = i.indexrelid + WHERE i.indrelid = 'partman_test.template_list_parent_p0'::regclass), 0, + 'List partition template index is independent'); + +CREATE TABLE partman_test.template_time_parent (created_at timestamptz NOT NULL, payload text) + PARTITION BY RANGE (created_at); +CREATE TABLE partman_test.template_time (LIKE partman_test.template_time_parent); +CREATE INDEX ON partman_test.template_time (payload); +SELECT create_partition('partman_test.template_time_parent', 'created_at', '1 day', + p_premake := 1, p_template_table := 'partman_test.template_time', p_jobmon := false); +SELECT ok(NOT EXISTS ( + SELECT FROM show_partitions('partman_test.template_time_parent', p_include_default := true) p + WHERE NOT EXISTS (SELECT FROM pg_index i + WHERE i.indrelid = format('%I.%I', p.partition_schemaname, p.partition_tablename)::regclass) +), 'Every time partition, including the default, copies the template index'); +SELECT is((SELECT count(*)::integer FROM pg_index i JOIN pg_inherits h ON h.inhrelid = i.indexrelid + WHERE i.indrelid = 'partman_test.template_time_parent_default'::regclass), 0, + 'Default time partition template index is independent'); + +-- A partitioned child gets its indexes from its own leaf partitions. +CREATE TABLE partman_test.template_subparent (id bigint NOT NULL) PARTITION BY RANGE (id); +SELECT is(inherit_template_properties('partman_test.template_list_parent', 'partman_test', + 'template_subparent'), false, 'Template properties are skipped on subpartition parents'); +SELECT is((SELECT count(*)::integer FROM pg_index + WHERE indrelid = 'partman_test.template_subparent'::regclass), 0, + 'Subpartition parent has no template indexes'); +SELECT is((SELECT count(*)::integer FROM pg_index + WHERE indrelid = 'partman_test.template_index_parent'::regclass), 2, + 'Template index copying does not add indexes to the parent'); + +SELECT * FROM finish(); +ROLLBACK; diff --git a/test/test_manual/test-template-indexes-concurrently.sql b/test/test_manual/test-template-indexes-concurrently.sql new file mode 100644 index 0000000..59c2f17 --- /dev/null +++ b/test/test_manual/test-template-indexes-concurrently.sql @@ -0,0 +1,52 @@ +-- Run with psql in autocommit mode after installing pg_partman in schema partman. +-- Concurrent DDL cannot run inside the rolled-back pgTAP tests. +\set ON_ERROR_STOP true + +CREATE SCHEMA partman_template_concurrent_test; +CREATE TABLE partman_template_concurrent_test.parent (id bigint NOT NULL, payload text) + PARTITION BY RANGE (id); +CREATE TABLE partman_template_concurrent_test.template + (LIKE partman_template_concurrent_test.parent); +CREATE INDEX ON partman_template_concurrent_test.template (payload); +SELECT partman.create_partition('partman_template_concurrent_test.parent', 'id', '10', + p_start_partition := '0', p_premake := 1, + p_template_table := 'partman_template_concurrent_test.template', p_jobmon := false); +INSERT INTO partman_template_concurrent_test.parent VALUES (1, 'range'), (100, 'default'); + +DO $$ +BEGIN + IF (SELECT count(*) FROM pg_index i JOIN pg_class c ON c.oid = i.indrelid + JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE n.nspname = 'partman_template_concurrent_test' AND c.relispartition) <> 3 THEN + RAISE EXCEPTION 'Missing template indexes'; + END IF; + IF EXISTS (SELECT FROM pg_inherits h JOIN pg_class c ON c.oid = h.inhrelid + JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE n.nspname = 'partman_template_concurrent_test' AND c.relkind = 'i') THEN + RAISE EXCEPTION 'Attached template indexes'; + END IF; +END +$$; + +SELECT format('DROP INDEX CONCURRENTLY %I.%I', n.nspname, idx.relname) +FROM pg_index i JOIN pg_class idx ON idx.oid = i.indexrelid +JOIN pg_class child ON child.oid = i.indrelid +JOIN pg_namespace n ON n.oid = idx.relnamespace +WHERE n.nspname = 'partman_template_concurrent_test' AND child.relispartition +\gexec + +DO $$ +BEGIN + IF EXISTS (SELECT FROM pg_index i JOIN pg_class c ON c.oid = i.indrelid + JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE n.nspname = 'partman_template_concurrent_test' AND c.relispartition) THEN + RAISE EXCEPTION 'Remaining child indexes'; + END IF; + IF (SELECT count(*) FROM partman_template_concurrent_test.parent) <> 2 THEN + RAISE EXCEPTION 'Missing partition data'; + END IF; +END +$$; + +SELECT partman.config_cleanup('partman_template_concurrent_test.parent'); +DROP SCHEMA partman_template_concurrent_test CASCADE; diff --git a/updates/pg_partman--5.4.3--5.5.0.sql b/updates/pg_partman--5.4.3--5.5.0.sql index 5af00b1..d8a6b9d 100644 --- a/updates/pg_partman--5.4.3--5.5.0.sql +++ b/updates/pg_partman--5.4.3--5.5.0.sql @@ -4066,7 +4066,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 + * Inherit indexes, primary keys, unlogged status, and relation options. */ SELECT parent_table, template_table @@ -4121,7 +4121,7 @@ AND c.relname = v_template_tablename; END IF; --- Index creation (Only for unique, non-partition key indexes) +-- Copy template indexes that are not supplied by the parent. FOR v_index_list IN SELECT array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement @@ -4140,7 +4140,7 @@ FOR v_index_list IN LEFT OUTER JOIN pg_catalog.pg_tablespace ts ON c.reltablespace = ts.oid WHERE i.indrelid = v_template_oid AND i.indisvalid - AND (i.indisprimary OR i.indisunique) + AND NOT i.indisexclusion ORDER BY 1 LOOP v_dupe_found := false; @@ -4149,6 +4149,7 @@ LOOP SELECT array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement , i.indisprimary + , i.indisunique , ( SELECT array_agg( a.attname ORDER by x.r ) FROM pg_catalog.pg_attribute a JOIN ( SELECT k, row_number() over () as r @@ -4169,8 +4170,9 @@ LOOP END IF; END IF; - IF v_parent_index_list.statement = v_index_list.statement THEN - RAISE DEBUG 'inherit_template_properties: Ignoring duplicate unique index on template table: %', v_index_list.statement; + IF v_parent_index_list.statement = v_index_list.statement + AND v_parent_index_list.indisunique = v_index_list.indisunique THEN + RAISE DEBUG 'inherit_template_properties: Ignoring duplicate index on template table: %', v_index_list.statement; v_dupe_found := true; CONTINUE; -- only continue within this nested loop END IF; @@ -4192,9 +4194,13 @@ LOOP END IF; RAISE DEBUG 'inherit_template_properties: Create pk: %', v_sql; EXECUTE v_sql; - ELSIF v_index_list.indisunique THEN + ELSE -- statement column should be just the portion of the index definition that defines what it actually is - v_sql := format('CREATE UNIQUE INDEX ON %I.%I %s', v_child_schema, v_child_tablename, v_index_list.statement); + v_sql := format('CREATE %sINDEX ON %I.%I %s' + , CASE WHEN v_index_list.indisunique THEN 'UNIQUE ' ELSE '' END + , v_child_schema + , v_child_tablename + , v_index_list.statement); IF v_index_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(' TABLESPACE %I WHERE ', v_index_list.tablespace_name), 'i'); @@ -4205,8 +4211,6 @@ LOOP RAISE DEBUG 'inherit_template_properties: Create index: %', v_sql; EXECUTE v_sql; - ELSE - RAISE EXCEPTION 'inherit_template_properties: Unexpected code path in unique index creation. Please report the steps that lead to this error to extension maintainers.'; END IF; END LOOP;
^ permalink raw [nested|flat] 7+ messages in thread

* Re: [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-06 09:52 ` "onno-vos-dev (@onno-vos-dev)" <noreply+onno-vos-dev@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: onno-vos-dev (@onno-vos-dev) @ 2026-11-06 09:52 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 Sorry for tagging you directly in a msg but I can't seem to request you as a reviewer? 🤔 

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

* Re: [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-06 16:45 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-06 16:45 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

(on updates/pg_partman--5.4.3--5.5.0.sql:4124)

Please do not apply changes to an existing update file. Any extension updates will need to go into their own new version file. 

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

* Re: [pgpartman/pg_partman] PR #884 (changes requested): [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-06 16:49 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  5 siblings, 0 replies; 7+ messages in thread

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



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

* Re: [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-06 16:55 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-06 16:55 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

Going by your description, you're saying that indexes should only be made on the template. But that is definitely not a design pattern I want. The code looks like it tries to compare to see if the template index exists on the parent and only includes it if it does. Is that the intent?

I would only want this if it could support either the parent or the template having the indexes, not only the template. And not cause issues if someone did it on both, or clearly throw errors and detect it when it happens. I'd tried doing this before but ran into issues trying to find a foolproof way of deduping any possible index on the template vs the parent. So I went with only supporting primary/unique indexes on the template and the remaining ones on the parent table.

If that is the intent of this PR, I'll have to take time and check more carefully to see if that is what this is allowing.

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

* Re: [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-17 18:01 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-17 18:01 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 assigned @keithf4.

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

* Re: [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent
@ 2026-11-17 18:01 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-17 18:01 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 applied label "requires submitter response".

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


end of thread, other threads:[~2026-11-17 18:01 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-06 09:50 [pgpartman/pg_partman] PR #884: [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent "onno-vos-dev (@onno-vos-dev)" <noreply+onno-vos-dev@github.com>
2026-11-06 09:52 ` "onno-vos-dev (@onno-vos-dev)" <noreply+onno-vos-dev@github.com>
2026-11-06 16:45 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-06 16:49 ` Re: [pgpartman/pg_partman] PR #884 (changes requested): [Request for Feedback] feature: Allow indexes to be created without attaching them to a parent "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-06 16:55 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-17 18:01 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-17 18:01 ` "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