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 17:44:09 -0400
Message-ID: <1303.1372369449@sss.pgh.pa.us> (raw)
In-Reply-To: <51CC8F1A.1090805@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>
<27533.1372358546@sss.pgh.pa.us>
<51CC8F1A.1090805@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Shaun Thomas <sthomas@optionshouse.com> writes:
> 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
Yeah, that is what the parser does with it. The way to read that is
"a constant of type text, containing the string 'now', to which is
applied a run-time coercion to type date". The run-time coercion is
equivalent to (and implemented by) calling text_out then date_in.
If date_in is marked immutable, then the planner will correctly conclude
that it can fold the whole thing to a date constant on sight. Now you
have a plan with a hard-wired value for the current date, which will
begin to give wrong answers after midnight passes. If your usage
pattern is such that no query plan survives across a day boundary,
you might not notice ... but it's still wrong.
> ... What's interesting is that EnterpriseDB has
> their own pg_catalog.current_date function that gets called by the
> CURRENT_DATE keyword.
Yeah, we really ought to do likewise in the community code. But that
doesn't affect the fundamental semantic issue here, which is that you
can't mark the expression immutable without creating incorrect cached
plans.
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: <1303.1372369449@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