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