Thread: CURRENT_TIMESTAMP not work correctly insinde a transaction.

Hello
  I am using pgsql 7.1 and 7.2 and I found a bug When I use
CURRENT_TIMESTAMP in a sql query.
In a transaction until you commit your transaction through a jdbc
connection, each time you use the Current_timestamp (or 'now'), the
timestamps is always the same, and when your transaction is very long you
have undesirable effect.


I also would like to submit enhancement request, who can I contact or where
can I send e-mail ?

Thanks for you Help.

example of code :


import java.sql.*;

class test  {

   static private String driver = "org.postgresql.Driver";
   static private String url    =
"jdbc:postgresql://bela:5432/idb?compatible=7.1";

   static void main(String argv[]) {
      Connection con        = null;
      Statement  statement  = null;
      ResultSet  rset       = null;

      try {
         Class.forName(driver);
         con = DriverManager.getConnection(url, "iuser", "qa");
         statement = con.createStatement();
         con.setAutoCommit(false);
         int cpt;
         for (int i=0;i<5;i++) {
            rset = statement.executeQuery("SELECT CURRENT_TIMESTAMP AS
VAR");
            if (rset!=null && rset.next()) {
               java.sql.Timestamp ts = rset.getTimestamp("VAR");
               System.out.println(i + "  -> timestamp = "+ts);
            }
            Thread.sleep(1000);
         }

         con.commit();

         for (int i=0;i<5;i++) {
            rset = statement.executeQuery("SELECT CURRENT_TIMESTAMP AS
VAR");
            if (rset!=null && rset.next()) {
               java.sql.Timestamp ts = rset.getTimestamp("VAR");
               System.out.println((i+5) + "  -> timestamp = "+ts);
            }
            Thread.sleep(1000);
         }

         statement.close();
         con.close();
      } catch (Exception e) {
         System.out.println("# Problem: "+e);
         e.printStackTrace();
      }
   }
}

--
Nicolas Paymal
Developer R&D.
Instranet Inc.

> In a transaction until you commit your transaction through a jdbc
> connection, each time you use the Current_timestamp (or 'now'), the
> timestamps is always the same, and when your transaction is very long you
> have undesirable effect.

This is not a bug, but the behavior required by SQL9x afaicr.

There is another function (timeofday()?) which returns the current clock
time.

hth

                    - Thomas

Thomas Lockhart writes:

> > In a transaction until you commit your transaction through a jdbc
> > connection, each time you use the Current_timestamp (or 'now'), the
> > timestamps is always the same, and when your transaction is very long you
> > have undesirable effect.
>
> This is not a bug, but the behavior required by SQL9x afaicr.

AFAIK, this is PostgreSQL bugward compatibility.  SQL says that
current_timestamp returns the "current timestamp", which you can interpret
any way you want to.

--
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> Thomas Lockhart writes:
>> This is not a bug, but the behavior required by SQL9x afaicr.

> AFAIK, this is PostgreSQL bugward compatibility.  SQL says that
> current_timestamp returns the "current timestamp", which you can interpret
> any way you want to.

Not entirely.  The SQL92 spec says (sec. 6.8):

         3) If an SQL-statement generally contains more than one reference
            to one or more <datetime value function>s, then all such ref-
            erences are effectively evaluated simultaneously. The time of
            evaluation of the <datetime value function> during the execution
            of the SQL-statement is implementation-dependent.

This clearly requires all current_timestamp calls within a single query
to return the same result.  We extend that to be the same result across
a whole transaction.  I consider that to be legal given the
"implementation-dependent" stipulation of the spec, and also to be quite
useful since you can reliably insert the same timestamp into multiple
rows in different tables.

If you happen to want true realtime rather than a transaction start
time, you can get it from timeofday().  But current_timestamp is not
supposed to give true realtime.

I'm not quite sure what the usefulness would be of start-of-statement
timestamps as opposed to start-of-transaction timestamps, which'd be
the other plausible way of conforming to the spec.  (For one thing,
it'd be less than clear what to do with SQL statements executed inside
functions: is there an "inner statement" current_timestamp that's
different from the one prevailing outside the function call?  Ugh.)

            regards, tom lane