Function Issue!

It would be very helpful if anyone could help me with below issue.

I am using below stored proc:

CREATE OR REPLACE FUNCTION FETC= H_CALL_LOGS() RETURNS=0Arefcursor AS $$
=0ADECLARE
=0Acall_log_rec ca= ll_log % rowtype;
=0Acall_log_cursor refcursor;
=0ABEGIN
=0AOPEN c= all_log_cursor FOR
=0ASELECT *
=0AFROM
=0A call_log
=0A=    WHERE aht_read_status =3D 0
=0A    &nb= sp;     ORDER BY=0Arecord_sequence_number ASC limit 20 = FOR UPDATE;
=0ALOOP
=0A    FETCH NEXT FROM call_log_cu= rsor INTO call_log_rec;
=0A    EXIT WHEN call_log_rec IS = NULL;
=0A    UPDATE call_log SET aht_read_status =3D 1 WH= ERE=0Arecord_sequence_number =3D call_log_rec.record_sequence_number;
= =0AEND LOOP;
=0ARETURN call_log_cursor;
=0AEND;
=0A$$ LANGUAGE plp= gsql;

and trying to read response in java:

&nbs= p;       =0Ajava.sql.CallableStatement = proc =3D c.prepareCall("{ ? =3D call=0Afetch_call_logs() }");
=0A&= nbsp;         =0Ac.setAutoCommit(fa= lse);
=0A          = =0Aproc.registerOutParameter(1, java.sql.Types.OTHER);
=0A  &n= bsp;         =0Aproc.execute(); =0A             Res= ultSet=0Arset2 =3D (ResultSet) proc
=0A   =0A  =             &nb= sp; =0A.getObject(1);
=0A   =0A   &nb= sp;         while=0A(rset2.next()) = {
=0A   =0A       &nbs= p;       =0ASystem.out.println(rset2
= =0A   =0A        &nb= sp;          =0A.getStrin= g(1));
=0A   =0A       = ;      =0A}
=0A   =0A =            =0Arset2.= close();
=0A   =0A      &nb= sp;     //=0Ac.setAutoCommit(false);
=0A  = =0A           = =0Aproc.close();
=0A        &nb= sp;       =0Ac.close();

but i= am not able to get proper response back... if i comment out=0Athe fetch st= atement and tried doing some static update i am able to get proper=0Arespon= se back. Stucked up with this...

I want to open a ref cursor with select for update and then update=0Athe= records and get the ref cursor in response back in java.
=0ABut its not= happening .... if i return ref cursor only after select it works=0Afine bu= t after fetch when i am returning the response back i am not getting..=

=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A= =0A=0A=0A

Where am I doing the mistake or anythi= ng I am missing???? Please=0Ahelp me with the same ... it would be very hel= pful.....

