Thread: Last_Inserted Value

Hi all,

i have a little question on getting the last inserted value from a
table.

Lets say i have a transaction which does the following.

insert into foo values(1);
insert into bar values(2);
insert into foobar values(3);

All three tables use the same sequence to increment their id.
I want to know if it is possible to find the id of the inserted value of
foo.

Can i do a select id from foo order by id desc limit 1 before the end of
the transaction,
does this return exactly the row i inserted for this transaction?

Thanks in advance

----- Original Message -----
From: "Andre Schubert"

>
> Can i do a select id from foo order by id desc limit 1 before the end of
> the transaction,
> does this return exactly the row i inserted for this transaction?
>

only if u use SERIALIZABLE transaction isolation level - then the
transactions committed from other users *while* your transaction was in
progress are not visible

in the default READ COMMITED level other committed transactions will
interfere with the "select id from foo order by id desc limit 1" you'll
issue before the end of your transaction

take a look at "Read Committed and Serializable Isolation Levels" from
"PostgreSQL: Introduction and Concepts"

hth,

    Marin

Marin Dimitrov schrieb:
>
> ----- Original Message -----
> From: "Andre Schubert"
>
> >
> > Can i do a select id from foo order by id desc limit 1 before the end of
> > the transaction,
> > does this return exactly the row i inserted for this transaction?
> >
>
> only if u use SERIALIZABLE transaction isolation level - then the
> transactions committed from other users *while* your transaction was in
> progress are not visible

Oh thanks for your advice, i exactly use this transaction isolation
level.

>
> in the default READ COMMITED level other committed transactions will
> interfere with the "select id from foo order by id desc limit 1" you'll
> issue before the end of your transaction
>
> take a look at "Read Committed and Serializable Isolation Levels" from
> "PostgreSQL: Introduction and Concepts"
>
> hth,
>
>     Marin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Andre Schubert <andre.schubert@km3.de> writes:
> All three tables use the same sequence to increment their id.
> I want to know if it is possible to find the id of the inserted value of
> foo.

Try "currval".

            regards, tom lane

Tom Lane schrieb:
>
> Andre Schubert <andre.schubert@km3.de> writes:
> > All three tables use the same sequence to increment their id.
> > I want to know if it is possible to find the id of the inserted value of
> > foo.
>
> Try "currval".

I think i cant use it. because all three tables use the same sequence,
and currval
gives me the id from the insert of the last table, not of the first one.