Re: Last Insert

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(at)svana(dot)org>
http://svana.org/kleptog/
> 11/9/2001 - a new beginning or the beginning of the end?