Обсуждение: BUG #1598: using default 'now', or now() or CURRENT_TIMESTAMP when creating table does not work as expected

The following bug has been logged online:

Bug reference:      1598
Logged by:          Nicolas HAHN
Email address:      hahnn@x-itools.com
PostgreSQL version: 7.4.7
Operating system:   Fedora Core 3
Description:        using default 'now', or now() or CURRENT_TIMESTAMP when
creating table does not work as expected
Details:

I read the doc and saw that, to avoid the problem of using a default 'now'
when creating the table which makes each record to have the timestamp of
when the table has been created, default now() or default CURRENT_TIMESTAMP
must be used instead. But using this is exactly the same as using 'now': I
still get the same timestamp for each record I insert in the table.

Here is what I did and the results, in the 3 cases:

**********************
case 1: using default 'now', which works as expected according the doc:

BEGIN;
CREATE TABLE test (date timestamp not null default 'now', val varchar(64));
INSERT INTO test (val) values ('test 1 at time T');
########### Here I wait 1 minute #############
INSERT INTO test (val) values ('test 2 at time T+1 min');
SELECT * from test;
            date            |      val
----------------------------+------------------------
 2005-04-15 13:47:38.796248 | test 1 at time T
 2005-04-15 13:47:38.796248 | test 2 at time T+1 min
(2 rows)
ROLLBACK;


**********************
case 2: using default now(), which doesn't work as expected according the
doc:

BEGIN;
CREATE TABLE test (date timestamp not null default now(), val varchar(64));
INSERT INTO test (val) values ('test 1 at time T');
########### Here I wait 1 minute #############
INSERT INTO test (val) values ('test 2 at time T+1 min');
SELECT * from test;
            date            |      val
----------------------------+------------------------
 2005-04-15 13:50:53.419981 | test 1 at time T
 2005-04-15 13:50:53.419981 | test 2 at time T+1 min
(2 rows)
ROLLBACK;


**********************
case 3: using default CURRENT_TIMESTAMP, which doesn't work as expected
according the doc:

BEGIN;
CREATE TABLE test (date timestamp not null default CURRENT_TIMESTAMP, val
varchar(64));
INSERT INTO test (val) values ('test 1 at time T');
########### Here I wait 1 minute #############
INSERT INTO test (val) values ('test 2 at time T+1 min');
SELECT * from test;
            date            |      val
----------------------------+------------------------
 2005-04-15 13:54:17.136511 | test 1 at time T
 2005-04-15 13:54:17.136511 | test 2 at time T+1 min
(2 rows)
ROLLBACK;

On Fri, 15 Apr 2005, Nicolas HAHN wrote:

>
> The following bug has been logged online:
>
> Bug reference:      1598
> Logged by:          Nicolas HAHN
> Email address:      hahnn@x-itools.com
> PostgreSQL version: 7.4.7
> Operating system:   Fedora Core 3
> Description:        using default 'now', or now() or CURRENT_TIMESTAMP when
> creating table does not work as expected
> Details:
>
> I read the doc and saw that, to avoid the problem of using a default 'now'
> when creating the table which makes each record to have the timestamp of
> when the table has been created, default now() or default CURRENT_TIMESTAMP
> must be used instead. But using this is exactly the same as using 'now': I
> still get the same timestamp for each record I insert in the table.

now() is transaction start time and as such isn't going to vary within one
transaction, no matter how long you wait.  The issue is what happens when
you commit the first transaction and start another and insert and whether
you get the transaction start time of the first transaction or the second.

Nicolas HAHN wrote:
> The following bug has been logged online:
>
> Bug reference:      1598
> Logged by:          Nicolas HAHN
> Email address:      hahnn@x-itools.com
> PostgreSQL version: 7.4.7
> Operating system:   Fedora Core 3
> Description:        using default 'now', or now() or CURRENT_TIMESTAMP when
> creating table does not work as expected
> Details:

> **********************
> case 2: using default now(), which doesn't work as expected according the
> doc:
>
> BEGIN;
> CREATE TABLE test (date timestamp not null default now(), val varchar(64));
> INSERT INTO test (val) values ('test 1 at time T');
> ########### Here I wait 1 minute #############
> INSERT INTO test (val) values ('test 2 at time T+1 min');
> SELECT * from test;
>             date            |      val
> ----------------------------+------------------------
>  2005-04-15 13:50:53.419981 | test 1 at time T
>  2005-04-15 13:50:53.419981 | test 2 at time T+1 min
> (2 rows)
> ROLLBACK;

This particular case will give the same time because it is all in the
same transaction. CURRENT_TIMESTAMP/now() are "frozen" at start of
transaction, so you can store several timestamps with the same value.

Try commit/begin where you wait for one minute - that should do it.

Alternatively, look at timeofday() instead.

--
   Richard Huxton
   Archonet Ltd