pg.ddx.io pgpartman/pg_partman GitHub issues and pull requests
help / color / mirror / Atom feed[pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text
5+ messages / 2 participants
[nested] [flat]
* [pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text
@ 2026-11-10 13:47 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
0 siblings, 0 replies; 5+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-10 13:47 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Fixes #868.
### Problem
`partition_data_time()` casts the control column to text and compares it with the text form of the child table bounds. For a `date` column, `'2002-07-01'` sorts before `'2002-07-01 00:00:00+00'`, so a row on the first day of a month is picked up together with the previous month. The move then fails with:
```
ERROR: new row for relation "events_p20020601" violates partition constraint
```
The reproduction script in #868 shows it with `partition_data_proc()` on a monthly `date` partition set.
### Fix
Compare the control column directly with the bounds cast to `timestamptz`:
```sql
WHERE col >= '...'::timestamptz AND col < '...'::timestamptz
```
- `date` and `timestamp` columns are compared with PostgreSQL's own cross-type operators, so no text ordering is involved.
- This also covers `timestamp without time zone` (#838), so the special case for it is no longer needed.
- The comparison no longer wraps the column in a cast, so an index on the control column can be used.
- The time decoder path is unchanged apart from the cast on the bounds.
I only changed `sql/functions/partition_data_time.sql`. I left the update script alone, as the other recent fixes did. I'm happy to add it if you prefer.
### Tests
- New test `test/test-time-date-monthly-boundary.sql`: it moves first-of-month rows for a `date` and a `timestamp` control column, with `timezone = 'America/New_York'`. It fails before the change and passes after.
- I ran the top-level tests (`pg_prove test/*.sql`) on PostgreSQL 17. All pass except `test-dump-definition.sql` test 2, which also fails on `development` without this change in my setup.
- I ran the reproduction script from #868 by hand: all three rows end up in the right child tables.
Thank you for looking at this!
inline patch 1: sql/functions/partition_data_time.sql, test/test-time-date-monthly-boundary.sql (+62/-11 lines) download inline-1.patch
diff --git a/sql/functions/partition_data_time.sql b/sql/functions/partition_data_time.sql
index 0f72f5c..0aeaf00 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;
@@ -315,14 +314,11 @@ FOR i IN 1..p_batch_count LOOP
-- Create a variable to use in all scenarios below for handling encoded columns
+ -- The boundary values below are always compared as timestamptz, never as text. Text comparison
+ -- puts rows in the wrong child table when the text format of the column differs from timestamptz,
+ -- for example date values (See Github Issues #838 and #868)
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;
+ v_decoded_col := v_partition_expression;
ELSE
v_decoded_col := format('%s(%s::text)', v_time_decoder_safe, v_partition_expression);
END IF;
@@ -338,7 +334,7 @@ FOR i IN 1..p_batch_count LOOP
-- Infinity only needs to be of concern when being removed from the default
- v_sql := format('DELETE FROM %1$I.%2$I WHERE %3$s >= %4$L AND %3$s < %5$L RETURNING *'
+ v_sql := format('DELETE FROM %1$I.%2$I WHERE %3$s >= %4$L::timestamptz AND %3$s < %5$L::timestamptz RETURNING *'
, v_source_schemaname
, v_source_tablename
, v_decoded_col
@@ -369,7 +365,7 @@ FOR i IN 1..p_batch_count LOOP
v_analyze := @extschema@.create_partition_time(p_parent_table, v_partition_timestamp);
v_sql := format('WITH partition_data AS (
- DELETE FROM ONLY %I.%I WHERE %3$s >= %L AND %3$s < %5$L RETURNING *)
+ DELETE FROM ONLY %I.%I WHERE %3$s >= %L::timestamptz AND %3$s < %5$L::timestamptz RETURNING *)
INSERT INTO %6$I.%7$I (%8$s) %9$s SELECT %8$s FROM partition_data'
, v_source_schemaname
, v_source_tablename
diff --git a/test/test-time-date-monthly-boundary.sql b/test/test-time-date-monthly-boundary.sql
new file mode 100644
index 0000000..f945d1a
--- /dev/null
+++ b/test/test-time-date-monthly-boundary.sql
@@ -0,0 +1,55 @@
+-- ########## TIME MONTHLY DATE BOUNDARY ##########
+-- Other tests:
+ -- partition_data_time() with a date and a timestamp without time zone control column
+ -- Rows on the first day of a month must go to that month's child table (See Github Issues #838 and #868)
+ -- Uses a non-UTC time zone so timestamptz text output has a non-zero offset
+
+\set ON_ERROR_ROLLBACK 1
+\set ON_ERROR_STOP true
+
+BEGIN;
+SELECT set_config('search_path','partman, public',false);
+SET LOCAL timezone = 'America/New_York';
+
+SELECT plan(14);
+CREATE SCHEMA partman_test;
+
+CREATE TABLE partman_test.date_taptest_table
+ (col1 int NOT NULL
+ , col2 date NOT NULL)
+ PARTITION BY RANGE (col2);
+
+CREATE TABLE partman_test.ts_taptest_table
+ (col1 int NOT NULL
+ , col2 timestamp NOT NULL)
+ PARTITION BY RANGE (col2);
+
+SELECT create_parent('partman_test.date_taptest_table', 'col2', '1 month');
+SELECT create_parent('partman_test.ts_taptest_table', 'col2', '1 month');
+
+-- Old values far from now, so they all go to the default table first
+INSERT INTO partman_test.date_taptest_table (col1, col2) VALUES
+ (1, '2002-06-15'), (2, '2002-07-01'), (3, '2002-07-15'), (4, '2002-08-01');
+INSERT INTO partman_test.ts_taptest_table (col1, col2) VALUES
+ (1, '2002-06-15 12:00:00'), (2, '2002-07-01 00:00:00'), (3, '2002-07-15 12:00:00'), (4, '2002-08-01 00:00:00');
+
+SELECT results_eq('SELECT partition_data_time(''partman_test.date_taptest_table'', p_batch_count := 10)::int', ARRAY[4], 'Check that partition_data_time moved all 4 rows of date_taptest_table');
+
+SELECT has_table('partman_test', 'date_taptest_table_p20020601', 'Check date_taptest_table_p20020601 exists');
+SELECT has_table('partman_test', 'date_taptest_table_p20020701', 'Check date_taptest_table_p20020701 exists');
+SELECT has_table('partman_test', 'date_taptest_table_p20020801', 'Check date_taptest_table_p20020801 exists');
+SELECT results_eq('SELECT col1 FROM partman_test.date_taptest_table_p20020601 ORDER BY col1', ARRAY[1], 'Check rows in date_taptest_table_p20020601');
+SELECT results_eq('SELECT col1 FROM partman_test.date_taptest_table_p20020701 ORDER BY col1', ARRAY[2,3], 'Check rows in date_taptest_table_p20020701');
+SELECT results_eq('SELECT col1 FROM partman_test.date_taptest_table_p20020801 ORDER BY col1', ARRAY[4], 'Check rows in date_taptest_table_p20020801');
+SELECT is_empty('SELECT * FROM partman_test.date_taptest_table_default', 'Check that date_taptest_table_default is empty');
+
+SELECT results_eq('SELECT partition_data_time(''partman_test.ts_taptest_table'', p_batch_count := 10)::int', ARRAY[4], 'Check that partition_data_time moved all 4 rows of ts_taptest_table');
+
+SELECT has_table('partman_test', 'ts_taptest_table_p20020701', 'Check ts_taptest_table_p20020701 exists');
+SELECT results_eq('SELECT col1 FROM partman_test.ts_taptest_table_p20020601 ORDER BY col1', ARRAY[1], 'Check rows in ts_taptest_table_p20020601');
+SELECT results_eq('SELECT col1 FROM partman_test.ts_taptest_table_p20020701 ORDER BY col1', ARRAY[2,3], 'Check rows in ts_taptest_table_p20020701');
+SELECT results_eq('SELECT col1 FROM partman_test.ts_taptest_table_p20020801 ORDER BY col1', ARRAY[4], 'Check rows in ts_taptest_table_p20020801');
+SELECT is_empty('SELECT * FROM partman_test.ts_taptest_table_default', 'Check that ts_taptest_table_default is empty');
+
+SELECT * FROM finish();
+ROLLBACK;
^ permalink raw [nested|flat] 5+ messages in thread
* Re: [pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text
@ 2026-11-10 14:29 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
3 siblings, 0 replies; 5+ messages in thread
From: keithf4 (@keithf4) @ 2026-11-10 14:29 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
I believe this should already be fixed in my update for 5.5.1 here: https://github.com/pgpartman/pg_partman/pull/883
Would you mind confirming as well? I think I missed that #868 was a dupe of #856
^ permalink raw [nested|flat] 5+ messages in thread
* Re: [pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text
@ 2026-11-10 15:55 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
3 siblings, 0 replies; 5+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-10 15:55 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Thanks for checking! You're right. I built the #883 branch and ran it on PostgreSQL 17:
- The repro script from #868 now puts all three rows in the right child tables, with the session time zone set to UTC and to America/Sao_Paulo.
- The test from this PR (date and timestamp control columns, `timezone = 'America/New_York'`) passes on #883 without any other change, and so does the rest of the top-level test suite.
Since #883 makes the same change, I'll close this PR. The only extra thing here is that the test runs in a non-UTC time zone. If you think that's useful, I'm happy to open a small PR with just that test after 5.5.1 is merged. Sorry I missed #856!
^ permalink raw [nested|flat] 5+ messages in thread
* Re: [pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text
@ 2026-11-10 15:56 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
3 siblings, 0 replies; 5+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-10 15:56 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@ritojoao closed this.
^ permalink raw [nested|flat] 5+ messages in thread
* Re: [pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text
@ 2026-11-10 16:04 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
3 siblings, 0 replies; 5+ messages in thread
From: keithf4 (@keithf4) @ 2026-11-10 16:04 UTC (permalink / raw)
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
@ritojoao Thank you for testing and confirming it works for you as well
Actually, yes that would be a good additional test set to have. Please feel free to do another PR with that test file and I can hopefully get it into the next release
^ permalink raw [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-11-10 16:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-10 13:47 [pgpartman/pg_partman] PR #887: Compare partition_data_time() bounds as timestamptz, not text "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-10 14:29 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-10 15:55 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-10 15:56 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-10 16:04 ` "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