Friday, October 8, 2021

Different behaviours in getting current timestamp in Oracle and PostgreSQL within a transaction

Behaviour in Oracle:

  • Implicitly start a transaction when first DML statement starts
  • DDL is not part of the transaction
  • functions like sysdate(), systimestamp() return different values to reflect the time when these functions executed.



SQL> create table t1 (ts timestamp);


Table T1 created.


SQL> insert into t1 values (systimestamp);


1 row inserted.


SQL> insert into t1 values (systimestamp);


1 row inserted.


SQL> select * from t1;


                               TS 

_________________________________ 

08/10/21 01:56:15.663021000 AM    

08/10/21 01:56:17.543937000 AM    


SQL> rollback;


Rollback complete.


SQL> select * from t1;


no rows selected




Behaviour in PostgreSQL:

  • Explicitly start a transaction (in psql CLI)
  • DDL can be part of the transaction
  • functions like sysdate(), systimestamp() return same value to reflect the time when transaction starts.
  • To get similar behaviour as Oracle, use clock_timestamp()

mytest=> begin;

BEGIN

mytest=*> create table t1 (ts timestamp);

CREATE TABLE

mytest=*> insert into t1 values (current_timestamp);

INSERT 0 1

mytest=*> insert into t1 values (current_timestamp);

INSERT 0 1

mytest=*> select * from t1;

             ts             

----------------------------

 2021-10-08 01:49:26.576841

 2021-10-08 01:49:26.576841

(2 rows)


mytest=*> rollback;

ROLLBACK


No comments:

Post a Comment