Thread

1. Bug with views and defaults

   Mark Hollomon <mhh@nortelnetworks.com> — 2000-06-15T14:05:10Z

   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');
   
   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-04
    n2   | 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
   
   

2. Re: Bug with views and defaults

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-16T23:43:10Z

   "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
   
   

3. Re: Bug with views and defaults

   Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-17T00:06:49Z

   > 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, Pennsylvania 19026