Hi Tom,
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: 02 February 2005 15:35
To: Mark Cave-Ayland
Cc: 'Alvaro Herrera'; 'Michael Fuhr'; 'Mitch Pirtle'; 'Tatsuo
Ishii'; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [NOVICE] Last ID Problem
(cut)
Hi Tom and others,
I think the correct solution is not to mess with what's admittedly a
legacy aspect of
our client API. Instead we should invent the INSERT RETURNING and
UPDATE RETURNING
commands that have been discussed repeatedly (see the pghackers archives).
That would
allow people to
Mark Cave-Ayland [EMAIL PROTECTED] writes:
Just off the top of my head, would it not be feasible to add a column to
pg_class called lastinsert that points to the OID of the pg_attribute column
to return after an insert?
No. The thing everyone is ignoring here is that the INSERT command tag
Tom Lane [EMAIL PROTECTED] writes:
How is what you're suggesting more portable?
Well, the driver would be free to implement $sth-last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be hidden in the driver layer. Switch out the driver and
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
How is what you're suggesting more portable?
Well, the driver would be free to implement $sth-last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be hidden in the
Tom Lane [EMAIL PROTECTED] writes:
INSERT/UPDATE ... RETURNING isn't something a driver can take
advantage
of.
It would require it to modify your statements which it can't do
safely. So
your application would have such non-portable SQL code written into
it.
Switch
databases and your
Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
His point stands though: if you are accessing Postgres through some kind
of connection-pooling software, currval() cannot be trusted across
transaction boundaries, since the
Tom Lane [EMAIL PROTECTED] writes:
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
How is what you're suggesting more portable?
Well, the driver would be free to implement $sth-last_insert_id() using
whatever proprietary extensions it has available. The
Tom Lane [EMAIL PROTECTED] writes:
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
How is what you're suggesting more portable?
For postgres it looks like currently it requires you to pass in the
table
and
field might even need a driver-specific hint telling
Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
How is what you're suggesting more portable?
Well, the driver would be free to implement $sth-last_insert_id() using
whatever proprietary extensions it has available. The non-portableness would
at least be
Greg Stark [EMAIL PROTECTED] writes:
This is from the DBI documentation -- that is, the non-driver-specific
abstract interface documentation.
Returns a value 'identifying' the row just inserted, if possible.
Typically this would be a value assigned by the database server
This portable function is so unportable that I see no reason to
accept it as precedent.
Hm. Instead of altering the syntax, what slipping in the last
inserted/updated tuple into the PQResult object? Maybe is a protocol
level option? Now everybody gets to use it with minimal muss.
Merlin
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Merlin Moncur wrote:
That is a shortcoming of the DBD::pg driver which really should be
returning a key (comprised of columns, some or none of which may be
defaulted by the server).
Actually, the spec comes from DBI, not DBD::Pg, and is
Attempts to return the id of the last value to be inserted into a table.
You can either provide a sequence name (preferred) or provide a table
name with optional schema. The $catalog and $field arguments are always
ignored.
The current value of the sequence is returned by a call to the
-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
On Tue, Feb 01, 2005 at 06:31:30PM +1100, John Hansen wrote:
Since OID's are now deprecated, and will eventually disappear,
wouldn't it be a good idea, to have INSERT and UPDATE return
a copy of the tuple that was inserted/updated?
How about the TID?
--
Alvaro Herrera ([EMAIL PROTECTED])
Since OID's are now deprecated, and will eventually disappear,
wouldn't it be a good idea, to have INSERT and UPDATE
return a copy of
the tuple that was inserted/updated?
How about the TID?
Yea, that'd work. As long as you can get an arbitrary column back out, 'as it
was at the time
John Hansen [EMAIL PROTECTED] writes:
Since OID's are now deprecated, and will eventually disappear,
No one has stated that they will disappear.
wouldn't it be a good idea, to have INSERT and UPDATE
return a copy of the tuple that was inserted/updated?
How about the TID?
Yea, that'd
I have a suggestion...
For libpq:
Since OID's are now deprecated, and will eventually disappear,
wouldn't it be a good idea, to have INSERT and UPDATE return
a copy of the tuple that was inserted/updated?
This way, you could have a funtion to fetch an arbitrary named
column from that tuple.
On Tue, 2005-02-01 at 11:24 -0300, Alvaro Herrera wrote:
How about the TID?
That wouldn't be sufficiently stable for use by client applications, I
believe: a concurrent VACUUM FULL could mean your TID no longer points
at what you think it does.
-Neil
---(end of
Neil Conway [EMAIL PROTECTED] writes:
On Tue, 2005-02-01 at 11:24 -0300, Alvaro Herrera wrote:
How about the TID?
That wouldn't be sufficiently stable for use by client applications, I
believe: a concurrent VACUUM FULL could mean your TID no longer points
at what you think it does.
It'd be
On Tue, 2005-02-01 at 17:50 -0500, Tom Lane wrote:
It'd be safe enough within the same transaction, since VACUUM can't kill
a tuple inserted by an open transaction; nor could VACUUM FULL touch the
table at all, since you'll be holding at least a writer's lock on the
table.
True, but it still
No one has stated that they will disappear.
Ohh,... just the impression I've been getting when speaking with people.
... John
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
With a default to return the primary key?
Of course, that would be ideal ... :)
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Michael Fuhr [EMAIL PROTECTED] writes:
On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
His point stands though: if you are accessing Postgres through some kind
of connection-pooling software, currval() cannot be trusted across
transaction boundaries, since the pool code might give
Tom Lane Writes:
Michael Fuhr [EMAIL PROTECTED] writes:
On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
His point stands though: if you are accessing Postgres
through some
kind of connection-pooling software, currval() cannot be trusted
across transaction boundaries, since
26 matches
Mail list logo