pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
6+ messages / 3 participants
[nested] [flat]

* [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
@ 2026-06-25 13:19 "jakubmalek (@jakubmalek)" <noreply+jakubmalek@github.com>
  0 siblings, 0 replies; 6+ messages in thread

From: jakubmalek (@jakubmalek) @ 2026-06-25 13:19 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

With latest `5.4.3` version pg_partman, I'm unable to create range partition for child-partition table.

Here is an example of the initial setup:
```sql
CREATE TYPE retention_tier AS ENUM (
  'standard',
  'extended'
);

CREATE TABLE IF NOT EXISTS events (
    id BIGSERIAL NOT NULL,
    retention_tier retention_tier NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    data JSONB NOT NULL
) PARTITION BY LIST (retention_tier);

CREATE TABLE events_standard PARTITION OF events
    FOR VALUES IN ('standard')
    PARTITION BY RANGE (event_time);
CREATE TABLE events_extended PARTITION OF events
    FOR VALUES IN ('extended')
    PARTITION BY RANGE (event_time);
```

Then when I try to create partition for `events_standard` table:

```sql
SELECT create_partition(
    p_parent_table => 'public.events_standard',
    p_control => 'event_time',
    p_interval=> '1 day',
    p_type => 'range',
    p_premake => 3,
    p_constraint_cols => ARRAY['id']);
```

I get following error:

```
ERROR:  invalid input syntax for type timestamp with time zone: "'standard'"
CONTEXT: PL/pgSQL function show_partition_info(text,text,text,boolean) line 130 at assignment
SQL statement "SELECT child_start_time::text, child_end_time::text
                                      FROM public.show_partition_info(p_parent_table, v_top_interval, v_top_schema||'.'||v_top_tablename)"
PL/pgSQL function check_subpartition_limits(text,text) line 52 at SQL statement
SQL statement "SELECT sub_min::timestamptz, sub_max::timestamptz                                               FROM public.check_subpartition_limits(p_parent_table, 'time')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],text) line 105 at SQL statement
PL/pgSQL function create_partition(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text,bigint) line 383 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],text) line 401 at RAISE
PL/pgSQL function create_partition(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text,bigint) line 383 at assignment
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function create_partition(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text,bigint) line 638 at RAISE
```

It looks like partman is trying to parse parent partitioning column as timestamp, which fails.

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
@ 2026-06-25 13:28 ` "jakubmalek (@jakubmalek)" <noreply+jakubmalek@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: jakubmalek (@jakubmalek) @ 2026-06-25 13:28 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

There is also problem with nested hash partitions:

```sql
CREATE TABLE IF NOT EXISTS events (
    id BIGSERIAL NOT NULL,
    customer_id BIGINT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    data JSONB NOT NULL
) PARTITION BY HASH (customer_id);

CREATE TABLE events_p00 PARTITION OF events
    FOR VALUES WITH (modulus 3, remainder 0)
    PARTITION BY RANGE (event_time);
CREATE TABLE events_p01 PARTITION OF events
    FOR VALUES WITH (modulus 3, remainder 1)
    PARTITION BY RANGE (event_time);
CREATE TABLE objects_p02 PARTITION OF events
    FOR VALUES WITH (modulus 3, remainder 2)
    PARTITION BY RANGE (event_time);
```

Then when range partition is created:

```sql
SELECT create_partition(
    p_parent_table => 'public.events_p00',
    p_control => 'event_time',
    p_interval=> '1 day',
    p_type => 'range',
    p_premake => 1,
    p_constraint_cols => ARRAY['id']);
```

I get this error:

```
ERROR:  partman functions only work with list partitioning with integers and ranged partitioning with time or integers. Found partition strategy "h" for given partition set
CONTEXT: PL/pgSQL function show_partition_info(text,text,text,boolean) line 109 at RAISE
SQL statement "SELECT child_start_time::text, child_end_time::text
                                      FROM public.show_partition_info(p_parent_table, v_top_interval, v_top_schema||'.'||v_top_tablename)"
PL/pgSQL function check_subpartition_limits(text,text) line 52 at SQL statement
SQL statement "SELECT sub_min::timestamptz, sub_max::timestamptz                                               FROM public.check_subpartition_limits(p_parent_table, 'time')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],text) line 105 at SQL statement
PL/pgSQL function create_partition(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text,bigint) line 383 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],text) line 401 at RAISE
PL/pgSQL function create_partition(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text,bigint) line 383 at assignment
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function create_partition(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text,boolean,text,text,bigint) line 638 at RAISE
```

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
@ 2026-06-25 13:52 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: keithf4 (@keithf4) @ 2026-06-25 13:52 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

I'll have to look into this. I hadn't tested sub-partitions with a parent of a type that pg_partman does not support (list and hash)

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
@ 2026-06-25 13:53 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: keithf4 (@keithf4) @ 2026-06-25 13:53 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 applied label "pending review".

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
@ 2026-06-25 13:53 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: keithf4 (@keithf4) @ 2026-06-25 13:53 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 set milestone to "Future".

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman
@ 2026-11-19 16:18 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: ritojoao (@ritojoao) @ 2026-11-19 16:18 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

Hi,

I tried both SQL scripts from this issue (the LIST -> RANGE case from the first post and the nested HASH -> RANGE case from the comment) on current master (5.5.1, commit e0a6c38). I ran them exactly as written, on PostgreSQL 17.12 and 16.16.

Both calls to `create_partition()` returned `t`, with no error:

- LIST -> RANGE (`public.events_standard`, premake 3, `p_constraint_cols => ARRAY['id']`): created 7 daily partitions plus the default.
- HASH -> RANGE (`public.events_p00`, premake 1, `p_constraint_cols => ARRAY['id']`): created 3 daily partitions plus the default.

The errors in the report came from 5.4.3. It looks like this was fixed after that release, possibly by the sub-partitioning fix in #857, but I did not bisect to confirm which commit. If the original reporter can retest on 5.5.1, this issue may be closable.

I only tested the two scripts above, not other nesting depths or partition types.


^ permalink  raw  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2026-11-19 16:18 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-25 13:19 [pgpartman/pg_partman] issue #870: Error while creating nested partition with pg_partman "jakubmalek (@jakubmalek)" <noreply+jakubmalek@github.com>
2026-06-25 13:28 ` "jakubmalek (@jakubmalek)" <noreply+jakubmalek@github.com>
2026-06-25 13:52 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-06-25 13:53 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-06-25 13:53 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-19 16:18 ` "ritojoao (@ritojoao)" <noreply+ritojoao@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