Re: [HACKERS] Why DEFAULT text 'now' does not work for TIMESTAMP columns

> Per Leon's recent gripe in the bugs list, I have confirmed that
> create table t1 (f1 int4, f2 timestamp default text 'now');
> does *not* work as expected --- the stored constraint expression
> has been pre-reduced to a timestamp constant, even though you
> get the desired behavior with
> create table t1 (f1 int4, f2 datetime default text 'now');
> I have tracked down the cause of this, and it's a mess.

Yes. We've had lots of small "improvements" in the code (there is
blood on my hands :) which danced around a fundamental problem: it
would be nice to pre-evaluate functions on constants, but there are a
few functions/constants (e.g. "random" or 'now') which shouldn't be
done this way. Functions and types really should have an "is cachable"
attribute so that they can be pre-evaluated when possible.

> First off, there is a pg_proc entry for converting a text value
> to datetime (proc text_datetime, OID 1351) but there is no similar
> function for timestamp.  Therefore, the parser's can_coerce_type
> function returns "true" if asked whether it can coerce text to
> datetime, but "false" for text to timestamp.

At the moment, timestamp serves the useful purpose of illustrating how
annoying a partially implemented and poorly supported feature can be.
I've been putting off relabeling "datetime" and "timespan" as
"timestamp" and "interval", thinking that it should wait for the major
rev bump to 7.0. But it really shouldn't wait. This would align the
best types in the date/time code with SQL-standard names. The original
timestamp and interval code would be killed. That doesn't fix the
underlying problems handling defaults, but would stop most complaints
about timestamp...

> Second, the actual storage of the constraint expression is being
> done through an incredibly klugy series of hacks.  After the grammar
> parses the constraint expression, the expression is converted back
> to text form (!) instead of being output as a parsetree.

Yeah, well, originally it was just passed through as a string, but the
parser couldn't validate the syntax under those circumstances. So I
had the parser tokenize it, and then reassemble the string. But
apparently I didn't try very hard to reassemble it correctly.

> This code makes me ill to look at ... 

You should know by now that Postgres internals aren't for a weak
stomach ;)

> I am not sure what should be done to clean this up.  A brute-force
> solution would be to make sure that there is a text-to-whatever
> conversion function in pg_proc for any type where the type input
> function is not necessarily a constant --- but I'm not sure which
> types besides timestamp might meet that description.  In any case
> I do not care for the code in StoreAttrDefault at all.
> 
> I am about to commit parser fixes that ensure a DEFAULT value is
> correctly coerced to the column type when it is used (that is,
> transformInsertStatement now does a coerce_type rather than just
> assuming what is in pg_attrdef is the right type).  So, one possible
> approach is to remove the coercion code from StoreAttrDefault
> altogether.  That would mean that
>                 field1 datetime 'now'
> would start acting the same as
>                 field1 datetime text 'now'
> currently does: both of them would be coerced to datetime at runtime,
> not when the constraint expression is created.  Given the frequency
> with which newbies complain about the current behavior, I think that
> that might be a Good Thing.  But it would be a change in behavior,
> and I suppose there are scenarios where you'd like to be able to get
> the old behavior.

Sorry, how does that change behavior for the worse? I can see it
taking a performance hit, but under which circumstances would runtime
evaluation be counter-intuitive or wrong?

And while you are being annoyed by code, how about looking at problems
with trying to use indices on constants and on functions calls? I've
assumed that it could benefit from a judicious application of
coerce_type...
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California