Home » SQL & PL/SQL » SQL & PL/SQL » unique timestamp
unique timestamp [message #686663] Tue, 15 November 2022 04:22 Go to next message
deepakdot
Messages: 76
Registered: July 2015
Member
Hi

I have a table with a timestamp column. we have a unique index on this column. When I insert rows, sometime it executed so fast that 2 rows get same timestamp and it failed. How to make sure Oracle always return a unique timestamp while I insert, So that it solve my purpose.

any trick to solve this. Or any user define function I can create and use here . Please suggest.

CREATE TABLE T1 (COL1 TIMESTAMP NOT NULL);

INSERT INTO T1 VALUES (CURRENT_TIMESTAMP);
INSERT INTO T1 VALUES (CURRENT_TIMESTAMP);
..
..
INSERT INTO T1 VALUES (CURRENT_TIMESTAMP);

Thanks,
Deepak Samal
Re: unique timestamp [message #686664 is a reply to message #686663] Tue, 15 November 2022 04:38 Go to previous messageGo to next message
John Watson
Messages: 8781
Registered: January 2010
Location: Global Village
Senior Member
I wouldn't think there is a solution. The timestamp precision depends on your hardware and OS. IIRC, Windows is milliseconds and Unix is microseconds. You can't get finer than that.
Re: unique timestamp [message #686665 is a reply to message #686663] Tue, 15 November 2022 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If this is in a single process you can use dbms_lock.sleep or a client sleep to wait a while between inserts.
If this is between processes then you can use dbms_lock to request and release a lock which will take longer than the microsecond between 2 timestamps.


Re: unique timestamp [message #686666 is a reply to message #686665] Tue, 15 November 2022 06:26 Go to previous messageGo to next message
John Watson
Messages: 8781
Registered: January 2010
Location: Global Village
Senior Member
If suppressing the error is enough, then you could use the /*+ ignore_row_on_dupkey_index(t1(col1)) */ hint.
Re: unique timestamp [message #686667 is a reply to message #686665] Tue, 15 November 2022 06:42 Go to previous messageGo to next message
deepakdot
Messages: 76
Registered: July 2015
Member
I think dbms_session.sleep will work for me. I will create a function like below and then use that in the insert. Then every insert it will pause for sometime and then return a value. Do you see any issue in this.


CREATE or replace FUNCTION CRT_TS
RETURN TIMESTAMP AS
TS TIMESTAMP;
BEGIN
dbms_session.sleep(0.05);
SELECT CURRENT_TIMESTAMP INTO TS from DUAL;
RETURN(TS);
END;
/

INSERT INTO T1 VALUES (CRT_TS);
INSERT INTO T1 VALUES (CRT_TS);
INSERT INTO T1 VALUES (CRT_TS);
..

Re: unique timestamp [message #686668 is a reply to message #686667] Tue, 15 November 2022 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No need to use SELECT in your function:
CREATE or replace FUNCTION CRT_TS
RETURN TIMESTAMP AS 
BEGIN 
  dbms_session.sleep(0.01);
  RETURN(CURRENT_TIMESTAMP);
END;
/
Re: unique timestamp [message #686670 is a reply to message #686668] Wed, 16 November 2022 04:25 Go to previous messageGo to next message
deepakdot
Messages: 76
Registered: July 2015
Member
Thank you Michel. This solves my issues.
Re: unique timestamp [message #686674 is a reply to message #686670] Thu, 17 November 2022 06:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3174
Registered: January 2010
Location: Connecticut, USA
Senior Member
This solution is simply masking the issue - using timestamp as unique/pk constraint. In addition it affects performance. Context switch + other overhead of calling and executing fuction CRT_TS will greately exceed sleep time especially when you do things like:

SELECT CURRENT_TIMESTAMP INTO TS from DUAL;
instead of simply

RETURN(CURRENT_TIMESTAMP);
And again, using timestamp as unique value is bad design - use sequence instead/in addition. If you must do it, then use:

BEGIN
INSERT INTO T1 VALUES (CURRENT_TIMESTAMP);
dbms_session.sleep(0.05);
INSERT INTO T1 VALUES (CURRENT_TIMESTAMP);
dbms_session.sleep(0.05);
INSERT INTO T1 VALUES (CURRENT_TIMESTAMP);
END;
/
But all of the above is flawed anyway since you sooner or later will run into duplicates when multiple users will be inserting - that sleep approach will work in single user case only. And look at column data type:

CREATE TABLE T1 (COL1 TIMESTAMP NOT NULL);
No time zone. Do you realize CURRENT_TIMESTAMP returns timestamp with client time zone? So assume client in New York inserts a row at 8 am. CURRENT_TIMESTAMP will return 17-NOV-22 08.00.00.000000 AM -05:00 and 17-NOV-22 08.00.00.000000 AM will be inserted into the table. 3 hours later client in San Francisco tries to insert. CURRENT_TIMESTAMP will return 17-NOV-22 08.00.00.000000 AM -08:00 and since column is just a timestamp without time zone we will end up with duplicate:

SQL> create table tbl(ts timestamp);

Table created.

SQL> insert into tbl values(cast('17-NOV-22 08.00.00.000000 AM -05:00' as timestamp with time zone));

1 row created.

SQL> insert into tbl values(cast('17-NOV-22 08.00.00.000000 AM -08:00' as timestamp with time zone));

1 row created.

SQL> select * from tbl;

TS
---------------------------------------------------------------------------
17-NOV-22 08.00.00.000000 AM
17-NOV-22 08.00.00.000000 AM

SQL>
You should consider using SYSTIMESTAMP which is client independent and possibly change column to timestamp with time zone.

SY.

[Updated on: Thu, 17 November 2022 07:04]

Report message to a moderator

Re: unique timestamp [message #686688 is a reply to message #686674] Mon, 21 November 2022 05:57 Go to previous message
deepakdot
Messages: 76
Registered: July 2015
Member
Thank you this advice but we will not have these issues.

1. I want to run these Insert scripts individually, not in a single block ( Begin .. End).
2. We dont have a unique index / constraint on this timestamp column. for some functional requirement, we wanted to have this column as unique. also this file will get executed once from one session. Not from multiple session.
3. we insert UTC timestamp. So we will not get duplicate while inserted from multiple region. we are good here.

Thanks
Deepak Samal
Previous Topic: Row into Column and sum two columns
Next Topic: Find row count after update
Goto Forum:
  


Current Time: Fri Dec 02 03:12:50 CST 2022