---897285659-938561821-1393503792=:47079-- From tgl@sss.pgh.pa.us Thu Feb 27 14:33:03 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ21H-0005yX-K6 for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 14:33:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJ21H-0007EI-4q for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 14:33:03 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ21G-0007EC-Ds for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 14:33:02 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ21E-0006HA-Cg for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 14:33:01 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id s1REWxhN030403; Thu, 27 Feb 2014 09:32:59 -0500 From: Tom Lane To: ALMA TAHIR cc: "pgsql-sql@postgresql.org" Subject: Re: Function Issue In-reply-to: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> References: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> Comments: In-reply-to ALMA TAHIR message dated "Thu, 27 Feb 2014 20:23:12 +0800" Date: Thu, 27 Feb 2014 09:32:59 -0500 Message-ID: <30402.1393511579@sss.pgh.pa.us> X-Pg-Spam-Score: -0.0 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ALMA TAHIR writes: > I want to open a ref cursor with select for update and then update > the records and get the ref cursor in response back in java. Your function has already sucked all the rows out of the cursor before it returns it, so it's not surprising that further reads from the cursor produce nothing. You could try rewinding the cursor (see MOVE) but I'm not sure that will help in this case, since the function has carefully ensured that none of the rows pass the cursor query's WHERE condition anymore. I think that since the cursor used SELECT FOR UPDATE, it will not return the updated rows even after rewinding. (I could be wrong though, so it's worth trying.) I think you need to rethink what you're doing. This seems like a fairly silly application design: why not do all the processing you need on these rows in one place? Or at the very least, don't use one cursor to serve two masters. Possibly you could have the function return the rows itself instead of passing back a refcursor. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From almaheena2003@yahoo.co.in Fri Feb 28 13:16:09 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJNIO-0002xA-9f for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 13:16:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJNIN-0005Yo-Lm for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 13:16:07 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJNIM-0005Yi-I4 for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 13:16:06 +0000 Received: from nm32.bullet.mail.ne1.yahoo.com ([98.138.229.25]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJNIJ-00057m-My for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 13:16:05 +0000 Received: from [127.0.0.1] by nm32.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2014 13:16:01 -0000 Received: from [98.138.100.118] by nm32.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2014 13:13:11 -0000 Received: from [106.10.166.126] by tm109.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2014 13:13:10 -0000 Received: from [106.10.151.253] by tm15.bullet.mail.sg3.yahoo.com with NNFMP; 28 Feb 2014 13:13:10 -0000 Received: from [127.0.0.1] by omp1002.mail.sg3.yahoo.com with NNFMP; 28 Feb 2014 13:13:10 -0000 X-Yahoo-Newman-Property: ymail-4 X-Yahoo-Newman-Id: 376247.18338.bm@omp1002.mail.sg3.yahoo.com Received: (qmail 13707 invoked by uid 60001); 28 Feb 2014 13:13:10 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.in; s=s1024; t=1393593190; bh=KzObqkrNNPQsMtAfTsERTpqX66KYwTHQGvZ98T4yj6Q=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=W2satYub9ukbvQjOgRx71rS6n0909oxYFswJFVpOAAv2t2g0pXnHjCGhEbrcCWHn47wabwRfpMP+MDCNydOhbXPfvRwAMH33rlJ4LygineB8yTFmXqAKmMUsfbOL/3NqnzSx/k/usBaIK1cLXbDJmA1p+38d0qV1mOF9GibfmUc= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.in; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=wmzLdyMmKSUwV+nJBwc5gyyYpmGOTuOSXAbwn0afsFsQ6VhunDLGjuLytSdqnPulCmtZEJxHn7h6q2AakQpq0Hw3ujKLLzrLxMLbqzwrcHc3hX6aAwCFP7mS++3FPnxhK/KectDqoUGGIFTDFzXM4CT7w8T/Ko5ExdA5BgpD3nE=; X-YMail-OSG: _HGtUw8VM1mqH31RshU4fnB5Cs.IQSKqBSk_xi.4GIblshH 1IJiGjvH3m4DvtMnYHg68pWjQ3j54CRrYH8kFjnRvp7hFMTBEebf9EkiSWLn cA6FhbsIaugGD8IAdJ.3ZHTGOPdovPHoFTxzG.fPqoYzOdyqugJQwUx4JewA yIooX01BlD89ejCWNuNPITVjuqJic3YCuvzbrvrSc29HBzRs4d9c0PzShg7z QcgFwa1vELbIP53NSve7esPWKR2Op3ETGBJ7L1Q_Hjzr3QyrYSf.HY9iTKpj utGDvno2HwIk9rjjGeP3azNrwi0Cl_iwRCNud66JiI35edBybhXaoJBMeKLj Le6nWTsLPL6NV.GcazcPXvIeJY.Nid7TkOnvYCeNqYDdVcJVpDDw1elNptJn a1GVrx0894VlAe1wHj9RkmyApYYmuUA1wFtVvYUXM.24TeK_sWN_lD6BqUHG SVMdArvwHjT8CazzcXRG8xVblCATosba6yiHGP5VrSYw.L6E18Kdh8eFKnjH cJBPBRc94JFU8RFWTsG1mehiTvb8kar3NnbYD5Todj5IkVZPTnh5Uui_OEke HWNTKiEzp Received: from [155.70.23.45] by web192706.mail.sg3.yahoo.com via HTTP; Fri, 28 Feb 2014 21:13:10 SGT X-Rocket-MIMEInfo: 002.001, aGksCnRoYW5reW91IGZvciBzdWdnZXN0aW9ucywgaXRzIHdvcmtpbmcgbm93LiBJIG1hIHJldHJpZXZpbmcgdGhlIGlkcyBpbiBhbiBpbnRbXSBhbmQgdGhlbiB1c2luZyBvbmUgbW9yZSBjdXJzb3IgdG8gcmVhZCBhbmQgcmV0dXJuIHVzaW5nIHRoZSBpbnRbXS4gCgoKCgoKT24gVGh1cnNkYXksIDI3IEZlYnJ1YXJ5IDIwMTQgODowNCBQTSwgVG9tIExhbmUgPHRnbEBzc3MucGdoLnBhLnVzPiB3cm90ZToKIApBTE1BIFRBSElSIDxhbG1haGVlbmEyMDAzQHlhaG9vLmNvLmluPiB3cml0ZXM6Cj4gSSB3YW50IHQBMAEBAQE- X-Mailer: YahooMailWebService/0.8.177.636 References: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> <30402.1393511579@sss.pgh.pa.us> Message-ID: <1393593190.2407.YahooMailNeo@web192706.mail.sg3.yahoo.com> Date: Fri, 28 Feb 2014 21:13:10 +0800 (SGT) From: ALMA TAHIR Reply-To: ALMA TAHIR Subject: Re: Function Issue To: Tom Lane Cc: "pgsql-sql@postgresql.org" In-Reply-To: <30402.1393511579@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-897285659-318668661-1393593190=:2407" X-Pg-Spam-Score: -0.1 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ---897285659-318668661-1393593190=:2407 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable hi,=0Athankyou for suggestions, its working now. I ma retrieving the ids in= an int[] and then using one more cursor to read and return using the int[]= . =0A=0A=0A=0A=0A=0AOn Thursday, 27 February 2014 8:04 PM, Tom Lane wrote:=0A =0AALMA TAHIR writes:=0A= > I want to open a ref cursor with select for update and then update=0A> th= e records and get the ref cursor in response back in java.=0A=0AYour functi= on has already sucked all the rows out of the cursor before=0Ait returns it= , so it's not surprising that further reads from the cursor=0Aproduce nothi= ng.=0A=0AYou could try rewinding the cursor (see MOVE) but I'm not sure tha= t will=0Ahelp in this case, since the function has carefully ensured that n= one of=0Athe rows pass the cursor query's WHERE condition anymore.=A0 I thi= nk that=0Asince the cursor used SELECT FOR UPDATE, it will not return the u= pdated=0Arows even after rewinding.=A0 (I could be wrong though, so it's wo= rth=0A=0Atrying.)=0A=0AI think you need to rethink what you're doing.=A0 Th= is seems like a fairly=0Asilly application design: why not do all the proce= ssing you need on these=0Arows in one place?=A0 Or at the very least, don't= use one cursor to serve=0Atwo masters.=A0 Possibly you could have the func= tion return the rows itself=0Ainstead of passing back a refcursor.=0A=0A=A0= =A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane=0A=0A=0A-- =0ASent via pgsql-s= ql mailing list (pgsql-sql@postgresql.org)=0ATo make changes to your subscr= iption:=0Ahttp://www.postgresql.org/mailpref/pgsql-sql ---897285659-318668661-1393593190=:2407 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

hi,
thankyou for s= uggestions, its working now. I ma retrieving the ids in an int[] and then u= sing one more cursor to read and return using the int[].

<= br>

On Thursday, 27 February 2014 8:04 PM, Tom Lane <tgl@sss.pgh.pa.us&g= t; wrote:

ALMA TAHIR <= ;almaheena2003@yahoo.co.in> writes:> I want to open a ref cursor with select for update and= then update
> the records and get the ref cursor in response back in= java.

Your function has already sucke= d all the rows out of the cursor before
it returns it, so= it's not surprising that further reads from the cursor
p= roduce nothing.

You could try rewindin= g the cursor (see MOVE) but I'm not sure that will
help i= n this case, since the function has carefully ensured that none of
the rows pass the cursor query's WHERE condition anymore. I= think that
since the cursor used SELECT FOR UPDATE, it w= ill not return the updated
rows even after rewinding.&nbs= p; (I could be wrong though, so it's worth

trying.)

I think you need to rethink what you're doing. This seems= like a fairly
silly application design: why not do all the processing you= need on these
rows in one place? Or at the very le= ast, don't use one cursor to serve
two masters. Pos= sibly you could have the function return the rows itself
= instead of passing back a refcursor.

&= nbsp; regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@po= stgresql.org)
To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sq= l

