Bright Yellow Cow :: Blog

begin;
insert into bar (email) values ('foo');
select last_value from bar_id_seq;
commit;

This is valid behaviour in the standard isolation model of Postgres (and most likely MySql too, but I haven't tested it). Here's how to reproduce it. Open two psql consoles, and run these commands:

create table bar (id serial primary key, email text);

Begin;

Begin;

insert into bar (email) values ('foo');

 insert into bar (email) values ('ff');

select last_value from bar_id_seq;
 last_value 
------------
          2
(1 row)

...assume that 'foo' has id 2...

Commit;

select * from bar;
 id | email 
----+-------
  1 | foo
(1 row)

Commit;

select * from bar;
 id | email 
----+-------
  1 | foo
  2 | ff
(2 rows)

Some database libraries (JDBC for example) allow you to query the values generated, which is one solution to the problem. Another is to not make the id type 'serial' at all, but define the sequence explicitly:

create sequence bar_id_seq;
create table bar (
	id	bigint primary key,
	... 
);

You then issue two queries: the first to mint a new id, and then use that to insert into the table:

select nextval(bar_id_seq');
insert into bar (id,...) values (?,...)

A second solution is to the the Postgresql extension 'returning':

insert into foo (id,email) values (default,'bla') returning id;
 id 
----
  1
(1 row)