Re: [HACKERS] [NOVICE] Last ID Problem
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) No. The thing everyone is ignoring here is that the INSERT command tag format is not something we can just go and change. You certainly could not put anything in it that wasn't an integer, and I'm not sure it would even be safe to put a bigint. So most of the cases you might actually want (timestamp, bigserial, etc) would be ruled out. Hardly worth inventing such a feature. OK, I didn't realise it was the command tag that was the issue here. I took a look at the libpq source to see how the INSERT tag works and it looks like it currently assumes a string of numbers. So as a minimum it would need some form of protocol extension to get this work (which I see from the archives that you were not keen to pursue). How is what you're suggesting more portable? I was hoping that it would only require minimal change (but obviously that is not the case). I think, out of the remaining options, that keeping with currval() is going to be the best approach - I can't really see the benefit of using a non-SQL standard command just for SQL insertion, mainly as you and others have suggested for portability reasons :( Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [NOVICE] Last ID Problem
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 get what they want, and do so in only one network round trip, without any artificial dependencies on OIDs or TIDs or anything else. It'd be unportable, but surely no more so than relying on OIDs or TIDs ... 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? It could be changed using something similar to ALTER TABLE x SET LASTINSERT TO y, but by default it would be set to the OID of the primary key of the table if the table specified WITHOUT OIDS at creation time, or the first column of the table otherwise. After the INSERT command, the value of the resulting is column is passed back to the client. I see that INSERT...RETURNING is a solution to the problem, but it seems somewhat strange to have to use an unportable command just to be able to return an identifier for the last inserted record... Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(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
Re: [HACKERS] [NOVICE] Last ID Problem
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 format is not something we can just go and change. You certainly could not put anything in it that wasn't an integer, and I'm not sure it would even be safe to put a bigint. So most of the cases you might actually want (timestamp, bigserial, etc) would be ruled out. Hardly worth inventing such a feature. I see that INSERT...RETURNING is a solution to the problem, but it seems somewhat strange to have to use an unportable command just to be able to return an identifier for the last inserted record... How is what you're suggesting more portable? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [NOVICE] Last ID Problem
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 the right thing would happen. 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 application code needs to be ported. -- greg ---(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
Re: [HACKERS] [NOVICE] Last ID Problem
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 driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [NOVICE] Last ID Problem
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 application code needs to be ported. I really don't think it matters. Currently, in PostgreSQL, there is only 'one true way' to have a real unique identifier for any given tuple that is persistent across queries and this is a sequence. Since sequences are basically managed by the app, your driver (I'm assuming) can't reliably use them. This is kind of similar to the issues being talked about wrt user locks. Because the lack of a true persistent tuple identifier, they require some data to be passed to them from the app (not really a big deal for them, however). From the point of view of your driver, the real solution is to bump oid to 64 bits and un-deprecate it. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [NOVICE] Last ID Problem
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 pool code might give your connection to someone else. In this situation the nextval-before-insert paradigm is the only way. I don't disagree with that; if the thread mentioned connection pooling then I must have overlooked it. (But in most of the applications I can think of, your uses of currval subsequent to an INSERT ought to be in the same transaction as the insert, so are perfectly safe. If your connection pooler takes control away from you within a transaction block, you need a less broken pooler...) That's the common situation I was talking about: doing an INSERT and immediately calling currval(), presumably in the same transaction. I should have been more clear about that and warned what could happen in other situations. Thanks. Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command that could be used to reset a connection between pooling assignments, so as to be sure that different pooled threads wouldn't see state that changes depending on what some other thread did. It seems like RESET CONNECTION ought to reset all currval() states to the error, currval not called yet condition. Comments? TODO update: * Add RESET CONNECTION command to reset all session state This would include resetting of all variables (RESET ALL), dropping of all temporary tables, removal of any NOTIFYs, cursors, prepared queries(?), currval()s, etc. This could be used for connection pooling. We could also change RESET ALL to have this functionality. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [NOVICE] Last ID Problem
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 non-portableness would at least be hidden in the driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. Well I'm not sure what you mean by portable. It's part of the DBI driver definition, so in theory it is. Not all drivers will implement it though, or implement it properly, and for some it may be more efficient than others. For postgres it looks like currently it requires you to pass in the table and field might even need a driver-specific hint telling it the sequence name. At least an application using it has a hope of working on a new driver. An application using RETURNING will only work on Oracle and one day Postgres. So it would be nice if the Postgres driver could efficiently implement it without having to do a second SELECT and without having to know out of band info like a sequence name. This is from the DBI documentation -- that is, the non-driver-specific abstract interface documentation. last_insert_id $rv = $dbh-last_insert_id($catalog, $schema, $table, $field); $rv = $dbh-last_insert_id($catalog, $schema, $table, $field, \%attr); Returns a value 'identifying' the row just inserted, if possible. Typically this would be a value assigned by the database server to a column with an auto_increment or serial type. Returns undef if the driver does not support the method or can't determine the value. The $catalog, $schema, $table, and $field parameters may be required for some drivers (see below). If you don't know the parameter values and your driver does not need them, then use undef for each. There are several caveats to be aware of with this method if you want to use it for portable applications: * For some drivers the value may only available immediately after the insert statement has executed (e.g., mysql, Informix). * For some drivers the $catalog, $schema, $table, and $field parameters are required (e.g., Pg), for others they are ignored (e.g., mysql). * Drivers may return an indeterminate value if no insert has been performed yet. * For some drivers the value may only be available if placeholders have not been used (e.g., Sybase, MS SQL). In this case the value returned would be from the last non-placeholder insert statement. * Some drivers may need driver-specific hints about how to get the value. For example, being told the name of the database 'sequence' object that holds the value. Any such hints are passed as driver-specific attributes in the \%attr parameter. * If the underlying database offers nothing better, then some drivers may attempt to implement this method by executing select max($field) from $table. Drivers using any approach like this should issue a warning if AutoCommit is true because it is generally unsafe - another process may have modified the table between your insert and the select. For situations where you know it is safe, such as when you have locked the table, you can silence the warning by passing Warn = 0 in \%attr. * If no insert has been performed yet, or the last insert failed, then the value is implementation defined. Given all the caveats above, it's clear that this method must be used with care. The last_insert_id method was added in DBI 1.38. -- greg ---(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
Re: [HACKERS] [NOVICE] Last ID Problem
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 it the sequence name. 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). The 'database supplied' integer assumption is bad, bad, bad. In fairness, getting the last returned key is a catastrophic limitation of sql that we must all work around (itself being a specific annoyance of that tricky devil, default columns). :-) The only thing that is going to meet your requirements is a system wide (well, at least table wide, but system wide would be better) 64 bit oid, which doesn't exist right now. Sequences (or more generally, defaulted columns) are application managed and difficult to deal with at the driver level. Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [NOVICE] Last ID Problem
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 hidden in the driver layer. Are you asserting that last_insert_id() is a portable function? I doubt it. I'm not familiar with the Perl interface, but JDBC has a standardized interface for this: http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int) http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#getGeneratedKeys() I tend to agree that a protocol-level change is easier to support in a driver. If it's done by extending INSERT/UPDATE, the driver will need to parse and modify queries which is hairy at the best of times. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
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 to a column with an auto_increment or serial type. Aside from the numerous serious problems pointed out in the documentation, this has an even more fatal objection, which is that it's unspecified what the result value is and thus there is no portable way of *using* the result after you have it. (If the PG driver returns an OID you certainly couldn't use that the same way as some other driver that returns a primary key ... especially a multicolumn primary key ...) This portable function is so unportable that I see no reason to accept it as precedent. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [NOVICE] Last ID Problem
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
-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 inspired by
MySQL's last_insert_id function. It is a poorly-speced function,
but we've done our best in the upcoming version of DBD::Pg,
which will support it.
Greg Stark wrote:
For postgres it looks like currently it requires you to pass in
the table and field might even need a driver-specific hint telling
it the sequence name.
For the record, the only required field for DBD::Pg will be the table
name, although the name of the sequence is highly encouraged. Here's
the docs for the next version, the first which supports lii:
last_insert_id
$rv = $dbh-last_insert_id($catalog, $schema, $table, $field);
$rv = $dbh-last_insert_id($catalog, $schema, $table, $field, \%attr);
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
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.
If you do not know the name of the sequence, you can provide a table name and
DBD::Pg will attempt to return the correct value. To do this, there must be at
least one column in the table with a CNOT NULL constraint, that has a unique
constraint, and which uses a sequence as a default value. If more than one
column
meets these conditions, the primary key will be used. This involves some
looking up of things in the system table, so DBD::Pg will cache the sequence
name for susequent calls. If you need to disable this caching for some reason,
you can control it via the 'pg_cache' attribute.
Please keep in mind that this method is far from foolproof, so make your
script use it properly. Specifically, make sure that it is called
immediately after the insert, and that the insert does not add a value
to the column that is using the sequence as a default value.
Some examples:
$dbh-do(CREATE SEQUENCE lii_seq START 1);
$dbh-do(CREATE TABLE lii (
foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
baz VARCHAR));
$SQL = INSERT INTO lii(baz) VALUES (?);
$sth = $dbh-prepare($SQL);
for (qw(uno dos tres quattro)) {
$sth-execute($_);
my $newid =
$dbh-last_insert_id(Cundef,undef,undef,undef,{sequence='lii_seq'});
print Last insert id was $newid\n;
}
If you did not want to worry about the sequence name:
$dbh-do(CREATE TABLE lii2 (
foobar SERIAL UNIQUE,
baz VARCHAR));
$SQL = INSERT INTO lii2(baz) VALUES (?);
$sth = $dbh-prepare($SQL);
for (qw(uno dos tres quattro)) {
$sth-execute($_);
my $newid = $dbh-last_insert_id(undef,undef,lii2,undef);
print Last insert id was $newid\n;
}
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200502022110
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCAYgSvJuQZxSWSsgRAgg3AJ4id98pta0CQR2w3xgwkxnph7qW4wCeMAJH
g/eXhtcmvXei9mESDDXg/s8=
=QaUa
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
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
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.
This suffers from the same problems that currval does when using
connection pools tho.
I previously suggested a function similar to last_insert_id in behaviour,
and have attached it to this email for reference.
Even so, this also suffers from the same problems when using a connection pool.
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.
--
John Hansen [EMAIL PROTECTED]
GeekNET
#include postgres.h
#include fmgr.h
#include storage/relfilenode.h
#include commands/sequence.h
static int64 _lastval = 0;
PG_FUNCTION_INFO_V1(nextval_new);
Datum nextval_new(PG_FUNCTION_ARGS) {
_lastval = DatumGetInt64(nextval(fcinfo));
PG_RETURN_INT64(_lastval);
}
PG_FUNCTION_INFO_V1(lastval);
Datum lastval(PG_FUNCTION_ARGS) {
PG_RETURN_INT64(_lastval);
}
SET search_path = pg_catalog;
BEGIN;
DELETE FROM pg_catalog.pg_proc WHERE proname = 'nextval';
CREATE FUNCTION nextval(text)
RETURNS bigint
AS 'lastval.so','nextval_new'
LANGUAGE 'C';
COMMENT ON FUNCTION nextval(text) IS 'sequence next value';
CREATE FUNCTION lastval()
RETURNS bigint
AS 'lastval.so','lastval'
LANGUAGE 'C';
COMMENT ON FUNCTION lastval() IS 'sequence last value';
COMMIT;
# -
lastval :
gcc -I /usr/include/postgresql/server/ -I /usr/include/postgresql/ -shared -o lastval.so lastval.c
strip lastval.so
install :
install -s -m 755 lastval.so $(DESTDIR)/usr/lib/postgresql/lib/;
clean :
rm -f *.o *~ core *.so;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502030012 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCAbQRvJuQZxSWSsgRArYMAKC4Kgsv153HHbC05AtraAh4O7oL9wCgtDmR zoucziPs5cyC1at00M8MC9w= =PDUD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [NOVICE] Last ID Problem
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]) El destino baraja y nosotros jugamos (A. Schopenhauer) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [NOVICE] Last ID Problem
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 it was committed'. Since not everything runs in a transaction,. And someone might have modified the row by the time you get to fetching it back out Or in terms of tuples,. No longer exist, if vacuum full have run... ... JOhn ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [NOVICE] Last ID Problem
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 work. You could only trust it for the duration of the inserting or updating transaction. Which might be enough ... but changing it would certainly break all existing apps that use this feature. 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 get what they want, and do so in only one network round trip, without any artificial dependencies on OIDs or TIDs or anything else. It'd be unportable, but surely no more so than relying on OIDs or TIDs ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [NOVICE] Last ID Problem
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. Like: last_insert_value(tuple,'column_name') With a default to return the primary key? Sincerely, Joshua D. Drake ... John ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
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 broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [NOVICE] Last ID Problem
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 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. But this is all moot since INSERT/UPDATE RETURNING is really the way to go, on grounds of functionality, speed, and not breaking backward compatibility for existing client code. regards, tom lane ---(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
Re: [HACKERS] [NOVICE] Last ID Problem
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 seems rather fragile -- it would be quite easy for people to get this wrong and not realize it (and then wonder why their application is silently corrupting data at odd times). Also, it might constrain out ability to improve how we garbage collect expired tuples in the future, although that's less of a concern. But this is all moot since INSERT/UPDATE RETURNING is really the way to go, on grounds of functionality, speed, and not breaking backward compatibility for existing client code. Agreed. Also, I believe we could do this without needing a protocol version bump. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [NOVICE] Last ID Problem
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]
Re: [HACKERS] [NOVICE] Last ID Problem
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
Re: [HACKERS] [NOVICE] Last ID Problem
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 your connection to someone else. In this situation the nextval-before-insert paradigm is the only way. I don't disagree with that; if the thread mentioned connection pooling then I must have overlooked it. (But in most of the applications I can think of, your uses of currval subsequent to an INSERT ought to be in the same transaction as the insert, so are perfectly safe. If your connection pooler takes control away from you within a transaction block, you need a less broken pooler...) That's the common situation I was talking about: doing an INSERT and immediately calling currval(), presumably in the same transaction. I should have been more clear about that and warned what could happen in other situations. Thanks. Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command that could be used to reset a connection between pooling assignments, so as to be sure that different pooled threads wouldn't see state that changes depending on what some other thread did. It seems like RESET CONNECTION ought to reset all currval() states to the error, currval not called yet condition. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [NOVICE] Last ID Problem
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 the pool code might give your connection to someone else. In this situation the nextval-before-insert paradigm is the only way. I don't disagree with that; if the thread mentioned connection pooling then I must have overlooked it. (But in most of the applications I can think of, your uses of currval subsequent to an INSERT ought to be in the same transaction as the insert, so are perfectly safe. If your connection pooler takes control away from you within a transaction block, you need a less broken pooler...) That's the common situation I was talking about: doing an INSERT and immediately calling currval(), presumably in the same transaction. I should have been more clear about that and warned what could happen in other situations. Thanks. Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command that could be used to reset a connection between pooling assignments, so as to be sure that different pooled threads wouldn't see state that changes depending on what some other thread did. It seems like RESET CONNECTION ought to reset all currval() states to the error, currval not called yet condition. Comments? 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. Like: last_insert_value(tuple,'column_name') ... John ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
