X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3E66AD1B4FF for ; Tue, 9 Sep 2003 06:54:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 38724-01 for ; Tue, 9 Sep 2003 03:54:23 -0300 (ADT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id DF92CD1B4E9 for ; Tue, 9 Sep 2003 03:54:19 -0300 (ADT) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h89C7C89002772; Tue, 9 Sep 2003 10:07:13 -0200 Date: Tue, 9 Sep 2003 10:07:12 -0200 (GMT+2) From: Achilleus Mantzios To: Chris Gamache Cc: pgsql-sql@postgresql.org Subject: Re: undefine currval() In-Reply-To: <20030908211254.80421.qmail@web13801.mail.yahoo.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/56 X-Sequence-Number: 15034 On Mon, 8 Sep 2003, Chris Gamache wrote: > I'm using sequences and currval() to retrieve the last inserted row in a table. > > > If currval() is undefined, as it is when a connection is made, then I know no > rows were inserted in that table and can take a different action. This is > problematic when using a connection pooling library, as the value of currval() > for any given sequence could possibly be set from a previous "connection". > > One (theoretical) workaround would be to issue some sort of command to the > back-end database to wipe all values of currval() when a "new" connection is > made. I've done some digging in the system tables and source code, and can't > find an obvious solution. Perhaps one you you gurus can suggest a SQL statement > to do such a thing. > > Alternately, if there is a better way to retrieve the last inserted row for any > given table, I'd be very grateful for the tip. It would need to be independent > of the connection history, and undefined if there has not been a row inserted > to the table during a definable interval of time (drop anchor when the > "connection" begins, raise anchor when the "connection" ends), and be > independant of the other connections inserting rows to the same table. > > Any idaeas? Are you writing in java? If yes, then implementing a wrapper around Connection would be a way. > > CG > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > > ---------------------------(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 > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr