pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
From: avinashvallarapu (@avinashvallarapu) <noreply+avinashvallarapu@github.com>
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Subject: [pgpartman/pg_partman] PR #880: Mark uuid7 time encoder/decoder IMMUTABLE so UUIDv7 sets can prune
Date: Sun, 04 Oct 2026 21:52:05 +0000
Message-ID: <gh-pgpartman-pg_partman-880@github.com> (raw)

Fixes #873.

`uuid7_time_encoder()` and `uuid7_time_decoder()` had no volatility set, so they were VOLATILE. The planner can't fold them, and a range query on a UUIDv7 set filtered through the encoder scans every child. With the repro from the issue (17 children) the one-month query scanned all 17. With both functions IMMUTABLE it scans one, and the encoder calls show up as uuid constants in the plan.

IMMUTABLE is safe for both. The encoder only uses `EXTRACT(EPOCH FROM ts)` and zero-fills the rest of the uuid, so nothing is random and the session TimeZone doesn't change the result (I checked UTC and Asia/Kolkata). The decoder only uses `substr`, the bit cast and `to_timestamp(double precision)`.

- `util_uuid7_time.sql`: `IMMUTABLE PARALLEL SAFE` on both
- `pg_partman--5.5.0--5.5.1.sql`: `ALTER FUNCTION` for existing installs
- changelog line under 5.5.1
- new `test/test-uuid-pruning.sql`

The test sets TimeZone to UTC because child boundaries are built in the session time zone and my test box isn't on UTC. It also turns off index and bitmap scans while counting children in the plan, otherwise a bitmap scan lists the same child twice.

Ran it on PG 18.6 with pgTAP 1.3.4. The new test passes, and fails 6 of 10 against the old function definitions. The rest of `test/*.sql` gives the same result as before: `test-time-daily.sql` test 4 fails with or without this change. I also checked that a 5.5.0 install updated with `ALTER EXTENSION pg_partman UPDATE TO '5.5.1'` ends up with the same definitions as a fresh install.

One behaviour change worth knowing about: a call that errors, like `uuid7_time_encoder('infinity')`, now fails while the query is planned, so it errors even against an empty table. Before, it only failed once there was a row to compare. Same error either way, and infinity can't be encoded anyway. Everything else I tried (prepared statements, untaken CASE branches, per-row decoder calls, pre-1970 input) behaves the same before and after.

#879 touches the same update file and changelog section, so whichever goes in second needs a small rebase. I can do it if this one is second.

Thanks to HexaCluster for supporting the time spent on this fix.


