Thread: 'iscachable' only partially solves premature constant coercion

I have finished installing the code changes associated with marking
functions 'iscachable' or not.  I had hoped that this change would
eliminate the problems we have with premature coercion of datetime
constants in column defaults and rules.  It turns out it doesn't :-(.
That's because there isn't any good way to postpone the evaluation of
a typinput function.  Since the argument of a typinput function is a
null-terminated C string, and not a 'text' or any other full-fledged
Postgres type, there is no way to construct an expression tree
representing runtime evaluation of the typinput function.  So, even
though the system knows it shouldn't evaluate the typinput function
before runtime, it has little choice.

We have talked about making 'C string' a genuine Postgres type, at
least to the extent of giving it an OID and making it representable
as a Const node.  If we did that then we could represent a typinput
function call by an expression tree and make this problem go away.
I'm not going to tackle that right now, though, since there are
higher-priority problems to deal with.

The current state of affairs is that if you write a constant of UNKNOWN
type (ie, an unadorned quoted constant), it'll get coerced to the
destination type just as soon as the system can figure out what that
type is.  So, it's still necessary to write "'now'::text" (or one of the
other syntaxes for type-casting) or "now()" as the default value for
a datetime column --- if you write unadorned 'now' then you will get
the time of table creation, same as before.

I am about to rip out and redo the crufty implementation of default and
constraint expressions, and I think that I can arrange for UNKNOWN
constants to remain UNKNOWN when they are stored into the pg_attrdef
table.  This would mean that what gets into pg_attrdef is just the
unadorned string 'now', and then the coercion of this to a particular
timestamp will occur when an INSERT statement that uses the default
is parsed.  So the right thing (approximately, anyway) should happen for
a typical run-of-the-mill INSERT.  The wrong thing will still happen
for an INSERT written in a rule --- its default will be established when
the rule is created.
        regards, tom lane

I wrote:
> I am about to rip out and redo the crufty implementation of default and
> constraint expressions, and I think that I can arrange for UNKNOWN
> constants to remain UNKNOWN when they are stored into the pg_attrdef
> table.  This would mean that what gets into pg_attrdef is just the
> unadorned string 'now', and then the coercion of this to a particular
> timestamp will occur when an INSERT statement that uses the default
> is parsed.  So the right thing (approximately, anyway) should happen for
> a typical run-of-the-mill INSERT.  The wrong thing will still happen
> for an INSERT written in a rule --- its default will be established when
> the rule is created.

I did this, and that's how it works now.  Unless we choose to do
something about making C strings and typinput functions fit into the
Postgres type scheme, that's how it will continue to work.

To summarize: in current sources, "default 'now'" works as expected in
simple cases:

play=> create table valplustimestamp (val int, stamp datetime default 'now');
CREATE
play=> insert into valplustimestamp values(1);
INSERT 653323 1
play=> insert into valplustimestamp values(2);
INSERT 653324 1
play=> select * from valplustimestamp;
val|stamp
---+---------------------------- 1|Mon Oct 04 10:58:47 1999 EDT 2|Mon Oct 04 10:58:49 1999 EDT
(2 rows)

but it still has a subtle failure mode:

play=> create view val as select val from valplustimestamp;
CREATE
play=> create rule val_ins as on insert to val do instead
play-> insert into valplustimestamp values(new.val);
CREATE
play=> insert into val values(3);
INSERT 653336 1
play=> insert into val values(4);
INSERT 653337 1
play=> select * from valplustimestamp;
val|stamp
---+---------------------------- 1|Mon Oct 04 10:58:47 1999 EDT 2|Mon Oct 04 10:58:49 1999 EDT 3|Mon Oct 04 10:59:48
1999EDT 4|Mon Oct 04 10:59:48 1999 EDT
 
(4 rows)

The default value inserted by the rule got frozen when the rule was
parsed, as can be seen by inspecting the back-parsing of the rule:

play=> select * from pg_rules;
tablename|rulename|definition

---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------
val      |val_ins |CREATE RULE val_ins AS ON INSERT TO val DO INSTEAD INSERT INTO valplustimestamp (val, stamp) VALUES
(new.val,'Mon Oct 04 10:59:48 1999 EDT'::datetime);
 
(1 row)


So, we should still recommend "DEFAULT now()" rather than "DEFAULT 'now'"
as the most reliable way of setting up a current-time default.
        regards, tom lane