From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@optionshouse.com
Cc: Dave Johansen <davejohansen@gmail.com>
Cc: bricklen <bricklen@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Partitions not Working as Expected
Date: Thu, 27 Jun 2013 14:42:26 -0400
Message-ID: <27533.1372358546@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sthomas@optionshouse.com, davejohansen@gmail.com, bricklen@gmail.com
Subject: Re: Partitions not Working as Expected
In-Reply-To: <27533.1372358546@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox