Re: MySQL LAST_INSERT_ID() to Postgres

On Thu, Aug 28, 2008 at 1:56 PM, Steve Atkins <steve(at)blighty(dot)com> wrote:
>
> Or lastval() if you want something bug-compatible with MySQL.

Not exactly. LAST_INSERT_ID is transactionally safe in that one
connection doesn't see another connections. However, it has it's own
special brand of bug that to me, is much worse.

create table test (id int auto_increment primary key);
insert into test values (DEFAULT);
select LAST_INSERT_ID();
1

In two sessions:
S1: insert into test values (DEFAULT);
S1: select LAST_INSERT_ID();
2
S2: insert into test values (DEFAULT);
S2: select LAST_INSERT_ID();
3
S1: select LAST_INSERT_ID();
2

So that seems reasonable. But here's the part that makes me go huh?

insert into test values (DEFAULT),(DEFAULT),(DEFAULT);
select * from test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+

uh, what? It returns not the LAST insert from a multicolumn insert
but the first. Correct me if I'm wrong, but 3 is most certainly NOT
the last id inserted by our session.

Try the same thing in postgresql and you get the much less
pathological and more understandable set returned:

insert into test values (DEFAULT),(DEFAULT),(DEFAULT) returning i;
i
---
6
7
8

And if it was a BIG insert, and interleaved with another big insert so
it got every other ID, you'd get something back like 6,8,10,11,14
etc... so you'd know again, exactly which records you'd created.