Re: Partitions not Working as Expected

Shaun Thomas <sthomas(at)optionshouse(dot)com> writes:
> On 06/27/2013 12:42 PM, Dave Johansen wrote:
>> Or what about something like DATE_TRUNC("DAY", now())? Or would that run
>> into the same optimization/planner problems as CURRENT_DATE?

> Same issue. This seems to work, though I'm not entirely sure of the
> implications:

> UPDATE pg_proc
> SET provolatile = 'i'
> WHERE proname = 'date_in';

That will break things: CURRENT_DATE will then be equivalent to just
writing today's date as a literal.

It's conceivable that it wouldn't break any scenario that you personally
care about, if you never use CURRENT_DATE in any view, rule, column
default expression, or cached plan; but it seems mighty risky from here.

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 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. So I'm not feeling
motivated to try to hack some solution for this case into the theorem
prover.

Unfortunately, it's likely to be awhile before that next-generation
partitioning code shows up. But major extensions to the proof engine
wouldn't be a weekend project, either...

regards, tom lane