From: povilas-amlyze (@povilas-amlyze) <noreply+povilas-amlyze@github.com>
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Subject: [pgpartman/pg_partman] issue #873: uuid7_time_encoder / uuid7_time_decoder are VOLATILE, which prevents partition pruning on UUIDv7 partition keys
Date: Tue, 04 Aug 2026 06:19:32 +0000
Message-ID: <gh-pgpartman-pg_partman-873@github.com> (raw)
**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;
```