From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@optionshouse.com
cc: Dave Johansen <davejohansen@gmail.com>, bricklen <bricklen@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Partitions not Working as Expected
In-reply-to: <51CC8160.2080905@optionshouse.com>
References: <51CC652F.3010304@optionshouse.com>
 <A76B25F2823E954C9E45E32FA49D70EC1BBD8EEA@mail.corp.perceptron.com>
 <51CC73B6.2050406@optionshouse.com>
 <CAGrpgQ_3pCLt3CJnap3z+nWe3-Q=A3sMH7OKP0sUeo79kViD8w@mail.gmail.com>
 <CAAcYxUfCaYbpAv-sQaNGKEVK2UZA1BtL87jM8LBd81ZomaLUuA@mail.gmail.com>
 <51CC8160.2080905@optionshouse.com>
Comments: In-reply-to Shaun Thomas <sthomas@optionshouse.com>
 message dated "Thu, 27 Jun 2013 13:16:00 -0500"
Date: Thu, 27 Jun 2013 14:42:26 -0400
Message-ID: <27533.1372358546@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Shaun Thomas <sthomas@optionshouse.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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance