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--