From: AlexeyShalaev (@AlexeyShalaev) <noreply+AlexeyShalaev@github.com>
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Subject: [pgpartman/pg_partman] PR #879: fix: partition_data_time() compares the control column in its own type
Date: Thu, 01 Oct 2026 20:08:27 +0000
Message-ID: <gh-pgpartman-pg_partman-879@github.com> (raw)
Fixes #856 (and #868, which is the same bug).
Since 5.4.0, `partition_data_time()` picks the rows to move by comparing the control column as text: `col3::text >= '2026-10-01 00:00:00+00'`. For a `date` column that sorts `'2026-10-01'` before `'2026-10-01 00:00:00+00'`, so a child table's first day is never selected and stays in the default, where the attach then fails with `updated partition constraint for default partition ... would be violated`, and the first day of the next range is selected, which is the `violates partition constraint` in #856. A daily table hits it every month, since every one of its first-of-month rows sits exactly there.
This goes back to comparing the column in its own type, as it was before 5.4.0. That is also how PostgreSQL reads the child's `FOR VALUES FROM (%L) TO (%L)` bounds, which are formatted from the same timestamptz values, so the rows selected for a child are the rows its constraint accepts, for `date`, `timestamp`, `timestamptz` and the epoch expressions alike. It covers what #838 fixed for `timestamp without time zone`, so that special case and `v_control_exact_type` go away. Encoded columns still compare through their decoder, unchanged.
A side effect: the text cast kept the comparison off any index on the control column. On a million-row table with a `date` index, one month's selection took 37 ms as a parallel seq scan before and 0.8 ms as a bitmap index scan after, which adds up when the default is large and every batch runs it.
Since `partition_data_time` is already in `pg_partman--5.5.0--5.5.1.sql` on this branch, the same change is there too, plus a line in the 5.5.1 changelog.
`test/test-time-monthly-boundary.sql` puts rows on the first, middle and last day of a month and on the first day of the next one, and moves them out of the default and out of a source table for a `date` column, and out of the default for `timestamp without time zone` (the #838 case, which had no test). The `date` part fails on 551 as it is; the `timestamp` part passes both before and after.
Tested with pg_prove in `pgxn/pgxn-tools`, the same way CI runs:
- `test/` on PG 14, 15, 16, 17 and 18: 4200 tests, one failure on each, `test-time-daily.sql` test 4 (the INCLUDE index check), which fails the same way on 551 without this change;
- `test/test_procedure/test-time-procedure-*`, run part by part with the `CALL` each part asks for, on PG 17: all 19 parts pass.
inline patch 1: CHANGELOG.md, sql/functions/partition_data_time.sql, test/test-time-monthly-boundary.sql and 1 more (+90/-18 lines) download inline-1.patchdiff --git a/CHANGELOG.md b/CHANGELOG.mdindex 92dc392..f5d67e5 100644--- a/CHANGELOG.md+++ b/CHANGELOG.md@@ -4,6 +4,7 @@ BUG FIXES
---------
- Fix variable assignment misalignment in partition_data_time() when using the p_lock_wait parameter. (Github PR #871)
- Fix inherit_template_properties() adding a template table's INCLUDE index columns as real key columns on child tables instead of ignoring them. (Github Issue #674)
+ - Fix partition_data_time() and partition_data_proc() leaving rows on the first day of a child table's range in the default, and taking rows on the first day of the next range, when the control column is a date. The rows are compared in the control column's own type again instead of as text. (Github Issue #856)
5.5.0
diff --git a/sql/functions/partition_data_time.sql b/sql/functions/partition_data_time.sqlindex 260a5ca..44af40e 100644--- a/sql/functions/partition_data_time.sql+++ b/sql/functions/partition_data_time.sql@@ -21,7 +21,6 @@ v_async_rowcount int;
v_column_list_filtered text;
v_column_list_full text;
v_control text;
-v_control_exact_type text;
v_control_type text;
v_datetime_string text;
v_current_partition_name text;
@@ -91,7 +90,7 @@ FROM pg_catalog.pg_tables
WHERE schemaname = split_part(p_parent_table, '.', 1)::name
AND tablename = split_part(p_parent_table, '.', 2)::name;
-SELECT general_type, exact_type INTO v_control_type, v_control_exact_type FROM @extschema@.check_control_type(v_parent_schemaname, v_parent_tablename, v_control);+SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schemaname, v_parent_tablename, v_control);
IF v_control_type <> 'time' THEN
IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type NOT IN ('text', 'id', 'uuid') OR (v_control_type IN ('text', 'uuid') AND v_time_encoder IS NULL) THEN
RAISE EXCEPTION 'Cannot run on partition set without time based control column, an epoch flag set with an id column or time_encoder set with text column. Found control: %, epoch: %, time_encoder: %s', v_control_type, v_epoch, v_time_encoder;
@@ -316,13 +315,10 @@ FOR i IN 1..p_batch_count LOOP
-- Create a variable to use in all scenarios below for handling encoded columns
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;+ -- Compare in the control column's own type, which is how the child table's bounds are read.+ -- Compared as text, a date sorts before the timestamptz bound it is equal to ('2026-10-01' < '2026-10-01 00:00:00+00'),+ -- so the first day of a child table's range was left behind and the first day of the next one taken (Github Issues #838, #856)+ v_decoded_col := v_partition_expression;
ELSE
v_decoded_col := format('%s(%s::text)', v_time_decoder_safe, v_partition_expression);
END IF;
diff --git a/test/test-time-monthly-boundary.sql b/test/test-time-monthly-boundary.sqlnew file mode 100644index 0000000..548c559--- /dev/null+++ b/test/test-time-monthly-boundary.sql@@ -0,0 +1,79 @@+-- ########## TIME MONTHLY, ROWS ON A CHILD TABLE'S BOUNDARIES ##########+-- Other tests:+ -- partition_data_time() moves a row on the first instant of a month into that month, and leaves+ -- a row on the first instant of the next month out of it+ -- date control column, out of the default and out of a source table (Github Issue #856)+ -- timestamp without time zone control column (Github Issue #838)++\set ON_ERROR_ROLLBACK 1+\set ON_ERROR_STOP true++BEGIN;+SELECT set_config('search_path','partman, public',false);++SELECT plan(12);+CREATE SCHEMA partman_test;++CREATE TABLE partman_test.time_taptest_table+ (col1 int+ , col2 text+ , col3 date NOT NULL)+ PARTITION BY RANGE (col3);++SELECT create_partition('partman_test.time_taptest_table', 'col3', '1 month', p_start_partition := to_char(date_trunc('month', CURRENT_DATE), 'YYYY-MM-DD'));++-- First, middle and last day of the month three months back, and the first day of the month after it.+-- No child table covers either month yet, so all four rows go to the default.+INSERT INTO partman_test.time_taptest_table (col1, col3) VALUES+ (1, date_trunc('month', CURRENT_DATE - '3 months'::interval)::date)+ , (2, date_trunc('month', CURRENT_DATE - '3 months'::interval)::date + 14)+ , (3, date_trunc('month', CURRENT_DATE - '2 months'::interval)::date - 1)+ , (4, date_trunc('month', CURRENT_DATE - '2 months'::interval)::date);++SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table''::text, p_batch_count := 2)::int', ARRAY[4], 'Check that partition_data_time moves all four rows out of the default');+SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_default', 'Check that the default is empty');+SELECT results_eq('SELECT col1 FROM partman_test.time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '3 months'::interval), 'YYYYMMDD')||' ORDER BY col1',+ ARRAY[1, 2, 3], 'Check that time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '3 months'::interval), 'YYYYMMDD')||' holds its first, middle and last day');+SELECT results_eq('SELECT col1 FROM partman_test.time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '2 months'::interval), 'YYYYMMDD')||' ORDER BY col1',+ ARRAY[4], 'Check that time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '2 months'::interval), 'YYYYMMDD')||' holds its first day');++-- The same four days, six and five months back, out of a source table+CREATE TABLE partman_test.time_taptest_source (col1 int, col2 text, col3 date NOT NULL);+INSERT INTO partman_test.time_taptest_source (col1, col3) VALUES+ (5, date_trunc('month', CURRENT_DATE - '6 months'::interval)::date)+ , (6, date_trunc('month', CURRENT_DATE - '6 months'::interval)::date + 14)+ , (7, date_trunc('month', CURRENT_DATE - '5 months'::interval)::date - 1)+ , (8, date_trunc('month', CURRENT_DATE - '5 months'::interval)::date);++SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table''::text, p_batch_count := 2, p_source_table := ''partman_test.time_taptest_source'')::int',+ ARRAY[4], 'Check that partition_data_time moves all four rows out of the source table');+SELECT is_empty('SELECT * FROM partman_test.time_taptest_source', 'Check that the source table is empty');+SELECT results_eq('SELECT col1 FROM partman_test.time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '6 months'::interval), 'YYYYMMDD')||' ORDER BY col1',+ ARRAY[5, 6, 7], 'Check that time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '6 months'::interval), 'YYYYMMDD')||' holds its first, middle and last day');+SELECT results_eq('SELECT col1 FROM partman_test.time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '5 months'::interval), 'YYYYMMDD')||' ORDER BY col1',+ ARRAY[8], 'Check that time_taptest_table_p'||to_char(date_trunc('month', CURRENT_DATE - '5 months'::interval), 'YYYYMMDD')||' holds its first day');++-- A timestamp without time zone control column, with rows on the first and last second of a month+CREATE TABLE partman_test.time_taptest_timestamp+ (col1 int+ , col2 text+ , col3 timestamp NOT NULL)+ PARTITION BY RANGE (col3);++SELECT create_partition('partman_test.time_taptest_timestamp', 'col3', '1 month', p_start_partition := to_char(date_trunc('month', CURRENT_DATE), 'YYYY-MM-DD'));++INSERT INTO partman_test.time_taptest_timestamp (col1, col3) VALUES+ (9, date_trunc('month', LOCALTIMESTAMP - '3 months'::interval))+ , (10, date_trunc('month', LOCALTIMESTAMP - '3 months'::interval) + '14 days'::interval)+ , (11, date_trunc('month', LOCALTIMESTAMP - '2 months'::interval) - '1 second'::interval)+ , (12, date_trunc('month', LOCALTIMESTAMP - '2 months'::interval));++SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_timestamp''::text, p_batch_count := 2)::int', ARRAY[4], 'Check that partition_data_time moves all four timestamp rows out of the default');+SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_timestamp_default', 'Check that the timestamp default is empty');+SELECT results_eq('SELECT col1 FROM partman_test.time_taptest_timestamp_p'||to_char(date_trunc('month', CURRENT_DATE - '3 months'::interval), 'YYYYMMDD')||' ORDER BY col1',+ ARRAY[9, 10, 11], 'Check that time_taptest_timestamp_p'||to_char(date_trunc('month', CURRENT_DATE - '3 months'::interval), 'YYYYMMDD')||' holds its first and last second');+SELECT results_eq('SELECT col1 FROM partman_test.time_taptest_timestamp_p'||to_char(date_trunc('month', CURRENT_DATE - '2 months'::interval), 'YYYYMMDD')||' ORDER BY col1',+ ARRAY[12], 'Check that time_taptest_timestamp_p'||to_char(date_trunc('month', CURRENT_DATE - '2 months'::interval), 'YYYYMMDD')||' holds its first second');++SELECT * FROM finish();+ROLLBACK;diff --git a/updates/pg_partman--5.5.0--5.5.1.sql b/updates/pg_partman--5.5.0--5.5.1.sqlindex 09687bd..72f7a65 100644--- a/updates/pg_partman--5.5.0--5.5.1.sql+++ b/updates/pg_partman--5.5.0--5.5.1.sql@@ -23,7 +23,6 @@ v_async_rowcount int;
v_column_list_filtered text;
v_column_list_full text;
v_control text;
-v_control_exact_type text;
v_control_type text;
v_datetime_string text;
v_current_partition_name text;
@@ -93,7 +92,7 @@ FROM pg_catalog.pg_tables
WHERE schemaname = split_part(p_parent_table, '.', 1)::name
AND tablename = split_part(p_parent_table, '.', 2)::name;
-SELECT general_type, exact_type INTO v_control_type, v_control_exact_type FROM @extschema@.check_control_type(v_parent_schemaname, v_parent_tablename, v_control);+SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schemaname, v_parent_tablename, v_control);
IF v_control_type <> 'time' THEN
IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type NOT IN ('text', 'id', 'uuid') OR (v_control_type IN ('text', 'uuid') AND v_time_encoder IS NULL) THEN
RAISE EXCEPTION 'Cannot run on partition set without time based control column, an epoch flag set with an id column or time_encoder set with text column. Found control: %, epoch: %, time_encoder: %s', v_control_type, v_epoch, v_time_encoder;
@@ -318,13 +317,10 @@ FOR i IN 1..p_batch_count LOOP
-- Create a variable to use in all scenarios below for handling encoded columns
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;+ -- Compare in the control column's own type, which is how the child table's bounds are read.+ -- Compared as text, a date sorts before the timestamptz bound it is equal to ('2026-10-01' < '2026-10-01 00:00:00+00'),+ -- so the first day of a child table's range was left behind and the first day of the next one taken (Github Issues #838, #856)+ v_decoded_col := v_partition_expression;
ELSE
v_decoded_col := format('%s(%s::text)', v_time_decoder_safe, v_partition_expression);
END IF;