postgresql last insert id concurrency

Question

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 ?

Frank Heikens · Accepted Answer · 2012-04-04 08:06:19Z

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;

answered Apr 4, 2012 at 8:06

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dipro Sen Over a year ago

Thanks. Really Simple Solution.

user330315 · Accepted Answer · 2012-04-04 08:03:44Z

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').

answered Apr 4, 2012 at 8:03

user330315

Collectives™ on Stack Overflow

postgresql last insert id concurrency

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related