pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
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;
```

view thread (10+ messages)  latest in thread

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