Re: Last Insert - Mailing list pgsql-general

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