Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3D0349FB422 for ; Tue, 24 Oct 2006 04:37:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 99899-01 for ; Tue, 24 Oct 2006 04:37:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from nguni.albourne.com (nguni.albourne.com [213.207.142.98]) by postgresql.org (Postfix) with ESMTP id 536E99FB36D for ; Tue, 24 Oct 2006 04:37:31 -0300 (ADT) Received: from [192.168.51.186] ([192.168.51.186]) by nguni.albourne.com (8.13.3/8.13.3/Albourne/FW/DNSBL) with ESMTP id k9O7bQeQ007570 for ; Tue, 24 Oct 2006 10:37:27 +0300 (EEST) Message-ID: <453DC2BB.70803@albourne.com> Date: Tue, 24 Oct 2006 10:37:31 +0300 From: Adriaan Joubert User-Agent: Thunderbird 1.5.0.7 (X11/20060922) MIME-Version: 1.0 To: pgsql-jdbc@postgresql.org Subject: Re: currval() race condition on server? References: <453CC859.8070306@albourne.com> <23634.1161613423@sss.pgh.pa.us> <453D42E6.8020102@opencloud.com> In-Reply-To: <453D42E6.8020102@opencloud.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.816 tagged_above=0 required=5 tests=SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: *** X-Archive-Number: 200610/105 X-Sequence-Number: 15988 Oliver Jowett wrote: > The driver should split this query on the semicolons and send > Parse/Bind/Execute for each part, followed by a Sync at the end. If it > ends up using named statements due to JDBC statement reuse, it should > use a different name for each part. Of course there might be unknown > bugs in there, but it has been designed to handle this sort of compound > statement.. > > To the OP: Is autocommit on or off? Can you reproduce the problem with a > URL parameter of loglevel=2 and see what it looks like? (that'll > generate a trace protocol-level messages) autocommit is off. I have not been able to reproduce this with logging on - even with logging off the problem only occurs intermittently. Looking at the output from the trace everything seems to happen as expected - see trace below. Yet we are getting a few of these errors in our logs every day - obviously only the first time we ask for the value of the sequence back. Scary thought is that may be getting an older value back in other instances. It feels an awful lot like a timing issue where the sequence number is retrieved, but there is a delay until currval can use it. I'm not sure how currval works. I will continue to try to reproduce this with more logging, but would appreciate any ideas. Thanks! Adriaan <=BE CommandStatus(BEGIN) <=BE ParseComplete [null] <=BE BindComplete [null] <=BE RowDescription(1) <=BE DataRow <=BE CommandStatus(SELECT) <=BE ReadyForQuery(T) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@87e9bf, maxRows=0, fetchSize=0, flags=1 FE=> Parse(stmt=null,query="UPDATE prm SET ... AND _audit=$16",oids={23,23,701,23,701,23,23,0,701,701,701,0,0,23,23,23}) FE=> Bind(stmt=null,portal=null,$1=<421>,$2=<1>,$3=<0.21927195726630877>,$4=<18>,$5=<0.376345008550404>,$6=<2>,$7=<1>,$8=,$9=<0.045037791453060014>,$10=<0.020180805785169714>,$11=<1.0>,$12=,$13=,$14=<387>,$15=<435>,$16=<3382993>) FE=> Describe(portal=null) FE=> Execute(portal=null,limit=0) FE=> Parse(stmt=null,query=" SELECT currval('ip_audit_seq')",oids={}) FE=> Bind(stmt=null,portal=null) FE=> Describe(portal=null) FE=> Execute(portal=null,limit=0) FE=> Sync <=BE ParseComplete [null] <=BE BindComplete [null] <=BE NoData <=BE CommandStatus(UPDATE 1) <=BE ParseComplete [null] <=BE BindComplete [null] <=BE RowDescription(1) <=BE DataRow <=BE CommandStatus(SELECT) <=BE ReadyForQuery(T) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Connection$TransactionCommandHandler@1033450, maxRows=0, fetchSize=0, flags=22 FE=> Bind(stmt=S_2,portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE BindComplete [null] <=BE CommandStatus(COMMIT) <=BE ReadyForQuery(I)