=20

---897285659-318668661-1393593190=:2407-- From almaheena2003@yahoo.co.in Mon Mar 3 11:35:13 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKR9N-0006jc-11 for pgsql-sql@arkaria.postgresql.org; Mon, 03 Mar 2014 11:35:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WKR9M-0006hK-2I for pgsql-sql@arkaria.postgresql.org; Mon, 03 Mar 2014 11:35:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKR9J-0006gL-Il for pgsql-sql@postgresql.org; Mon, 03 Mar 2014 11:35:09 +0000 Received: from nm39.bullet.mail.ne1.yahoo.com ([98.138.229.32]) by magus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WKR9E-00058e-V4 for pgsql-sql@postgresql.org; Mon, 03 Mar 2014 11:35:08 +0000 Received: from [127.0.0.1] by nm39.bullet.mail.ne1.yahoo.com with NNFMP; 03 Mar 2014 11:35:02 -0000 Received: from [98.138.226.178] by nm39.bullet.mail.ne1.yahoo.com with NNFMP; 03 Mar 2014 11:32:03 -0000 Received: from [106.10.166.63] by tm13.bullet.mail.ne1.yahoo.com with NNFMP; 03 Mar 2014 11:32:02 -0000 Received: from [106.10.151.171] by tm20.bullet.mail.sg3.yahoo.com with NNFMP; 03 Mar 2014 11:32:02 -0000 Received: from [127.0.0.1] by omp1011.mail.sg3.yahoo.com with NNFMP; 03 Mar 2014 11:32:02 -0000 X-Yahoo-Newman-Property: ymail-4 X-Yahoo-Newman-Id: 302793.48590.bm@omp1011.mail.sg3.yahoo.com Received: (qmail 3824 invoked by uid 60001); 3 Mar 2014 11:32:02 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.in; s=s1024; t=1393846322; bh=vxDjTEfGgvbaVrz6aQ9Vlb2+6MYSYfY5HUlqSKPU0wI=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=VdTMcKTP82f4QCe4yKg/7e/JRjw2285MUK1YV9Ne0nHbQ9Q2WXdixALIKXobI1GpXvtbuxCG4CO0MEjqaT0SKm3kwVQjaBoviDUVayKY5K/J/ANvfT3cwzt47W9VDM4nOGeCrgaCgN2Zi8jiRr07lr8ORwo2P1fWahGxMYtPHo8= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.in; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=FxClkXQ+aflqAc5wukX4NKgQXpnC8Uok1WQYuL2QodjysOkYHwGeZ4ZNttuE07GTgLda+77rwX1UpCOasT77ebOGnDrX8X3tH02biSvInmXbBogES8WPHN+zxQ19iOLvqG+1eACZvuX19VW3yZpQAjrvX69iENwMA6l55gKm4p0=; X-YMail-OSG: Kiy6f5IVM1l6ivjTzOT0JP8ZQQAdN6KSZkP_I3R903u3b2U F9Cc9MIyMpimZTfltpXlimzGeeBsXNhIHrY5Xc2lO82owNVyfGs48CqzMQWI gqEl02kEwJEzIOkTaCYxWMQQkt2Il7wT1hixHqEQWPO90mz1sjk7CnzAU.Iz UOlIAjiPMIXlC955bg4UJGdvW2E7NPO9pOUOLZ4JpBFH2IPwjMGgMQoJVBAz ChGi7xuF5c6yCJiehIbCpmnVFUi6bl5aTl9.GtPbJhKRV5rMMSxD6vB.ks3s __X03EY8o.A0mO744hR6pyrID1aLnvOS8W8O0rbX.jcxJEjneIPeUqqAAq73 uBZiF6zO1I4AZjmPazXMaYX._gHmLW1BuztpcNF5_paZ2BVfohTmMCmIqStg a1YQ.xlL16Y7y4.nQCBW7IeMBMVmWkEWmzJIfGUI4wo_uklpfcNSe0I_bPih kjAxyZfLmdJ1KkJqhAlydAoEH9YmOgkYTv11ocYbakAogqzkAJ5R2IgGjqmw ljZQm1vRzkH6pv9dCDQZBUreuoAef5UvIHOQc0aZSSYQrwhZ1s6hOPlzu6ff _a6JIeLGX Received: from [155.70.39.45] by web192703.mail.sg3.yahoo.com via HTTP; Mon, 03 Mar 2014 19:32:02 SGT X-Rocket-MIMEInfo: 002.001, SGksCgpJIGFtIHVzaW5nIGJlbG93IGNvZGUgaW4gbXVsdGkgdGhyZWFkZWQgZW52aXJvbm1lbnQsIGJ1dCB3aGVuIG11bHRpcGxlIHRocmVhZHMgYXJlIGFjY2Vzc2luZyB0aGVuIGkgZ2V0IDogIm9yZy5wb3N0Z3Jlc3FsLnV0aWwuUFNRTEV4Y2VwdGlvbjogRVJST1I6IHR1cGxlIGNvbmN1cnJlbnRseSB1cGRhdGVkIiBleGNlcHRpb24uIEJ1dCBteSBjb25jZXJuIGlzIEkgbmVlZCB0byB1c2UgaXQgaW4gbXVsdGkgdGhyZWFkZWQgZW52LCBmb3IgdGhlIHNhbWUgcmVhc29uIEkgYW0gdXNpbmcgRk9SIFVQREEBMAEBAQE- X-Mailer: YahooMailWebService/0.8.177.636 References: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> <30402.1393511579@sss.pgh.pa.us> <1393593190.2407.YahooMailNeo@web192706.mail.sg3.yahoo.com> Message-ID: <1393846322.42246.YahooMailNeo@web192703.mail.sg3.yahoo.com> Date: Mon, 3 Mar 2014 19:32:02 +0800 (SGT) From: ALMA TAHIR Reply-To: ALMA TAHIR Subject: Re: Function Issue To: Tom Lane Cc: "pgsql-sql@postgresql.org" In-Reply-To: <1393593190.2407.YahooMailNeo@web192706.mail.sg3.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1446671062-1644711356-1393846322=:42246" X-Pg-Spam-Score: -1.5 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --1446671062-1644711356-1393846322=:42246 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi,=0A=0AI am using below code in multi threaded environment, but when mult= iple threads are accessing then i get : "org.postgresql.util.PSQLException:= ERROR: tuple concurrently updated" exception. But my concern is I need to = use it in multi threaded env, for the same reason I am using FOR UPDATE wit= h cursor. Then where is the issue??? Am I missing something????? Please hel= p me with the same.....=0A=0A=A0=A0=A0 =A0=A0=A0 Statement stmt =3D c.creat= eStatement();=0A=A0=A0=A0 =A0=A0=A0=A0=A0 // Setup function to call.=0A=A0= =A0=A0 =A0=A0=A0=A0=A0 stmt.execute("CREATE OR REPLACE FUNCTION refcursorfu= nc() RETURNS refcursor AS '"=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 + " DECLARE "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + "=A0= =A0=A0 call_log_rec call_log % rowtype; "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 + "=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 call_log_cursor ref= cursor; "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " final_curs= or refcursor; "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " idIn= t int[]; "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " BEGIN "=0A= =A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + "=A0=A0=A0 OPEN call_log= _cursor FOR SELECT * FROM call_log WHERE aht_read_status =3D 0 ORDER BY rec= ord_sequence_number ASC limit 20 FOR UPDATE; "=0A=A0=A0=A0 =A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0 + " LOOP "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 + " FETCH NEXT FROM call_log_cursor INTO call_log_rec; "=0A=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " EXIT WHEN call_log_rec IS NULL;= "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " UPDATE call_log SET= aht_read_status =3D 1 WHERE CURRENT OF call_log_cursor; "=0A=A0=A0=A0 =A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " idInt :=3D idInt || ARRAY [call_log_r= ec.record_sequence_number]; "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 + " END LOOP;"=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 + " O= PEN final_cursor FOR SELECT record_sequence_number FROM call_log WHERE reco= rd_sequence_number=A0 =3D ANY(idInt); "=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 + "=A0=A0=A0 RETURN final_cursor; "=0A=A0=A0=A0 =A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 + " END;' language plpgsql");=0A=A0=A0=A0 =A0= =A0=A0=A0=A0 stmt.close();=0A=A0=A0=A0 =A0=A0=A0=A0=A0 // We must be inside= a transaction for cursors to work.=0A=A0=A0=A0 =A0=A0=A0=A0=A0 c.setAutoCo= mmit(false);=0A=0A=A0=A0=A0 =A0=A0=A0=A0=A0 // Procedure call.=0A=A0=A0=A0 = =A0=A0=A0=A0=A0 CallableStatement proc =3D c.prepareCall("{ ? =3D call refc= ursorfunc() }");=0A=A0=A0=A0 =A0=A0=A0=A0=A0 proc.registerOutParameter(1, T= ypes.OTHER);=0A=A0=A0=A0 =A0=A0=A0=A0=A0 System.out.println("BEFORE::: Thre= ad name::: " + Thread.currentThread().getName());=0A=A0=A0=A0 =A0=A0=A0=A0= =A0 proc.execute();=0A=A0=A0=A0 =A0=A0=A0=A0=A0 =0A=A0=A0=A0 =A0=A0=A0=A0= =A0 ResultSet results =3D (ResultSet) proc.getObject(1);=0A=A0=A0=A0 =A0=A0= =A0=A0=A0 while (results.next()) {=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0 = // do something with the results...=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 System.out.println("Hurrey got the res= ults from SP........");=0A=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 System.out.println("AFTER::::Thread name::: " + Th= read.currentThread().getName()+ " record_sequence_number:::: "+results.getS= tring(1));=0A=A0=A0=A0 =A0=A0=A0=A0=A0 }=0A=A0=A0=A0 =A0=A0=A0=A0=A0 c.comm= it();=0A=A0=A0=A0 =A0=A0=A0=A0=A0 results.close();=0A=A0=A0=A0 =A0=A0=A0=A0= =A0 proc.close();=0A=0A=0A=0A=0AOn Friday, 28 February 2014 6:47 PM, ALMA T= AHIR wrote:=0A =0Ahi,=0Athankyou for suggestion= s, its working now. I ma retrieving the ids in an int[] and then using one = more cursor to read and return using the int[]. =0A=0A=0A=0A=0A=0AOn Thursd= ay, 27 February 2014 8:04 PM, Tom Lane wrote:=0A =0AALM= A TAHIR writes:=0A> I want to open a ref cursor= with select for update and then update=0A> the records and get the ref cur= sor in response back in java.=0A=0AYour function has already sucked all the= rows out of the cursor before=0Ait returns it, so it's not surprising that= further reads from the cursor=0Aproduce nothing.=0A=0AYou could try rewind= ing the cursor (see MOVE) but I'm not sure that will=0Ahelp in this case, s= ince the function has carefully ensured that none of=0Athe rows pass the cu= rsor query's WHERE condition anymore.=A0 I think that=0Asince the cursor us= ed SELECT FOR UPDATE, it will not return the updated=0Arows even after rewi= nding.=A0 (I could be wrong though, so it's worth=0A=0Atrying.)=0A=0AI thin= k you need to rethink what you're doing.=A0 This seems like a fairly=0Asill= y application design: why not do all the processing you need on these=0Arow= s in one place?=A0 Or at the very least, don't use one cursor to serve=0Atw= o masters.=A0 Possibly you could have the function return the rows itself= =0Ainstead of passing back a refcursor.=0A=0A=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 = regards, tom lane=0A=0A=0A-- =0ASent via pgsql-sql mailing list (pgsql-sql@= postgresql.org)=0ATo make changes to your subscription:=0Ahttp://www.postgr= esql.org/mailpref/pgsql-sql --1446671062-1644711356-1393846322=:42246 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

Hi,=

I am using below code in multi= threaded environment, but when multiple threads are accessing then i get := "org.postgresql.util.PSQLException: ERR= OR: tuple concurrently updated" exception. But my concern is I need = to use it in multi threaded env, for the same reason I am using FOR UPDATE = with cursor. Then where is the issue??? Am I missing something????? Please = help me with the same.....

    &nbs= p;   Statement stmt =3D c.createStatement();
&nb= sp;         // Setup function to call.          stmt.execute("CREATE OR= REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '"
  &n= bsp;            &nbs= p;            &= nbsp;           &nbs= p; + " DECLARE "
                &n= bsp;            = ;             += "    call_log_rec call_log % rowtype; "
  &nbs= p;             =             &nb= sp;            = + "            = ; call_log_cursor refcursor; "
       &nbs= p;            &= nbsp;           &nbs= p;         + " final_cursor refcurs= or; "
                &n= bsp;            = ;             += " idInt int[]; "
          = ;            &n= bsp;            = ;       + " BEGIN "
    &nb= sp;            =             &nb= sp;            + "&n= bsp;   OPEN call_log_cursor FOR SELECT * FROM call_log WHERE aht_= read_status =3D 0 ORDER BY record_sequence_number ASC limit 20 FOR UPDATE; = "
                &n= bsp;            = ;             += " LOOP "
           &= nbsp;           &nbs= p;            &= nbsp;     + " FETCH NEXT FROM call_log_cursor INTO call= _log_rec; "
           = ;            &n= bsp;            = ;      + " EXIT WHEN call_log_rec IS NULL; "
