On Wed, Nov 30, 2011 at 08:10:22PM -0500, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > SQL:
> > set datestyle to postgres,us;
> > prepare stmt as select '02-01-2011'::date::text;
> > execute stmt;
> > set datestyle to postgres,euro;
> > execute stmt;
> > deallocate stmt;
>
> > The results I get with normal debug compilation are:
>
> > SET
> > PREPARE
> > text
> > ------------
> > 02-01-2011
> > (1 row)
>
> > SET
> > text
> > ------------
> > 01-02-2011
> > (1 row)
>
> > DEALLOCATE
> > But with -DCLOBBER_CACHE_ALWAYS and -DRELCACHE_FORCE_RELEASE, I get:
>
> > SET
> > PREPARE
> > text
> > ------------
> > 02-01-2011
> > (1 row)
>
> > SET
> > text
> > ------------
> > 02-01-2011
> > (1 row)
>
> > DEALLOCATE
>
> > Which one of those results is correct?
>
> I believe what is happening in the second case is that the query is
> getting re-parse-analyzed, from scratch, and since now datestyle is
> different (DMY not MDY), the date literal gets interpreted differently.
> You could argue it either way as to which result is "more correct",
> but I doubt we're going to try to do something about that. Best advice
> is to avoid ambiguous input, or if you can't, at least avoid flipping
> your datestyle on the fly.
One could defend consistent use of either the PREPARE-time DateStyle or the
EXECUTE-time DateStyle to interpret literals. However, using the value as of
the last RevalidateCachedQuery(), its timing independent of any GUC change, is
an implementation artifact with no redeeming value for the user.
This hazard also arises around IntervalStyle, TimeZone, sql_inheritance,
transform_null_equals, and array_nulls.
Implementation challenges aside, I'd contend for always using PREPARE-time
values during parse analysis. That's more consistent with the user-visible
consequences of changing search_path or standard_conforming_strings. That
said, I don't have in mind a cure clearly less ugly than the disease.
nm
