Re: @@IDENTITY (Was: Access - ODBC - serial problem)

On Sat, 10 Apr 2004, Ed Brown wrote:

> I agree with the strangeness of Microsoft's name, but the capability is
> useful. What I do in a case like this is use the transaction capability. If
> you do
>
> Begin transaction;
> <Do the insert>;
> Select max(oid_column_name) from table;
> Commit work;
>
> You are guaranteed to get the correct ID back in any system that supports
> transactions. Because all "real" databases support caching, it's
> computationally inexpensive, unless you have a trigger that will insert
> another record behind your back.

In Postgresql, this is NOT I/O inexpensive, but results in a seq scan of
the whole table to find the max value.

select oid_column_name from table order by oid_column_name desc limit 1

is computationally and I/O inexpensive if the table has an index on that
field.

However, I think you have to be in serializable transaction mode for that
code to be gauranteed not to get the wrong data. But I'm not certain on
that one.