Thread: Bug with views and defaults

CREATE TABLE foo (name TEXT,type CHAR(1),when_added TIMESTAMP DEFAULT 'now'
);

CREATE VIEW mytype AS SELECT name, when_added FROM foo WHERE type = 'M';

CREATE RULE mytype_insert ASON INSERT TO mytype DO INSTEADINSERT INTO foo (name, type) VALUES (NEW.name, 'M');

db=# insert into foo (name, type) VALUES ('n1', 'M');
INSERT 414488 1
db=# insert into mytype (name) VALUES ('n2');
INSERT 414489 1
db=# select * from foo;name | type |       when_added
------+------+------------------------n1   | M    | 2000-06-15 09:53:44-04n2   | M    | 2000-06-15 09:52:27-04
(2 rows)

Inserting directly into foo sets when_added to the current time.
Inserting through the view sets it to what looks like the time of
view creation.

-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

"Mark Hollomon" <mhh@nortelnetworks.com> writes:
> CREATE TABLE foo (
>     name TEXT,
>     type CHAR(1),
>     when_added TIMESTAMP DEFAULT 'now'
> );

> CREATE VIEW mytype AS 
>     SELECT name, when_added FROM foo WHERE type = 'M';

> CREATE RULE mytype_insert AS
>     ON INSERT TO mytype DO INSTEAD
>     INSERT INTO foo (name, type) VALUES (NEW.name, 'M');

> Inserting directly into foo sets when_added to the current time.
> Inserting through the view sets it to what looks like the time of
> view creation.

This is a known and not readily fixable problem.  It's far safer
to write the default for a timestamp column as now(), rather than
relying on a string literal not getting coerced to timestamp form
too soon.  See
http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00036.html

BTW, Bruce: it probably would be wise to have the FAQ's item 4.22
recommend now() and nothing else.  'now' has nothing much to recommend
it and there are still pitfalls like this one.
        regards, tom lane

> This is a known and not readily fixable problem.  It's far safer
> to write the default for a timestamp column as now(), rather than
> relying on a string literal not getting coerced to timestamp form
> too soon.  See
> http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00036.html
> 
> BTW, Bruce: it probably would be wise to have the FAQ's item 4.22
> recommend now() and nothing else.  'now' has nothing much to recommend
> it and there are still pitfalls like this one.
> 
>             regards, tom lane
> 

TODO updated.


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026