pg.ddx.io pgpartman/pg_partman GitHub issues and pull requests
help / color / mirror / Atom feed[pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
10+ messages / 2 participants
[nested] [flat]
* [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-08-04 06:19 "povilas-amlyze (@povilas-amlyze)" <noreply+povilas-amlyze@github.com>
0 siblings, 0 replies; 10+ messages in thread
From: povilas-amlyze (@povilas-amlyze) @ 2026-08-04 06:19 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
**pg_partman version: 5.4.3 (also present in 5.5.0)**
**Summary**
The built-in helpers in s`ql/functions/util_uuid7_time.sql `are declared without a volatility category, so they default to `VOLATILE`. Because the planner may not evaluate a volatile function before a scan begins, a query that
expresses a time range through `uuid7_time_encoder()` cannot prune: the call remains inside each partition's per-row Filter and **every partition of the set is scanned**.
That undercuts the main appeal of uuid/text control columns — you can partition by time, but you cannot then query by time without computing the uuid bounds outside the statement and pasting them in as literals.
**Reproduction**
Self-contained, on a fresh database:
```sql
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
CREATE SCHEMA IF NOT EXISTS partman_test;
CREATE TABLE partman_test.uuid_pruning_demo (
id uuid NOT NULL,
payload text,
CONSTRAINT uuid_pruning_demo_pkey PRIMARY KEY (id)
) PARTITION BY RANGE (id);
SELECT partman.create_partition(
p_parent_table => 'partman_test.uuid_pruning_demo'
, p_control => 'id'
, 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
);
```
```
-- one row per month, ids generated with the encoder itself
INSERT INTO partman_test.uuid_pruning_demo (id, payload)
SELECT partman.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;
ANALYZE partman_test.uuid_pruning_demo;
```
Query one month:
```
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*)
FROM partman_test.uuid_pruning_demo
WHERE id >= partman.uuid7_time_encoder('2026-03-01 00:00:00+00')
AND id < partman.uuid7_time_encoder('2026-04-01 00:00:00+00');
```
**Actual** — every partition scanned (loops=1 on each), no Subplans Removed, and the encoder call visible in each filter:
```
Aggregate (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Seq Scan on uuid_pruning_demo_p20260101 (actual rows=0 loops=1)
Filter: ((id >= partman.uuid7_time_encoder('2026-03-01 00:00:00+00'::timestamptz))
AND (id < partman.uuid7_time_encoder('2026-04-01 00:00:00+00'::timestamptz)))
-> Seq Scan on uuid_pruning_demo_p20260201 (actual rows=0 loops=1)
Filter: (…)
-> Seq Scan on uuid_pruning_demo_p20260301 (actual rows=1 loops=1)
Filter: (…)
…every remaining partition, plus _default…
```
**Expected** — only uuid_pruning_demo_p20260301 is scanned.
**Cause**
```
SELECT proname, provolatile
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'partman' AND proname LIKE 'uuid7_time%';
-- proname | provolatile
-- uuid7_time_encoder | v
-- uuid7_time_decoder | v
```
`sql/functions/util_uuid7_time.sql `declares neither function's volatility, and PL/pgSQL defaults to `VOLATILE`.
That volatility is the only factor. An otherwise identical wrapper that differs solely in its declared category prunes to one partition:
```
CREATE FUNCTION partman_test.uuid7_at(ts timestamptz)
RETURNS uuid LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
AS $$ BEGIN RETURN partman.uuid7_time_encoder(ts); END; $$;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*)
FROM partman_test.uuid_pruning_demo
WHERE id >= partman_test.uuid7_at('2026-03-01 00:00:00+00')
AND id < partman_test.uuid7_at('2026-04-01 00:00:00+00');
```
```
-- Aggregate (actual rows=1 loops=1)
-- -> Seq Scan on uuid_pruning_demo_p20260301 (actual rows=1 loops=1)
```
**Suggested fix**
Declare both functions **IMMUTABLE PARALLEL SAFE**:
```sql
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
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
```
Both are pure functions of their argument: bit manipulation only, no table access, no session state. The encoder takes `timestamptz`, an absolute instant, so the mapping does not depend on the session's `TimeZone`; the UUIDv7 field layout is fixed by RFC 9562.
`STABLE` alone would already enable execution-time pruning, but `IMMUTABLE` additionally allows plan-time folding — so untouched partitions never enter the plan and are not planned at all, which matters once a set has dozens of
children. It also makes `uuid7_time_decoder` usable in expression indexes and generated columns.
**Workaround**
An **IMMUTABLE** plpgsql wrapper as shown above (plpgsql rather than SQL, so it is not inlined back into the query and the declared category is what the planner uses).
**Cleanup**
```
SELECT partman.config_cleanup('partman_test.uuid_pruning_demo');
DROP TABLE partman_test.uuid_pruning_demo CASCADE;
DROP SCHEMA partman_test CASCADE;
```
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-08-04 15:26 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-08-04 15:26 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 assigned @keithf4.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-08-04 15:26 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-08-04 15:26 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 applied label "pending review".
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-08-04 15:26 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-08-04 15:26 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 set milestone to "Next Patch".
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-08-04 15:27 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-08-04 15:27 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Thank you for this! Will review during my next session.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-10-28 14:20 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-10-28 14:20 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
I've merged PR #880 into the `551` branch if you'd like to test this out. I'm hopefully going to get 5.5.1 out by next week.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-10-28 14:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-10-28 14:21 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 removed label "pending review".
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-10-28 14:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-10-28 14:21 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 applied label "confirmed bug".
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-10-28 14:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-10-28 14:21 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 applied label "fixed in next release".
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
@ 2026-11-17 22:02 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: keithf4 (@keithf4) @ 2026-11-17 22:02 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@keithf4 closed this.
^ permalink raw [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-11-17 22:02 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-04 06:19 [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys "povilas-amlyze (@povilas-amlyze)" <noreply+povilas-amlyze@github.com>
2026-08-04 15:26 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-08-04 15:26 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-08-04 15:26 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-08-04 15:27 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-10-28 14:20 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-10-28 14:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-10-28 14:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-10-28 14:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-17 22:02 ` "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