Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Last ID Problem

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [NOVICE] Last ID Problem
Date: 2005-02-03 05:16:24
Message-ID: c1312e8b887322ad1d87af390b6e6f21@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-hackers
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
> This suffers from the same problems that currval does when using
> connection pools tho.
 
I still don't see this as much of a real world problem however,
more of a "doctor, it hurts when I do this" variety. As the DBD::Pg docs
point out, you should not separate the calls to insert and currval
far apart, and certainly not across connections. I have a hard time
visualizing a case where an app would ever need to worry about the
problem anyway, unless they were using pooling in a strange way and
had a very poorly written application.
 
> The solution I proposed, namely having the tuple returned by
> inserts/updates (perhaps even deletes?) would only mean changing the
> client library to handle this, and as an example, libpg could easily
> figure out the OID of said tuple and return that if it's present for
> PQExec() (for backwards compatibility just as it does today,) and add a
> separate PQExecSelect() that instead returns the tuple(s) as if they had
> been SELECTed.
 
There's a few issues with the above, however, the most important of which
is that OIDs are going away, and then what do you use? Also, it does not
handle cases where the insert necessarily happened with a direct INSERT
via PQexec: the insert could have happened inside of a called function, or
a trigger could have inserted into three different tables. Truth be told, I
don't think the whole last_insert_id() in DBI is a very useful function
anyway. It's mainly (at this point) a quick abstraction of a nextval call
between Oracle and PostgreSQL. We do go out of our way to be more compatible
to MySQL by accepting just a table name, but one should really use the
sequence directly, IMO.
 
- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200502030012
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFCAbQRvJuQZxSWSsgRArYMAKC4Kgsv153HHbC05AtraAh4O7oL9wCgtDmR
zoucziPs5cyC1at00M8MC9w=
=PDUD
-----END PGP SIGNATURE-----



In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-02-03 05:17:19
Subject: Re: subselects in the target list
Previous:From: Neil ConwayDate: 2005-02-03 05:02:52
Subject: Re: subselects in the target list

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group