From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Status of 'now' column defaults |
Date: | 1999-10-04 15:11:02 |
Message-ID: | 2324.939049862@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 1999 EDT
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
From | Date | Subject | |
---|---|---|---|
Next Message | G. Anthony Reina | 1999-10-04 18:59:32 | MATLAB mex file for PostgreSQL |
Previous Message | Peter Eisentraut | 1999-10-04 14:03:34 | Database names with spaces |