pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17525: Range partition by date won't allow the use of a date that is the upper bound
3+ messages / 3 participants
[nested] [flat]
* BUG #17525: Range partition by date won't allow the use of a date that is the upper bound
@ 2022-06-21 23:11 PG Bug reporting form <noreply@postgresql.org>
2022-06-21 23:31 ` RE: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound Allen Sutton <Allen.Sutton@valocityglobal.com>
2022-06-21 23:33 ` Re: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound Christophe Pettus <xof@thebuild.com>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2022-06-21 23:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: allen.sutton@valocityglobal.com
The following bug has been logged on the website:
Bug reference: 17525
Logged by: Allen Sutton
Email address: allen.sutton@valocityglobal.com
PostgreSQL version: 14.4
Operating system: Ubuntu 22.04LTS
Description:
I have a table that I have partitioned by a field est_date (date).
CREATE TABLE IF NOT EXISTS avm.avm_history_part
(
model_type character varying(4) COLLATE pg_catalog."default",
model_id integer,
est_date date NOT NULL,
address_id integer,
est integer,
conf numeric(8,4),
comps_used bigint,
last smallint DEFAULT 0,
est_id integer NOT NULL DEFAULT
nextval('avm.avm_history_part_est_id_seq1'::regclass),
est_smoothed integer,
dipid character varying(20) COLLATE pg_catalog."default",
fsd numeric(4,1)
) PARTITION BY RANGE (est_date);
The partitions of this table are created with a range of a month.
e.g. CREATE TABLE avm.avm_history_201704 PARTITION OF avm.avm_history_part
FOR VALUES FROM ('2017-04-01') TO ('2017-04-30');
This seems to be causing issues in two ways: -
1. When I prepare a table and then add it as a partition with a date range
and est_date in rows equal to the upper bound of the range, Postgres gives
me an error that it can't find a partition for the date
2. When I try update a row and set the value of est_date to the upper range,
I get an update error such as
ERROR: new row for relation "avm_history_201704" violates partition
constraint
DETAIL: Failing row contains (A, 11, 2017-04-30, 46846, 1270000, 0.2647, 7,
0, 26563523, 1246000, NZ82047105, 39.9).
SQL state: 23514
I need assistance to work out what I have done wrong if anything and how to
resolve this issue
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound
2022-06-21 23:11 BUG #17525: Range partition by date won't allow the use of a date that is the upper bound PG Bug reporting form <noreply@postgresql.org>
@ 2022-06-21 23:31 ` Allen Sutton <Allen.Sutton@valocityglobal.com>
1 sibling, 0 replies; 3+ messages in thread
From: Allen Sutton @ 2022-06-21 23:31 UTC (permalink / raw)
To: Allen Sutton <Allen.Sutton@valocityglobal.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
I have found the secret! I need to specify the upper bound to be the 1st of the next month as the partition constraint is a < not <= check
So my former definition
partition=> \d+ avm.avm_history_201704
Table "avm.avm_history_201704"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+-----------------------+-----------+----------+-------------------------------------------------------+----------+-------------+--------------+-------------
model_type | character varying(4) | | | | extended | | |
model_id | integer | | | | plain | | |
est_date | date | | not null | | plain | | |
address_id | integer | | | | plain | | |
est | integer | | | | plain | | |
conf | numeric(8,4) | | | | main | | |
comps_used | bigint | | | | plain | | |
last | smallint | | | 0 | plain | | |
est_id | integer | | not null | nextval('avm.avm_history_part_est_id_seq1'::regclass) | plain | | |
est_smoothed | integer | | | | plain | | |
dipid | character varying(20) | | | | extended | | |
fsd | numeric(4,1) | | | | main | | |
Partition of: avm.avm_history_part FOR VALUES FROM ('2017-04-01') TO ('2017-04-30')
Partition constraint: ((est_date IS NOT NULL) AND (est_date >= '2017-04-01'::date) AND (est_date < '2017-04-30'::date))
Indexes:
"avm_history_201704_est_date_idx" btree (est_date)
"avm_history_201704_est_date_idx1" btree (est_date)
"avm_history_201704_est_id_est_date_idx" UNIQUE, btree (est_id, est_date)
"avm_history_201704_model_id_est_date_dipid_idx" UNIQUE, btree (model_id, est_date, dipid)
Access method: heap
Becomes
partition=> \d+ avm.avm_history_201704
Table "avm.avm_history_201704"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+-----------------------+-----------+----------+-------------------------------------------------------+----------+-------------+--------------+-------------
model_type | character varying(4) | | | | extended | | |
model_id | integer | | | | plain | | |
est_date | date | | not null | | plain | | |
address_id | integer | | | | plain | | |
est | integer | | | | plain | | |
conf | numeric(8,4) | | | | main | | |
comps_used | bigint | | | | plain | | |
last | smallint | | | 0 | plain | | |
est_id | integer | | not null | nextval('avm.avm_history_part_est_id_seq1'::regclass) | plain | | |
est_smoothed | integer | | | | plain | | |
dipid | character varying(20) | | | | extended | | |
fsd | numeric(4,1) | | | | main | | |
Partition of: avm.avm_history_part FOR VALUES FROM ('2017-04-01') TO ('2017-05-01')
Partition constraint: ((est_date IS NOT NULL) AND (est_date >= '2017-04-01'::date) AND (est_date < '2017-05-01'::date)) <----- This will work
Indexes:
"avm_history_201704_est_date_idx" btree (est_date)
"avm_history_201704_est_date_idx1" btree (est_date)
"avm_history_201704_est_id_est_date_idx" UNIQUE, btree (est_id, est_date)
"avm_history_201704_model_id_est_date_dipid_idx" UNIQUE, btree (model_id, est_date, dipid)
Access method: heap
Please close this bug.
Regards,
Allen Sutton
Allen Sutton
Senior Data Analyst
m: +64 27 272 8886 w: valocityglobal.com
Wellington, New Zealand
The material in this email is confidential to the person or organisation to whom it is addressed and may be protected by legal privilege. If you are not the intended recipient of this email can you please notify the sender by return immediately then delete this email and any copies made. Communications sent by email can be corrupted or intercepted by third parties. For this reason Valocity Limited does not accept any responsibility for any breach of confidence arising through use of this medium.
-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org>
Sent: Wednesday, 22 June 2022 11:12 am
To: pgsql-bugs@lists.postgresql.org
Cc: Allen Sutton <Allen.Sutton@valocityglobal.com>
Subject: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound
Caution: This email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. When in doubt, contact your IT Department.
The following bug has been logged on the website:
Bug reference: 17525
Logged by: Allen Sutton
Email address: allen.sutton@valocityglobal.com
PostgreSQL version: 14.4
Operating system: Ubuntu 22.04LTS
Description:
I have a table that I have partitioned by a field est_date (date).
CREATE TABLE IF NOT EXISTS avm.avm_history_part (
model_type character varying(4) COLLATE pg_catalog."default",
model_id integer,
est_date date NOT NULL,
address_id integer,
est integer,
conf numeric(8,4),
comps_used bigint,
last smallint DEFAULT 0,
est_id integer NOT NULL DEFAULT
nextval('avm.avm_history_part_est_id_seq1'::regclass),
est_smoothed integer,
dipid character varying(20) COLLATE pg_catalog."default",
fsd numeric(4,1)
) PARTITION BY RANGE (est_date);
The partitions of this table are created with a range of a month.
e.g. CREATE TABLE avm.avm_history_201704 PARTITION OF avm.avm_history_part
FOR VALUES FROM ('2017-04-01') TO ('2017-04-30');
This seems to be causing issues in two ways: - 1. When I prepare a table and then add it as a partition with a date range and est_date in rows equal to the upper bound of the range, Postgres gives me an error that it can't find a partition for the date 2. When I try update a row and set the value of est_date to the upper range, I get an update error such as
ERROR: new row for relation "avm_history_201704" violates partition constraint
DETAIL: Failing row contains (A, 11, 2017-04-30, 46846, 1270000, 0.2647, 7, 0, 26563523, 1246000, NZ82047105, 39.9).
SQL state: 23514
I need assistance to work out what I have done wrong if anything and how to resolve this issue
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound
2022-06-21 23:11 BUG #17525: Range partition by date won't allow the use of a date that is the upper bound PG Bug reporting form <noreply@postgresql.org>
@ 2022-06-21 23:33 ` Christophe Pettus <xof@thebuild.com>
1 sibling, 0 replies; 3+ messages in thread
From: Christophe Pettus @ 2022-06-21 23:33 UTC (permalink / raw)
To: allen.sutton@valocityglobal.com; pgsql-bugs@lists.postgresql.org
> On Jun 21, 2022, at 16:11, PG Bug reporting form <noreply@postgresql.org> wrote:
> e.g. CREATE TABLE avm.avm_history_201704 PARTITION OF avm.avm_history_part
> FOR VALUES FROM ('2017-04-01') TO ('2017-04-30');
The upper bound on a range partition is exclusive: The partition is defined as from 2017-04-01 up to, but not including 2017-04-30. If you want full-month partitions, you probably want the upper bound to be 2017-05-01 (in this example).
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-06-21 23:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-21 23:11 BUG #17525: Range partition by date won't allow the use of a date that is the upper bound PG Bug reporting form <noreply@postgresql.org>
2022-06-21 23:31 ` Allen Sutton <Allen.Sutton@valocityglobal.com>
2022-06-21 23:33 ` Christophe Pettus <xof@thebuild.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