&n= bsp;               &n= bsp;            = ;             += " UPDATE call_log SET aht_read_status =3D 1 WHERE CURRENT OF call_log_curs= or; "
            = ;            &n= bsp;            = ;     + " idInt :=3D idInt || ARRAY [call_log_rec.recor= d_sequence_number]; "
         &= nbsp;           &nbs= p;            &= nbsp;       + " END LOOP;"
  &nb= sp;             &n= bsp;            = ;             += " OPEN final_cursor FOR SELECT record_sequence_number FROM call_log WHERE = record_sequence_number =3D ANY(idInt); "
    =             &nb= sp;            =             + " = ;   RETURN final_cursor; "
      &nbs= p;            &= nbsp;           &nbs= p;          + " END;' language= plpgsql");
          stmt.close();
          // We m= ust be inside a transaction for cursors to work.
    &nbs= p;     c.setAutoCommit(false);

   = ;       // Procedure call.
    &= nbsp;     CallableStatement proc =3D c.prepareCall("{ ?= =3D call refcursorfunc() }");
       &nbs= p; proc.registerOutParameter(1, Types.OTHER);
    &= nbsp;     System.out.println("BEFORE::: Thread name::: = " + Thread.currentThread().getName());
      &n= bsp;   proc.execute();
       &n= bsp;
          ResultSet = results =3D (ResultSet) proc.getObject(1);
    &nbs= p;    while (results.next()) {
              // do something wit= h the results...
          =             &nb= sp;   System.out.println("Hurrey got the results from SP........"= );
            &n= bsp;            = ; System.out.println("AFTER::::Thread name::: " + Thread.currentThread().ge= tName()+ " record_sequence_number:::: "+results.getString(1));
&nb= sp;       }
    &nbs= p;    c.commit();
       &n= bsp; results.close();
        &= nbsp; proc.close();

On Friday, 28 February 2014 6:47 PM,= ALMA TAHIR <almaheena2003@yahoo.co.in> wrote:

hi,
thankyou for suggestions, i= ts working now. I ma retrieving the ids in an int[] and then using one more= cursor to read and return using the int[].

<= br clear=3D"none">

On Thursday, 27 February 2014 8:04 PM, Tom Lane &l= t;tgl@sss.pgh.pa.us> wrote:

ALMA TAHIR <almaheena2003@yahoo.co.in> w= rites:
> I want to open a ref cursor with select for u= pdate and then update
> the records and get the ref cu= rsor in response back in java.

Your fu= nction has already sucked all the rows out of the cursor before
it returns it, so it's not surprising that further reads from the cu= rsor
produce nothing.

You could try rewinding the cursor (see MOVE) but I'm not sure that will<= br clear=3D"none">help in this case, since the function has carefully ensured that none of
the rows pass the cursor query's WHERE condition anymore. I think= that
since the cursor used SELECT FOR UPDATE, it will no= t return the updated
rows even after rewinding. (I = could be wrong though, so it's worth

trying.)

I think you need to rethink what you're doing= . This seems like a fairly
silly application design= : why not do all the processing you need on these
rows in= one place? Or at the very least, don't use one cursor to serve
two masters. Possibly you could have the function return= the rows itself
instead of passing back a refcursor.

