pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgpartman/pg_partman] PR #889: Add test for monthly boundary with non-UTC timezone (#868)
4+ messages / 2 participants
[nested] [flat]

* [pgpartman/pg_partman] PR #889: Add test for monthly boundary with non-UTC timezone (#868)
@ 2026-11-10 22:32 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
  0 siblings, 0 replies; 4+ messages in thread

From: ritojoao (@ritojoao) @ 2026-11-10 22:32 UTC (permalink / raw)
  To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>

As requested in #887, this PR only adds the test, separate from the fix in #883.

`test/test-time-date-monthly-boundary.sql` runs `partition_data_time()` with a `date` and a `timestamp without time zone` control column, under a non-UTC session time zone. It checks that rows on the first day of a month go to that month's child table and that the default partition ends up empty (see #838 and #868).

Tested locally on PostgreSQL 17:
- On `development` (without #883), the test fails with `new row for relation "date_taptest_table_p20020601" violates partition constraint` for the row `2002-07-01`.
- With #883 applied, the new test passes (14/14), and the full top-level suite passes (35 files, 4677 tests).

This should be merged after #883, since it fails on `development` until then.


inline patch 1: test/test-time-date-monthly-boundary.sql (+55/-0 lines) download inline-1.patchdiff --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] 4+ messages in thread

* Re: [pgpartman/pg_partman] PR #889 (approved): Add test for monthly boundary with non-UTC timezone (#868)
@ 2026-11-17 17:18 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  2 siblings, 0 replies; 4+ messages in thread

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



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

* Re: [pgpartman/pg_partman] PR #889: Add test for monthly boundary with non-UTC timezone (#868)
@ 2026-11-17 17:59 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  2 siblings, 0 replies; 4+ messages in thread

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

@keithf4 merged this (commit 63a9fcae6bf6).

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

* Re: [pgpartman/pg_partman] PR #889: Add test for monthly boundary with non-UTC timezone (#868)
@ 2026-11-17 17:59 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
  2 siblings, 0 replies; 4+ messages in thread

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

@keithf4 closed this.

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


end of thread, other threads:[~2026-11-17 17:59 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-10 22:32 [pgpartman/pg_partman] PR #889: Add test for monthly boundary with non-UTC timezone (#868) "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-17 17:18 ` Re: [pgpartman/pg_partman] PR #889 (approved): Add test for monthly boundary with non-UTC timezone (#868) "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-17 17:59 ` "keithf4 (@keithf4)" <noreply+keithf4@github.com>
2026-11-17 17:59 ` "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