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@xxxxxxxxxxxxxxxx
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
