Skip to Content

CURRENT_IDENTITY_VALUE inconsistency

It seems like CURRENT_IDENTITY_VALUE() function in HANA can mess things up when you have at least two tables in your session with identity columns. Just try this sample code:

create column table test1(id integer not null primary key generated by default as identity, x integer);
create column table test2(id integer not null primary key generated by default as identity, x integer);

create procedure temp_identity_p as
  i integer;
  j integer;
begin
  for i in 1..500 do
    insert into test1(x) values (:i);
    select current_identity_value() into j from dummy;
    if i > 100 then
      insert into test2(x) values (:j);
    end if;
  end for;
end;

call temp_identity_p;

select * from test1 where id <> x;
select * from test2 where id <> x-100 order by id;

After executing the procedure test1 table looks as expected. Values from 1 to 500, same values in "id" and "x" columns.

But in test2, x should come from test1.id (sort of a link to it) with a delay of 100. So you expect:

1,101

2,102

3, 103 and so on

But it's not the case in every record.

4,3

5,4

records follows. It seems like CURRENT_IDENTITY_VALUE() sometimes gets its value from the last insert of test2 instead of test1. However it's pretty obvious that CURRENT_IDENTITY_VALUE() is executed right after test1 insertion.

This is very dangerous since it can mess up your foreign keys in your code.

Do you know any solution for this problem?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Jun 06, 2017 at 10:06 PM

    While this may not help, you could remove the identity key from the table and manage it yourself by creating a specific sequence (sq_mysequence) and callint it's sq_mysequence.nextvalue when doing the insert, or (before doing the insert into test1, putting it in a variable then reusing it for all subsequent insert in test2).

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 06, 2017 at 10:11 PM

    What is the revision of your database ? executing this on 122.05 return on result in the test2 select query. looking manually at all the results, they seems fine to me.

    Add comment
    10|10000 characters needed characters exceeded

    • Thank you for your answer, and your time!

      Our revision is 112.03, but strangely enough, now everything works fine for me too.

      It's so embarrassing, but I cannot reproduce it. There has been no version change since my post. Maybe this behaviour was caused by some settings or coincidence, that has been changed since then. But I'm sure I spent a lot of time with it, and it caused a lot of trouble to us. I really don't understand...

  • avatar image
    Former Member
    Jul 19, 2017 at 01:57 PM

    For (1..500) might be executed in parallel. current_identity_value will probably not return the last inserted value from this thread, but any thread. In your case, execution of foreach with i = 100 started and inserted, but then another thread processed where i = 1 and inserted. then code switched back to the select current_ident_value and got 1?

    Instead of using current_identity_value which might not be correct as it mixes identity created from any table and threads and sessions, proactively get the sequence to use, then push it to the child table. You will end up with less issue. here is a draft but I am not sure will compile

    on mssql you would have fixed this by using the output close, to return the inserted primary key of that specific insert, but it's seems it's not possible on hana as far as the insert documentation is concerned.

    create column table test1(id integer not null primary key, x integer);
    create column table test2(id integer not null primary key, x integer);
    create sequence sq_test1;
    create sequence sq_test2;
    
    create procedure temp_identity_p as
      i integer; sq_i integer;
      j integer; 
    begin
      for i in 1..500 do
        selelct sq_test1.nextval into sq_i from dummy;
        insert into test1(id, x) values (:sq_i, :i);
        
        if i > 100 then
          insert into test2(id, x) values (sq_test2.nextval, :sq_i);
        end if;
      end for;
    end;
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

    • Thank you!

      Sequence is the way to go, instead of auto generated identity and current_identity_value.

      In my opinion, using current_identity_value makes no sense at all in any cases, because of the thread-issue.

  • Jun 28, 2017 at 09:31 AM

    Ooops I did it again. :)

    In the same sql console window that I used last time. But last time it refused to mix up the result, now it does it again.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 19, 2017 at 02:15 PM

    ... and the last example with sequence show that you will insert the sequence of i into test2, and not your i variable generated by the for, so so it's normal you might not have test2.x > 100 because those are two different var.

    Add comment
    10|10000 characters needed characters exceeded