Re: Partitions not Working as Expected

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux