From: | Shaun Thomas <sthomas(at)optionshouse(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Dave Johansen <davejohansen(at)gmail(dot)com>, bricklen <bricklen(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Partitions not Working as Expected |
Date: | 2013-06-27 19:14:34 |
Message-ID: | 51CC8F1A.1090805@optionshouse.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 06/27/2013 01:42 PM, Tom Lane wrote:
> That will break things: CURRENT_DATE will then be equivalent to just
> writing today's date as a literal.
Interesting. I tested it by creating a view and a table with a default,
and it always seems to get translated to:
('now'::text)::date
But I'll take your explanation at face value, since that doesn't imply
what the output would be. What's interesting is that EnterpriseDB has
their own pg_catalog.current_date function that gets called by the
CURRENT_DATE keyword. So unlike in vanilla PG, I could mark just the
current_date function as immutable without affecting a lot of other
internals.
On EDB, this actually works:
UPDATE pg_proc
SET provolatile = 'i'
WHERE proname = 'current_date';
Then the plan gets pared down as desired. But again, if the date were to
roll over, I'm not sure what would happen. I wish I could test that
without fiddling with machine times.
> I don't see any very good solution to your problem within the current
> approach to partitioning, which is basically theorem-proving. That
> proof engine has no concept of time passing, let alone the sort of
> detailed knowledge of the semantics of this particular function that
> would allow it to conclude "if CURRENT_DATE > '2013-06-20' is true now,
> it will always be so in the future as well".
I get it. From the context of two months ago, CURRENT_DATE >
'2013-06-20' would return a different answer than it would today, which
isn't really good for proofs.
The only way for it to work as "expected" would be to add a first pass
to resolve any immediate variables, which would effectively throw away
plan caches. I'd actually be OK with that.
> I think most hackers agree that the way forward on partitioning
> involves building hard-wired logic that selects the correct
> partition(s) at run-time, so that it wouldn't particularly matter
> where we got the comparison value from or whether it was a constant.
Fair enough. I'll stop telling devs to use current_date instead of ORM
injections, then. Hopefully we can track down and tweak the affected
queries on the tables we're partitioning without too much work and QA.
Thanks, Tom!
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
From | Date | Subject | |
---|---|---|---|
Next Message | Shaun Thomas | 2013-06-27 19:17:40 | Re: Partitions not Working as Expected |
Previous Message | Albin, Lloyd P | 2013-06-27 18:45:09 | Re: Partitions not Working as Expected |