From: | Alan Jackson <ajax(at)tvsquared(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | inconsistent results querying table partitioned by date |
Date: | 2019-05-09 16:45:55 |
Message-ID: | FAD28A83-AC73-489E-A058-2681FA31D648@tvsquared.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi
Im having a problem with querying a table partitioned by date.
Depending on the sequence of operations on a date parameter used in a where clause, I either get the expected results, or no results.
This suggests a bug in the handling of date parameters and partition range handling.
I’ve replicated this down to a single sequence of create table, insert data, query.
This issue occurs for me on postgresql 11.2 on a mac, installed via brew.
In this case the table is partitioned by an id and then by the date, if it is partitioned by only the date everything works as expected.
However, I am attempting to add partitioning to a fairly large sofware-as-a-service platform, so making changes to the table definitions or global code changes is not really practical.
The sql in question is below.
I hope there is something simple I can change in the partition definitions to work around this.
Many Thanks,
Alan Jackson
Data Architect
TVSquared
--SQL STARTS HERE
--drop table dataid;
CREATE TABLE dataid
(
id integer not null,
datadatetime timestamp without time zone NOT NULL,
CONSTRAINT dataid_pkey PRIMARY KEY (id, datadatetime)
) PARTITION BY RANGE (id, datadatetime)
;
CREATE TABLE dataid_201902 PARTITION OF dataid FOR VALUES FROM (1, '2019-02-01 00:00:00') TO (1, '2019-03-01 00:00:00');
CREATE TABLE dataid_default PARTITION OF dataid DEFAULT;
insert into dataid values (1,'2019-02-24T00:00:00');
--- returns 1 row as expected
select * from dataid where id=1 and datadatetime < (('2019-02-26T00:00:00'::timestamp::timestamp at time zone 'America/New_York' ) at time zone 'UTC' + '2 days'::interval);
--- returns no rows
select * from dataid where id=1 and datadatetime < (('2019-02-26T00:00:00'::timestamp::timestamp at time zone 'America/New_York' + '2 days'::interval) at time zone 'UTC');
-- both date expressions evaluate to the same date.
select
(('2019-02-26T00:00:00'::timestamp::timestamp at time zone 'America/New_York' ) at time zone 'UTC' + '2 days'::interval) as workingdate,
(('2019-02-26T00:00:00'::timestamp::timestamp at time zone 'America/New_York' + '2 days'::interval) at time zone 'UTC') as notworkingdate;
--SQL ENDS HERE
--
TV Squared Limited is a company registered in Scotland. Registered number:
SC421072. Registered office: CodeBase, Argyle House, 3 Lady Lawson Street,
Edinburgh, EH3 9DR.
TV Squared Inc (File No. 5600204) is an Incorporated
company registered in Delaware. Principal office: 1412 Broadway, 22 Fl, New
York, New York, 10018
TV Squared GmbH is a company registered in Munich.
Registered number: HRB 236077. Registered office: Oskar-von-Miller-Ring 20,
c/o wework, 80333 Munchen
This message is private and confidential. If
you have received this message in error, please notify us and remove it
from your system.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-05-09 19:24:20 | Re: BUG #15795: ERROR: could not find pathkey item to sort |
Previous Message | Tom Lane | 2019-05-09 16:06:18 | Re: BUG #15795: ERROR: could not find pathkey item to sort |