Thread: Last Insert
Hello All, I have two tables, the first is parental and the second is a child of the first. Ex.: create table first (id SERIAL, descrip CHAR(20)); create table second (id INTEGER references first(id), state CHAR(2)); I have `all' the values to make the inserts in the two tables (ie, descrip and state), so I `would' use: INSERT into first (descrip) values ($descr); INSERT into second (id,state) values (?????????,$state); As you can see, I don't know the ID number assigned to first.id, so I can't do the INSERT in the `second' table... How can I discover the number gave to the SERIAL field in the first table? Thanks in advance, Marcelo
currval('id_of_the _sequence')
Darren Ferguson
Software Engineer
Openband
On Wed, 13 Mar 2002, Marcelo Pereira wrote:
> Hello All,
>
> I have two tables, the first is parental and the second is a child of the
> first.
>
> Ex.:
>
> create table first (id SERIAL, descrip CHAR(20));
> create table second (id INTEGER references first(id), state CHAR(2));
>
> I have `all' the values to make the inserts in the two tables (ie, descrip
> and state), so I `would' use:
>
> INSERT into first (descrip) values ($descr);
> INSERT into second (id,state) values (?????????,$state);
>
> As you can see, I don't know the ID number assigned to first.id, so I
> can't do the INSERT in the `second' table...
>
> How can I discover the number gave to the SERIAL field in the first table?
>
> Thanks in advance,
>
> Marcelo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
On Wed, 13 Mar 2002, Marcelo Pereira wrote:
> Hello All,
>
> I have two tables, the first is parental and the second is a child of the
> first.
>
> Ex.:
>
> create table first (id SERIAL, descrip CHAR(20));
> create table second (id INTEGER references first(id), state CHAR(2));
>
> I have `all' the values to make the inserts in the two tables (ie, descrip
> and state), so I `would' use:
>
> INSERT into first (descrip) values ($descr);
> INSERT into second (id,state) values (?????????,$state);
currval('first_id_seq') should get you the last sequence value given to
your session.
On 3/13/02 10:58 AM, "Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote:
> On Wed, 13 Mar 2002, Marcelo Pereira wrote:
>
>> Hello All,
>>
>> I have two tables, the first is parental and the second is a child of the
>> first.
>>
>> Ex.:
>>
>> create table first (id SERIAL, descrip CHAR(20));
>> create table second (id INTEGER references first(id), state CHAR(2));
>>
>> I have `all' the values to make the inserts in the two tables (ie, descrip
>> and state), so I `would' use:
>>
>> INSERT into first (descrip) values ($descr);
>> INSERT into second (id,state) values (?????????,$state);
>
> currval('first_id_seq') should get you the last sequence value given to
> your session.
I havw a question. Is this reliable even in a connection-pooled environment?
I've always been a little scared of any possible concurrency issues here so
I usually look at the result of the INSERT to get the oid, then use the oid
to query for my key. Am I being too cautious?
-- steve
On Wed, Mar 13, 2002 at 03:46:27PM -0600, Steve Lane wrote:
> On 3/13/02 10:58 AM, "Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote:
> > currval('first_id_seq') should get you the last sequence value given to
> > your session.
>
>
> I havw a question. Is this reliable even in a connection-pooled environment?
> I've always been a little scared of any possible concurrency issues here so
> I usually look at the result of the INSERT to get the oid, then use the oid
> to query for my key. Am I being too cautious?
As long as the currval happens across the same connection as the nextval and
no other processes did any queries in that time across that connection,
you're fine.
In general, as long as each accessor has exclusive access to a single
connection you're fine, no matter what other connections are happening.
So yes, I think you are being overcautious.
Besides, that OID trick won't work in scripts. I often write queries to
files of the form:
insert into a select blah ... ;
insert into b select currval('blah'), blah ... ;
Works like a charm.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> 11/9/2001 - a new beginning or the beginning of the end?
>> I havw a question. Is this reliable even in a connection-pooled environment?
>> I've always been a little scared of any possible concurrency issues here so
>> I usually look at the result of the INSERT to get the oid, then use the oid
>> to query for my key. Am I being too cautious?
>
> As long as the currval happens across the same connection as the nextval and
> no other processes did any queries in that time across that connection,
> you're fine.
Okay. I guess I'm not sure how I would guarantee that no other processes did
any queries in the intervening time. See below.
>
> In general, as long as each accessor has exclusive access to a single
> connection you're fine, no matter what other connections are happening.
In general I'm accessing postgres through PHP-based applications that use a
persistent connection. I use PHP as an Apache module, which means that the
connections are one-per-process. But as far as I understand, it's perfectly
possible that in the middle of PHP script X that's being serviced by process
37, some other user's web request could get handed off to process 37 and use
that connection at any time.
Doesn't this means the currval technique is unsafe in my circumstance?
>
> So yes, I think you are being overcautious.
>
> Besides, that OID trick won't work in scripts. I often write queries to
> files of the form:
>
> insert into a select blah ... ;
> insert into b select currval('blah'), blah ... ;
>
> Works like a charm.
My density is not deliberate, but it is density all the same :-> I don't
understand the above SQL syntax well enough to see how it demonstrates that
the OID technique is unsafe in scripts, Can you elaborate a little?
Thanks for your help.
-- sgl
=======================================================
Steve Lane
Vice President
Chris Moyer Consulting, Inc.
833 West Chicago Ave Suite 203
Voice: (312) 433-2421 Email: slane@fmpro.com
Fax: (312) 850-3930 Web: http://www.fmpro.com
=======================================================
On Wed, Mar 13, 2002 at 09:35:25PM -0600, Steve Lane wrote:
> In general I'm accessing postgres through PHP-based applications that use a
> persistent connection. I use PHP as an Apache module, which means that the
> connections are one-per-process. But as far as I understand, it's perfectly
> possible that in the middle of PHP script X that's being serviced by process
> 37, some other user's web request could get handed off to process 37 and use
> that connection at any time.
Really? I thought a single process in Apache could only handle one request
at a time. That's why apache has multiple processes. However, I'm not sure
sure a multi-threaded version of Apache would deal with this.
> Doesn't this means the currval technique is unsafe in my circumstance?
If it really did that, then yes it would be unsafe. But if that were the
case then it should be advertised as it would be a serious issue. Worse, it
would be one where no amount of trickery in the DB server would help you
(except returning the currval as part of the query response instead of the
OID).
> > insert into a select blah ... ;
> > insert into b select currval('blah'), blah ... ;
> >
> > Works like a charm.
>
> My density is not deliberate, but it is density all the same :-> I don't
> understand the above SQL syntax well enough to see how it demonstrates that
> the OID technique is unsafe in scripts, Can you elaborate a little?
The OID is not unsafe, I mean that it can't be used in a system where you
use psql < file to do your updates. There is no programmatical construct for
OID_OF_LAST_INSERT. Like in:
insert into a select blah ... ;
insert into b select a.id, blah ... where a.oid = OID_OF_LAST_INSERT;
You need a program to extract the oid from the result of the query and send
a modify the query appropriatly. This may not be applicable in your case.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> 11/9/2001 - a new beginning or the beginning of the end?
On 3/14/02 1:06 AM, "Martijn van Oosterhout" <kleptog@svana.org> wrote: > On Wed, Mar 13, 2002 at 09:35:25PM -0600, Steve Lane wrote: >> In general I'm accessing postgres through PHP-based applications that use a >> persistent connection. I use PHP as an Apache module, which means that the >> connections are one-per-process. But as far as I understand, it's perfectly >> possible that in the middle of PHP script X that's being serviced by process >> 37, some other user's web request could get handed off to process 37 and use >> that connection at any time. > > Really? I thought a single process in Apache could only handle one request > at a time. That's why apache has multiple processes. However, I'm not sure > sure a multi-threaded version of Apache would deal with this. Oh, right. When you put it that way I'm sure that's the way it works. Each Apache process is single-threaded to the best of my knowledge. > The OID is not unsafe, I mean that it can't be used in a system where you > use psql < file to do your updates. There is no programmatical construct for > OID_OF_LAST_INSERT. Like in: > > insert into a select blah ... ; > insert into b select a.id, blah ... where a.oid = OID_OF_LAST_INSERT; > > You need a program to extract the oid from the result of the query and send > a modify the query appropriatly. This may not be applicable in your case. Ah, now I see you what you meant by a file query. Makes perfect sense. Thanks again. -- sgl
Steve,
An alternate method to do this (which I use in a CGI environment) would be
to select your nextval from your script, then insert your data with the
specified value, rather than letting the table default it. You would have
something like:
(This isn't written in any particular language, as I don't know PHP, but
you should get the idea.)
$NewID = "SELECT nextval('sequence_name'::text)'";
do "INSERT INTO MyParentTable (ID, Value) VALUES ($NewID, 'some stuff');"
do "INSERT INTO MyChildTable (ParentID, Value) VALUES ($NewID, 'some other
stuff');"
This method should be safe with connection pooling, and doesn't require you
to change the design of your tables or anything.
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Steve Lane
Sent: Wednesday, March 13, 2002 7:35 PM
To: Martijn van Oosterhout
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Last Insert
>> I havw a question. Is this reliable even in a connection-pooled
environment?
>> I've always been a little scared of any possible concurrency issues here
so
>> I usually look at the result of the INSERT to get the oid, then use the
oid
>> to query for my key. Am I being too cautious?
>
> As long as the currval happens across the same connection as the nextval
and
> no other processes did any queries in that time across that connection,
> you're fine.
Okay. I guess I'm not sure how I would guarantee that no other processes did
any queries in the intervening time. See below.
>
> In general, as long as each accessor has exclusive access to a single
> connection you're fine, no matter what other connections are happening.
In general I'm accessing postgres through PHP-based applications that use a
persistent connection. I use PHP as an Apache module, which means that the
connections are one-per-process. But as far as I understand, it's perfectly
possible that in the middle of PHP script X that's being serviced by process
37, some other user's web request could get handed off to process 37 and use
that connection at any time.
Doesn't this means the currval technique is unsafe in my circumstance?
>
> So yes, I think you are being overcautious.
>
> Besides, that OID trick won't work in scripts. I often write queries to
> files of the form:
>
> insert into a select blah ... ;
> insert into b select currval('blah'), blah ... ;
>
> Works like a charm.
My density is not deliberate, but it is density all the same :-> I don't
understand the above SQL syntax well enough to see how it demonstrates that
the OID technique is unsafe in scripts, Can you elaborate a little?
Thanks for your help.
-- sgl
=======================================================
Steve Lane
Vice President
Chris Moyer Consulting, Inc.
833 West Chicago Ave Suite 203
Voice: (312) 433-2421 Email: slane@fmpro.com
Fax: (312) 850-3930 Web: http://www.fmpro.com
=======================================================
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org