Does last_insert_id() gives session specific value or global value across all sessions(multi-client environment)?

Question

I have a table with AUTO_INCREMENT column in vertica db and using this column as foreign key for some other table. For that i need last inserted value for AUTO_INCREMENT column.

CREATE TABLE orders.order_test 
(
  order_id                 AUTO_INCREMENT(1,1,1) PRIMARY KEY,
  order_type               VARCHAR(255)
);

Found this function,but not sure how it works for multiple sessions? https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/LAST_INSERT_ID.htm

Above link says this: Returns the last value of an AUTO_INCREMENT/IDENTITY column. If multiple sessions concurrently load the same table with an AUTO_INCREMENT/IDENTITY column, the function returns the last value generated for that column.

score 1 · Answer 1 · answered Sep 23 '20 at 16:25

It is by session.

Let's test it.

Two command line windows. Starting vsql on both.

The transcript is the whole of the sessions.

Transaction 1:

sbx=> select export_objects('','id1',false);
CREATE TABLE dbadmin.id1
(
    id  IDENTITY ,
    num int
);
[. . .]

sbx=> select * from id1;
   id   | num 
--------+-----
 250001 |   1
sbx=> \pset null NULL
Null display is "NULL".
sbx=> SELECT LAST_INSERT_ID();
 LAST_INSERT_ID 
----------------
           NULL

-- insert a row ...
sbx=> INSERT INTO id1 (num) VALUES(2);
 OUTPUT 
--------
      1
sbx=> SELECT LAST_INSERT_ID();
 LAST_INSERT_ID 
----------------
         500001

Transaction 2:

sbx=> SELECT LAST_INSERT_ID();
 LAST_INSERT_ID 
----------------
           NULL
-- now insert another row ...
sbx=> INSERT INTO id1 (num) VALUES(3);
 OUTPUT 
--------
      1
sbx=> SELECT LAST_INSERT_ID();
 LAST_INSERT_ID 
----------------
         750001

Now, back to Transaction 1:

sbx=> SELECT LAST_INSERT_ID();
 LAST_INSERT_ID 
----------------
         500001

Still at the old value ...

Does last_insert_id() gives session specific value or global value across all sessions(multi-client environment)?

1 Answers1