&nb= sp; regards, tom lane

= --
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

=0A

--1446671062-1644711356-1393846322=:42246-- From adrian.klaver@aklaver.com Tue Mar 4 15:15:04 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKr3g-00037o-NS for pgsql-sql@arkaria.postgresql.org; Tue, 04 Mar 2014 15:15:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WKr3g-0008AC-1q for pgsql-sql@arkaria.postgresql.org; Tue, 04 Mar 2014 15:15:04 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKr3f-000898-45 for pgsql-sql@postgresql.org; Tue, 04 Mar 2014 15:15:03 +0000 Received: from new1-smtp.messagingengine.com ([66.111.4.221]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKr3c-0000m9-SG for pgsql-sql@postgresql.org; Tue, 04 Mar 2014 15:15:02 +0000 Received: from compute6.internal (compute6.nyi.mail.srv.osa [10.202.2.46]) by gateway1.nyi.mail.srv.osa (Postfix) with ESMTP id 81C04407; Tue, 4 Mar 2014 10:14:58 -0500 (EST) Received: from frontend2 ([10.202.2.161]) by compute6.internal (MEProxy); Tue, 04 Mar 2014 10:14:58 -0500 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=aklaver.com; h= message-id:date:from:mime-version:to:cc:subject:references :in-reply-to:content-type:content-transfer-encoding; s=mesmtp; bh=u6weSFAFVcjeMB72/RwgimjhF7I=; b=Xk+//kZPjFqBo7/ZtRvY+hD2ONQQ 2bnaJKrFnGfMSkocgQNBXKh3T3NgM3Fl0gyye/O3H5Z8YtaVLYab6PLUwSmnhlPP uA8KwLKKys1WSsG8zy61P04piUd+ckcioZFtlKYy3bQeoQCs929RvoMZdXcbrBu2 P4ZriCcVlOZN9g0= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=message-id:date:from:mime-version:to:cc :subject:references:in-reply-to:content-type :content-transfer-encoding; s=smtpout; bh=u6weSFAFVcjeMB72/Rwgim jhF7I=; b=hI3XVrmvBg9bPrbygSLyQKEwe8DBvZMSHRs/WadaJvREuB4X5S2jHK 1pCGShdXCE/kG/UnpfMdhNucWCyJn8vBnuzBzUlbgohkJ/KXsUpxDlTYMgPylZ2t S5CsC1Iho5cgLta3Mw0JwfZSnpjiJtoC6fhgCJsI5z3mGE4URddy4= X-Sasl-enc: 8vnpn4JPSkecXfdSfjxPwLEbFtVxW4G3W7wiHU89VEcp 1393946097 Received: from panda.site (unknown [97.113.11.16]) by mail.messagingengine.com (Postfix) with ESMTPA id 8C7336800E7; Tue, 4 Mar 2014 10:14:57 -0500 (EST) Message-ID: <5315EDF0.1020600@aklaver.com> Date: Tue, 04 Mar 2014 07:14:56 -0800 From: Adrian Klaver User-Agent: Mozilla/5.0 (X11; Linux i686; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: ALMA TAHIR , Tom Lane CC: "pgsql-sql@postgresql.org" Subject: Re: Function Issue References: <1393503792.47079.YahooMailNeo@web192706.mail.sg3.yahoo.com> <30402.1393511579@sss.pgh.pa.us> <1393593190.2407.YahooMailNeo@web192706.mail.sg3.yahoo.com> <1393846322.42246.YahooMailNeo@web192703.mail.sg3.yahoo.com> In-Reply-To: <1393846322.42246.YahooMailNeo@web192703.mail.sg3.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On 03/03/2014 03:32 AM, ALMA TAHIR wrote: > Hi, > > I am using below code in multi threaded environment, but when multiple > threads are accessing then i get : "org.postgresql.util.PSQLException: > ERROR: tuple concurrently updated" exception. But my concern is I need > to use it in multi threaded env, for the same reason I am using FOR > UPDATE with cursor. Then where is the issue??? Am I missing > something????? Please help me with the same..... I will say up front I am wandering out of my depth, but here it goes. I researched the above error message and it always seems to lead back to issue with a system catalog tuple getting concurrent updates. So, are we seeing all the queries that are happening when you run the function? In other words is there anything that touches a system catalog, say an ANALYZE? Are you sure your threads are using separate transactions and are not tromping over each other? What does the Postgres log show around the error message? > > > > > -- Adrian Klaver adrian.klaver@aklaver.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql