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
The result of parse analysis for that query is a stored date constant
(in a Const node) with a cast-to-text on top of it. The system is aware
that cast-date-to-text isn't immutable, so it doesn't try to fold the
cast operation. When you execute the query, it displays the date
constant using the now-current datestyle.
> 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.
regards, tom lane