Обсуждение: TIMESTAMP and PgPL/SQL

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.

Kyle Burton <kburton@hmsonline.com> writes:
> 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.

CURRENT_TIMESTAMP is defined to return the time of transaction start.
If you've got concurrent transactions inserting into the same table,
it's certainly possible that transaction A might start before
transaction B and then insert a record into the table after transaction
B does.

But it's not entirely clear to me how you're defining "before" here.
Physical ordering in the table is not a reliable guide.

In most situations, the only way to ensure that there's a
well-understood ordering between two concurrent transactions is for each
to successively acquire some exclusive lock before it does any useful
work.  This is a recipe for poor concurrent performance, so if you find
yourself feeling you need that, it's a good idea to take two steps back
and ask yourself exactly why.

            regards, tom lane