RE: Help with list partitioning on expression

Thank you! Thank you! Thank you!

I would not have figured it out, but solution is so simple.

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

________________________________
From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Sent: Sunday, October 21, 2018 6:50:47 PM
To: Dinko Papak
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Help with list partitioning on expression

On 19 October 2018 at 02:49, Dinko Papak <rimokatolik(at)outlook(dot)com> wrote:
> I have created table partitioned by list on expression using timestamp
> column, so for each timestamp value function returns int and each partition
> table is for single value (range of timestamp for which function result is
> the same). This helps me to simplify querying as I do not need to always
> write date ranges. Querying (partition pruning) works nice, however when I
> attach new partition it seems to always scan whole table, although I do have
> necessary check constraint on partitioned table. I have tried to make
> timestamp column both null and not null. Also, it takes longer to attach
> partition then to add constraint itself although per my understanding those
> 2 operations should do the same scan.

It's not all that obvious, but if you have PARTITION BY LIST
(extract(minute FROM ts)) and try to attach a partition like:

CREATE TABLE mypartition PARTITION OF parted FOR VALUES IN (1);

then the partition constraint is actually (extract(minute FROM ts) IS
NOT NULL AND extract(minute FROM ts) = 1). If your CHECK constraint is
just checking `extract(minute FROM ts) = 1` then the current code in
PartConstraintImpliedByRelConstraint() is not smart enough to know
that `extract(minute FROM ts) = 1` is strict and cannot match nulls.
Perhaps that could be improved, but that's how it is today.

Likely you'll have better luck with a check constraint that explicitly
checks the function IS NOT NULL.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services