inline patch 1: CHANGELOG.md, sql/functions/util_uuid7_time.sql, test/test-uuid-pruning.sql and 1 more (+112/-2 lines) download inline-1.patchdiff --git a/CHANGELOG.md b/CHANGELOG.md index a0766e4..3c79399 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -4,9 +4,11 @@ BUG FIXES --------- - Fix variable assignment misalignment in partition_data_time() when using the p_lock_wait parameter. (Github PR #871 & #878) - Fix inherit_template_properties() adding a template table's INCLUDE index columns as real key columns on child tables instead of ignoring them. (Github Issue #674) + - Mark uuid7_time_encoder() and uuid7_time_decoder() IMMUTABLE and PARALLEL SAFE. As VOLATILE functions they blocked partition pruning, so queries on a UUIDv7 partition set filtered with uuid7_time_encoder() scanned every child table. (Github Issue #873) - Fix partition_data_time() and partition_data_proc() leaving rows on the first day of a child table's range in the default, and taking rows on the first day of the next range, when the control column is a date. The rows are compared in the control column's own type again instead of as text. (Github Issue #856) + 5.5.0 ===== BREAKING CHANGES diff --git a/sql/functions/util_uuid7_time.sql b/sql/functions/util_uuid7_time.sql index 68e5c6a..9c34909 100644 --- a/sql/functions/util_uuid7_time.sql +++ b/sql/functions/util_uuid7_time.sql @@ -2,10 +2,14 @@ * Convenience helper functions to support time based partitioning for UUIDv7 columns. * Set the following functions as p_time_encoder, p_time_decoder params for time based * partitioning of UUIDv7 columns + * + * Both are IMMUTABLE so calls with constant arguments get folded at plan time, which + * partition pruning needs. Neither depends on session settings (timestamptz is an + * absolute instant). */ CREATE FUNCTION @extschema@.uuid7_time_encoder(ts TIMESTAMPTZ) RETURNS UUID - LANGUAGE plpgsql + LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE SET search_path = @extschema@, pg_catalog, pg_temp AS $$ DECLARE @@ -24,7 +28,7 @@ $$; -- Currently time decoder function must take a text parameter. See if this can be more flexible in the future CREATE FUNCTION @extschema@.uuid7_time_decoder(uuidv7 TEXT) RETURNS TIMESTAMPTZ - LANGUAGE plpgsql + LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE SET search_path = @extschema@, pg_catalog, pg_temp AS $$ DECLARE diff --git a/test/test-uuid-pruning.sql b/test/test-uuid-pruning.sql new file mode 100644 index 0000000..8fcf175 --- /dev/null +++ b/test/test-uuid-pruning.sql @@ -0,0 +1,99 @@ +-- ########## UUID7 ENCODER/DECODER PRUNING TESTS ########## +-- Github Issue #873 +-- A query filtered with uuid7_time_encoder() should only scan the matching child table. + +\set ON_ERROR_ROLLBACK 1 +\set ON_ERROR_STOP true + +BEGIN; +SELECT set_config('search_path','partman, public',false); +-- Child boundaries are built in the session time zone and the queries below use UTC bounds +SET TimeZone = 'UTC'; + +SELECT plan(10); + +CREATE SCHEMA partman_test; + +SELECT volatility_is('partman', 'uuid7_time_encoder', ARRAY['timestamp with time zone'], 'immutable', 'Check uuid7_time_encoder() is immutable'); +SELECT volatility_is('partman', 'uuid7_time_decoder', ARRAY['text'], 'immutable', 'Check uuid7_time_decoder() is immutable'); +SELECT is( + (SELECT array_agg(proparallel ORDER BY proname) FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace + WHERE n.nspname = 'partman' AND proname IN ('uuid7_time_encoder', 'uuid7_time_decoder')) + , ARRAY['s','s']::"char"[] + , 'Check uuid7 helper functions are parallel safe'); + +CREATE TABLE partman_test.uuid_pruning_table + (col1 uuid NOT NULL + , col2 text + , CONSTRAINT uuid_pruning_table_pkey PRIMARY KEY (col1)) + PARTITION BY RANGE (col1); + +SELECT create_partition( + p_parent_table := 'partman_test.uuid_pruning_table' + , p_control := 'col1' + , p_interval := '1 month' + , p_time_encoder := 'partman.uuid7_time_encoder' + , p_time_decoder := 'partman.uuid7_time_decoder' + , p_start_partition := '2026-01-01' + , p_premake := 6 +); + +INSERT INTO partman_test.uuid_pruning_table (col1, col2) +SELECT uuid7_time_encoder(ts), ts::text +FROM generate_series('2026-01-15 00:00:00+00'::timestamptz, '2026-06-15 00:00:00+00'::timestamptz, '1 month') AS ts; + +SELECT cmp_ok( + (SELECT count(*)::int FROM pg_catalog.pg_inherits WHERE inhparent = 'partman_test.uuid_pruning_table'::regclass) + , '>', 6, 'Check partition set has more than 6 child tables'); + +-- Capture the plan. Index and bitmap scans are off so each child shows up as one Seq Scan line. +SET enable_indexscan = off; +SET enable_indexonlyscan = off; +SET enable_bitmapscan = off; +CREATE TEMP TABLE uuid_pruning_plan (line text); +CREATE FUNCTION partman_test.capture_plan(p_query text) RETURNS void LANGUAGE plpgsql AS $$ +DECLARE v_line text; +BEGIN + DELETE FROM uuid_pruning_plan; + FOR v_line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || p_query LOOP + INSERT INTO uuid_pruning_plan VALUES (v_line); + END LOOP; +END +$$; + +SELECT partman_test.capture_plan($q$SELECT count(*) FROM partman_test.uuid_pruning_table + WHERE col1 >= partman.uuid7_time_encoder('2026-03-01 00:00:00+00') + AND col1 < partman.uuid7_time_encoder('2026-04-01 00:00:00+00')$q$); +SELECT is( + (SELECT count(*)::int FROM uuid_pruning_plan WHERE line ~ 'Seq Scan on uuid_pruning_table_') + , 1, 'Check query filtered with uuid7_time_encoder() is pruned to one child table'); +SELECT is( + (SELECT count(*)::int FROM uuid_pruning_plan WHERE line ~ 'Seq Scan on uuid_pruning_table_p20260301 ') + , 1, 'Check the remaining child table is uuid_pruning_table_p20260301'); +SELECT is( + (SELECT count(*)::int FROM uuid_pruning_plan WHERE line ~ 'uuid7_time_encoder') + , 0, 'Check uuid7_time_encoder() calls were folded to constants at plan time'); + +RESET enable_indexscan; +RESET enable_indexonlyscan; +RESET enable_bitmapscan; + +SELECT results_eq( + $q$SELECT col2 FROM partman_test.uuid_pruning_table + WHERE col1 >= partman.uuid7_time_encoder('2026-03-01 00:00:00+00') + AND col1 < partman.uuid7_time_encoder('2026-04-01 00:00:00+00')$q$ + , ARRAY[('2026-03-15 00:00:00+00'::timestamptz)::text] + , 'Check pruned query still returns the matching row'); + +SELECT is( + (SELECT count(*)::int FROM partman_test.uuid_pruning_table + WHERE uuid7_time_decoder(col1::text) >= '2026-03-01 00:00:00+00' + AND uuid7_time_decoder(col1::text) < '2026-05-01 00:00:00+00') + , 2, 'Check uuid7_time_decoder() returns the encoded timestamps'); + +SELECT lives_ok( + $q$CREATE INDEX uuid_pruning_table_decoded_idx ON partman_test.uuid_pruning_table (partman.uuid7_time_decoder(col1::text))$q$ + , 'Check uuid7_time_decoder() can be used in an index expression'); + +SELECT * FROM finish(); +ROLLBACK; diff --git a/updates/pg_partman--5.5.0--5.5.1.sql b/updates/pg_partman--5.5.0--5.5.1.sql index 72f7a65..16ce65c 100644 --- a/updates/pg_partman--5.5.0--5.5.1.sql +++ b/updates/pg_partman--5.5.0--5.5.1.sql @@ -1752,3 +1752,8 @@ RETURN true; END $$; + + +-- uuid7 helpers were VOLATILE by default, which blocked partition pruning (Github Issue #873) +ALTER FUNCTION @extschema@.uuid7_time_encoder(timestamptz) IMMUTABLE PARALLEL SAFE; +ALTER FUNCTION @extschema@.uuid7_time_decoder(text) IMMUTABLE PARALLEL SAFE;

view thread (8+ messages)  latest in thread

Message-ID: <gh-pgpartman-pg_partman-880@github.com>
Permalink:  ../gh-pgpartman-pg_partman-880@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