Lists: | pgsql-admin |
---|
From: | Andre Schubert <andre(dot)schubert(at)km3(dot)de> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Last_Inserted Value |
Date: | 2002-03-28 08:06:55 |
Message-ID: | 3CA2CF1F.3451C5B4@km3.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | "Marin Dimitrov" <marin(dot)dimitrov(at)sirma(dot)bg> |
---|---|
To: | "Andre Schubert" <andre(dot)schubert(at)km3(dot)de>, <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Last_Inserted Value |
Date: | 2002-03-28 11:11:39 |
Message-ID: | 000501c1d649$55b47110$9b0e32d4@sirma.bg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
----- 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
From: | Andre Schubert <andre(dot)schubert(at)km3(dot)de> |
---|---|
To: | Marin Dimitrov <marin(dot)dimitrov(at)sirma(dot)bg> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Last_Inserted Value |
Date: | 2002-03-28 12:37:48 |
Message-ID: | 3CA30E9C.A45A5557@km3.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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(at)postgresql(dot)org)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andre Schubert <andre(dot)schubert(at)km3(dot)de> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Last_Inserted Value |
Date: | 2002-03-28 14:40:35 |
Message-ID: | 25557.1017326435@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Andre Schubert <andre(dot)schubert(at)km3(dot)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
From: | Andre Schubert <andre(dot)schubert(at)km3(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Last_Inserted Value |
Date: | 2002-04-02 04:52:07 |
Message-ID: | 3CA938F7.F8EB74E5@km3.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Tom Lane schrieb:
>
> Andre Schubert <andre(dot)schubert(at)km3(dot)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.