pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
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.md index 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.sql index 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.sql new file mode 100644 index 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.sql index 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;

view thread (12+ messages)  latest in thread

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