There was a recent question on pgsql-sql (not visible in the archives
yet, but look for title "Very strange 'now' behaviour" of today's date)
about misbehavior of a 'now' default for a timestamp column. This makes
me wonder whether the following bit of hackery in catalog/heap.c is
really as good an idea as it seemed when we put it in:
/* * Check that it will be possible to coerce the expression to the * column's type. We store the expression
withoutcoercion, however, * to avoid premature coercion in cases like * * CREATE TABLE tbl (fld timestamp
DEFAULT'now'); */
If we went ahead and stored the coerced expression, it would make no
difference for cases with non-constant defaults (such as "now()"). It
would make a difference for the above case and close relatives, in which
the datatype's input routine is context-sensitive and can convert the
same string to different values at different times. The conversion
would occur at CREATE TABLE (or ALTER TABLE SET DEFAULT) time, not at
rewrite time as it does now.
The existing behavior is a convenient hack that works nicely for
interactive commands, because they are parsed and immediately executed,
so it appears that the default is getting evaluated at runtime.
However, it fails miserably if the default is invoked via a prepared
statement, or in plpgsql or another language that does plan caching.
The default will be evaluated at planning time and then re-used later,
leading to mysterious behavior (as in the above complaint).
If we coerced the constant at table definition time, then it would be
fairly obvious what had gone wrong ("\d table" would show the actually
applied value) and people would be forced to define their defaults in
a way that's bulletproof, rather than allowing a way that works
most of the time and then bites you when you aren't expecting it.
Since 7.4's new FE protocol encourages use of prepared statements,
I think that we are going to see this kind of problem more and more
frequently if we don't change the behavior.
Comments?
regards, tom lane
