Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 44A019FB2CC for ; Tue, 24 Oct 2006 10:10:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 95336-02 for ; Tue, 24 Oct 2006 13:10:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by postgresql.org (Postfix) with SMTP id D8DDA9FB1FE for ; Tue, 24 Oct 2006 10:10:11 -0300 (ADT) Received: (qmail 1106 invoked from network); 24 Oct 2006 13:10:09 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 24 Oct 2006 13:10:09 -0000 In-Reply-To: <453DC2BB.70803@albourne.com> References: <453CC859.8070306@albourne.com> <23634.1161613423@sss.pgh.pa.us> <453D42E6.8020102@opencloud.com> <453DC2BB.70803@albourne.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <4AE92172-01D8-41B4-82C0-3B800B312555@fastcrypt.com> Cc: pgsql-jdbc@postgresql.org Content-Transfer-Encoding: 7bit From: Dave Cramer Subject: Re: currval() race condition on server? Date: Tue, 24 Oct 2006 09:10:08 -0400 To: Adriaan Joubert X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/106 X-Sequence-Number: 15989 On 24-Oct-06, at 3:37 AM, Adriaan Joubert wrote: > 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. currval works like this: When you execute nextval('sequence_name') the result of that is stored in your session. A session lives as long as this connection is alive. If nextval('sequence_name') has not been called in this session then currval('sequence_name') is undefined. Hopefully this helps Dave > > 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) > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >