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@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email