TIMESTAMP and PgPL/SQL - Mailing list pgsql-general

First off, I'm not sure if this is the right forum for support or not
(we've been using PostgreSQL with no need for any support for quite a
while, this is the first issue we've run into).  If this isn't the
correct forum, please point me in the right direction.


I've searched on Google and read through the idocs linked to from
postgresql.org, but have not been able to find any postings related to
the issue we're having.

We've got a table with a timestamp column.  The timestamp column was
defaulted to CURRENT_TIMESTAMP at the time of creation.  Using pg_dump
to backup/replicate the database, the column ends up being defined as:

  "v_timestamp" timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone,

We're using a stored procedure (PgPL/SQL) to insert records into the
table in question.

The strange behavior we're seeing is variance in this timestamp column.
Sequentialy inserted records will have a timestamp that varys
independent of any system time - sometimes subsequent insetions will end
up with a timestamp that is _before_ their preceeding records.

I've tried allowing the value to default, using CURRENT_TIMESTAMP,
selecting CURRENT_TIMESTAMP into a temporary, and using (SELECT
CURRENT_TIMESTAMP).


  INSERT INTO TABLE (...) VALUES (...);
  INSERT INTO TABLE (..., v_timestamp) VALUES (..., CURRENT_TIMESTAMP);
  INSERT INTO TABLE (..., v_timestamp) VALUES (..., (SELECT CURRENT_TIMESTAMP));

  DECLARE
    ...
    tempTimestamp TIMESTAMP;
  BEGIN
    ...
    SELECT INTO tempTimestamp CURRENT_TIMESTAMP;
    INSERT INTO TABLE (..., v_timestamp) VALUES (..., tempTimestamp);
    ...
  END

In all cases, we still end up seeing the same inconsistient behavior.


In this table output, the records were created in pairs by the
application (35+36, 37+38, 39+40, 41+42).  The v_id column is populated
(defaulted) from a sequence.

  sfa_data=> select v_id,v_timestamp from v_practitioner_org_rel;
   v_id |          v_timestamp
  ------+-------------------------------
     35 | 2002-11-04 14:50:18.311747-05
     36 | 2002-11-04 14:50:18.311747-05
     37 | 2002-11-04 14:50:02.195181-05
     38 | 2002-11-04 14:57:05.255803-05
     39 | 2002-11-04 14:43:29.583182-05
     40 | 2002-11-04 14:43:29.583182-05
     41 | 2002-11-04 14:49:25.795724-05
     42 | 2002-11-04 14:57:54.072637-05
  (8 rows)

Some interesting aspects are:

35+36 look fine.
37+38 were inserted within a few milliseconds of each other.
39+40 these were inserted _after_ 37+38, but have an earlier timestamp?
41+42 again, these were inserted sequentialy as part of the same
      transaction, and 41 is still earlier than 38.

Our environment is:

  OS:          Mandrake 9.0 Linux 2.4.19
  PostgreSQL:  7.2.2
  Apache:      1.3.26 w/mod_perl  1.27
  Perl:        5.8.0
  DBI:         1.30
  DBD::Pg:     1.13



Any help or guidance would be greatly appreciated.