on 04-11-2017 12:59 AM
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?
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
... 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.