Re: 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(at)fmpro(dot)com
Fax: (312) 850-3930 Web: http://www.fmpro.com
=======================================================