pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
10+ messages / 2 participants
[nested] [flat]

* [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-06-09 03:58 "maitredede (@maitredede)" <noreply+maitredede@github.com>
  0 siblings, 0 replies; 10+ messages in thread

From: maitredede (@maitredede) @ 2026-06-09 03:58 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

Hello,

Tested with postgresql 18 (docker debian), partman 5.4.3 (apt package) :

Partition on column type `date` fails when value is first day of month. 

A workaround would be to precreate partitions before inserting data.

Here is an example script :

```sql
-- ============================================================================
-- pg_partman partition_data_proc — off-by-one on month boundaries
--
-- Self-contained reproduction. Uses a generic `events` table only (no external
-- data). Run against a database where the `pg_partman` extension is installed:
--
--     psql "<dsn>" -f repro.sql
--
-- ON_ERROR_STOP is OFF on purpose: we want the script to keep going after the
-- bug raises its error, so the WORKAROUND section below can also run.
-- ============================================================================
\set ON_ERROR_STOP off

\echo ''
\echo '==================== pg_partman version ===================='
SELECT extversion FROM pg_extension WHERE extname = 'pg_partman';

-- ----------------------------------------------------------------------------
-- 1. THE BUG
-- ----------------------------------------------------------------------------
\echo ''
\echo '#################### 1. BUG ####################'

DROP SCHEMA IF EXISTS repro CASCADE;
CREATE SCHEMA repro;

-- A plain monthly RANGE-partitioned table.
CREATE TABLE repro.events (
  id         bigint NOT NULL,
  event_date date   NOT NULL,
  payload    text,
  PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

SELECT partman.create_parent(
  p_parent_table := 'repro.events',
  p_control      := 'event_date',
  p_interval     := '1 month',
  p_type         := 'range',
  p_jobmon       := false
);

-- Historical rows (far from now) fall into the DEFAULT partition, because
-- create_parent only pre-makes partitions around the current date.
--
-- The KEY detail: the lowest value (2002-06-15) is NOT aligned to a month
-- boundary. partition_data_time builds its batch window from that raw minimum,
-- i.e. [2002-06-15, 2002-07-15), which spans TWO months — yet it routes the
-- whole batch into the single partition of the window start (June 2002).
-- The 2002-07-01 row therefore lands in the June partition and is rejected.
INSERT INTO repro.events (id, event_date, payload) VALUES
  (1, DATE '2002-06-15', 'mid-June'),
  (2, DATE '2002-07-01', 'boundary: 1st of July'),
  (3, DATE '2002-07-15', 'mid-July');

\echo '--- before: all rows sit in the default partition ---'
SELECT tableoid::regclass AS partition, count(*) FROM repro.events GROUP BY 1;

\echo '--- calling partition_data_proc (EXPECT: "violates partition constraint") ---'
CALL partman.partition_data_proc('repro.events');

\echo '--- after: rows are STILL in default because the move above errored ---'
SELECT tableoid::regclass AS partition, id, event_date FROM repro.events ORDER BY id;
\echo '--- rows still in default (bug => 3, fixed => 0) ---'
SELECT count(*) AS rows_in_default FROM repro.events_default;

-- ----------------------------------------------------------------------------
-- 2. The p_date_trunc_interval parameter is NOT the cause
--    Same setup but with p_date_trunc_interval := 'month' — fails identically.
-- ----------------------------------------------------------------------------
\echo ''
\echo '#################### 2. p_date_trunc_interval := month (same failure) ####################'

DROP SCHEMA IF EXISTS repro_trunc CASCADE;
CREATE SCHEMA repro_trunc;
CREATE TABLE repro_trunc.events (
  id bigint NOT NULL, event_date date NOT NULL, payload text,
  PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
SELECT partman.create_parent(
  p_parent_table        := 'repro_trunc.events',
  p_control             := 'event_date',
  p_interval            := '1 month',
  p_type                := 'range',
  p_date_trunc_interval := 'month',
  p_jobmon              := false
);
INSERT INTO repro_trunc.events (id, event_date, payload) VALUES
  (1, DATE '2002-06-15', 'mid-June'),
  (2, DATE '2002-07-01', 'boundary: 1st of July'),
  (3, DATE '2002-07-15', 'mid-July');
\echo '--- calling partition_data_proc (EXPECT: same error) ---'
CALL partman.partition_data_proc('repro_trunc.events');
SELECT count(*) AS rows_in_default FROM repro_trunc.events_default;

-- ----------------------------------------------------------------------------
-- 3. WORKAROUND: pre-create the partitions, then INSERT directly.
--    PostgreSQL's native row routing has no such off-by-one, so every row —
--    including the month boundaries — lands in the correct partition.
-- ----------------------------------------------------------------------------
\echo ''
\echo '#################### 3. WORKAROUND (native routing) ####################'

DROP SCHEMA IF EXISTS fixed CASCADE;
CREATE SCHEMA fixed;
CREATE TABLE fixed.events (
  id bigint NOT NULL, event_date date NOT NULL, payload text,
  PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
SELECT partman.create_parent(
  p_parent_table := 'fixed.events',
  p_control      := 'event_date',
  p_interval     := '1 month',
  p_type         := 'range',
  p_jobmon       := false
);

-- Pre-create exactly the months we are about to load (partman-managed partitions,
-- correct names). In real use, derive these from the data:
--   ARRAY(SELECT DISTINCT date_trunc('month', <date_col>)::timestamptz FROM <staging>)
SELECT partman.create_partition_time(
  'fixed.events',
  ARRAY['2002-06-01', '2002-07-01']::timestamptz[]
);

INSERT INTO fixed.events (id, event_date, payload) VALUES
  (1, DATE '2002-06-15', 'mid-June'),
  (2, DATE '2002-07-01', 'boundary: 1st of July'),
  (3, DATE '2002-07-15', 'mid-July');

\echo '--- distribution (each row in its own month) ---'
SELECT tableoid::regclass AS partition, id, event_date FROM fixed.events ORDER BY id;
\echo '--- rows in default (EXPECT 0) ---'
SELECT count(*) AS rows_in_default FROM fixed.events_default;

\echo ''
\echo '==================== done ===================='
```

Problem seams to come from a text comparison https://github.com/pgpartman/pg_partman/blob/development/sql/functions/partition_data_time.sql#L311-... :

When I have a date value, its text representation is `2002-07-01`  (date without time). When compared in code, it is casted as timestamptz, then text, `2002-07-01 00:00:00+00`. So the string comparison is `'2002-07-01' < '2002-07-01 00:00:00+00'` => `true` : "a prefix is placed before".

A fix would be to make comparison as timestamptz instead of text.

Old code (https://github.com/pgpartman/pg_partman/blob/development/sql/functions/partition_data_time.sql#L311-...):
```sql
    IF v_time_decoder IS NULL THEN
        IF v_control_exact_type = 'timestamp' THEN
            -- Conversion to text of timestamp without timezone cannot be directly compared to timestamptz
            -- that is returned by other functions and used below (See Github Issue #838)
            v_decoded_col := format('%s::timestamptz::text', v_partition_expression);
        ELSE
            v_decoded_col := format('%s::text', v_partition_expression);
        END IF;
    ELSE
        v_decoded_col := format('%s(%s::text)', v_time_decoder, v_partition_expression);
    END IF;
```
New code :

```sql
    IF v_time_decoder IS NULL THEN
        v_decoded_col := format('%s::timestamptz', v_partition_expression);
    ELSE
        v_decoded_col := format('%s(%s::text)', v_time_decoder, v_partition_expression);
    END IF;
```

I have tested it locally, and it does not break #838. 

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

* Re: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-10 14:30 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-10 14:30 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 #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-10 14:30 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-10 14:30 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 applied label "duplicate".

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

* Re: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-10 14:30 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-10 14:30 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 #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-10 14:31 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-10 14:31 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

I believe this is a dupe of #856 and should be fixed in the 5.5.1 release which has a PR up here: #883 

Would you mind confirming?

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

* Re: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-10 14:31 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-10 14:31 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 #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-19 10:20 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-19 10:20 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 set milestone to "5.5".

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

* Re: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-19 10:20 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-19 10:20 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 removed milestone.

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

* Re: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-19 10:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-19 10:21 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 applied label "fixed in current release".

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

* Re: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
@ 2026-11-19 10:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: keithf4 (@keithf4) @ 2026-11-19 10:21 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

@keithf4 removed label "fixed in next release".

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


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

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-09 03:58 [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint "maitredede (@maitredede)" <noreply+maitredede@github.com>
2026-11-10 14:30 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-10 14:30 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-10 14:30 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-10 14:31 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-10 14:31 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-19 10:20 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-19 10:20 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-19 10:21 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-19 10:21 ` "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