Thread: Ref to last INSERT on a table without OIDs?
Hi there, Hopefully just a quick one: how is it possible to obtain a reference to the last inserted record in a table which is created without oids? I would like to dump/restore some of our larger tables so they don't use oids, however I am concerned that simply getting the current PK sequence value after insertion within a transaction is not safe when many people are accessing the table/sequence at once? Can anyone clarify this? We're using the latest and greatest 7.3.1. Many thanks, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
On Tue, 28 Jan 2003, Mark Cave-Ayland wrote: > Hi there, > > Hopefully just a quick one: how is it possible to obtain a reference to > the last inserted record in a table which is created without oids? I > would like to dump/restore some of our larger tables so they don't use > oids, however I am concerned that simply getting the current PK sequence > value after insertion within a transaction is not safe when many people > are accessing the table/sequence at once? Can anyone clarify this? We're > using the latest and greatest 7.3.1. If you use currval() to get the sequence value it'll be the last value given to your session so you don't have to worry about other sessions modifying the sequence.
> -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > Sent: 28 January 2003 16:14 > To: Mark Cave-Ayland > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Ref to last INSERT on a table without OIDs? > > > On Tue, 28 Jan 2003, Mark Cave-Ayland wrote: > > > Hi there, > > > > Hopefully just a quick one: how is it possible to obtain a reference to > > the last inserted record in a table which is created without oids? I > > would like to dump/restore some of our larger tables so they don't use > > oids, however I am concerned that simply getting the current PK sequence > > value after insertion within a transaction is not safe when many people > > are accessing the table/sequence at once? Can anyone clarify this? We're > > using the latest and greatest 7.3.1. > > If you use currval() to get the sequence value it'll be the last value > given to your session so you don't have to worry about other sessions > modifying the sequence. Hi Stephan, Thanks for your reply! But I'm still a little confused by what defines a session - is it a transaction or a backend? I'm just thinking about connections from web pages which use PHP pooled connections..... if a session is a transaction then I'm guessing everything should work fine without any problems? Cheers, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
On Tue, 28 Jan 2003, Mark Cave-Ayland wrote: > > > -----Original Message----- > > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > > Sent: 28 January 2003 16:14 > > To: Mark Cave-Ayland > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Ref to last INSERT on a table without OIDs? > > > > > > On Tue, 28 Jan 2003, Mark Cave-Ayland wrote: > > > > > Hi there, > > > > > > Hopefully just a quick one: how is it possible to obtain a reference > to > > > the last inserted record in a table which is created without oids? I > > > would like to dump/restore some of our larger tables so they don't > use > > > oids, however I am concerned that simply getting the current PK > sequence > > > value after insertion within a transaction is not safe when many > people > > > are accessing the table/sequence at once? Can anyone clarify this? > We're > > > using the latest and greatest 7.3.1. > > > > If you use currval() to get the sequence value it'll be the last value > > given to your session so you don't have to worry about other sessions > > modifying the sequence. > > Thanks for your reply! But I'm still a little confused by what defines a > session - is it a transaction or a backend? I'm just thinking about > connections from web pages which use PHP pooled connections..... if a > session is a transaction then I'm guessing everything should work fine > without any problems? It's to the backend, but as long as you're asking for the value before you release the connection to another script you should be fine. If you want to do something like: script 1 inserts - release connection so someone else can get it script 2 inserts - release connection script 1 wants a value You can't do that without converting it to a different form script 1 inserts script 1 saves its inserted value - release connection ... - release connection script 1 retrieves inserted value.
On Tue, Jan 28, 2003 at 14:25:32 -0000, Mark Cave-Ayland <mark.cave-ayland@webbased.co.uk> wrote: > > Hopefully just a quick one: how is it possible to obtain a reference to > the last inserted record in a table which is created without oids? I > would like to dump/restore some of our larger tables so they don't use > oids, however I am concerned that simply getting the current PK sequence > value after insertion within a transaction is not safe when many people > are accessing the table/sequence at once? Can anyone clarify this? We're > using the latest and greatest 7.3.1. If you are trying to get the primary key of the last record inserted during the same database session, then using serial for the type of the primary key and the currval function is exactly what you want.