postgresql last insert id concurrency

Go To StackoverFlow.com

2

I want to insert an user and get its id e.g. last insert id. through sql.

CREATE OR REPLACE FUNCTION create_user(registration_ip inet)
  RETURNS integer AS
insert into users(registration_ip)
   values($1);
select max(id) from users;
LANGUAGE sql VOLATILE

Is this function secure ? I meant is it concurrency safe ? e.g. n users are being created concurrently is it guaranteed that it will return the current user's id ?

2012-04-04 07:59
by Dipro Sen


4

Use RETURNING:

CREATE OR REPLACE FUNCTION create_user(registration_ip inet)
  RETURNS integer AS
INSERT INTO users(registration_ip)
   VALUES($1) RETURNING id;
LANGUAGE sql VOLATILE;
2012-04-04 08:06
by Frank Heikens
+1 even better that explicitely using the sequenc - a_horse_with_no_name 2012-04-04 08:06
Thanks. Really Simple Solution - Dipro Sen 2012-04-04 08:10


1

No it's not safe (and it's terribly slow!)

You should use sequences for this purpose.

If the id column is defined as serial, a sequence is automatically created for you and you can retrieve the last generated number using currval('users_id_seq').

2012-04-04 08:03
by a_horse_with_no_name
It never came into my mind. good optio - Dipro Sen 2012-04-04 08:10
Ads