Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKi3W-0000qm-P5 for pgsql-sql@arkaria.postgresql.org; Tue, 04 Mar 2014 05:38:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WKi3V-0003Mm-On for pgsql-sql@arkaria.postgresql.org; Tue, 04 Mar 2014 05:38:17 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKi3U-0003Mg-Qp for pgsql-sql@postgresql.org; Tue, 04 Mar 2014 05:38:17 +0000 Received: from nm10-vm2.bullet.mail.sg3.yahoo.com ([106.10.148.225]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WKi3Q-0007El-Bx for pgsql-sql@postgresql.org; Tue, 04 Mar 2014 05:38:16 +0000 Received: from [106.10.166.124] by nm10.bullet.mail.sg3.yahoo.com with NNFMP; 04 Mar 2014 05:38:10 -0000 Received: from [106.10.151.187] by tm13.bullet.mail.sg3.yahoo.com with NNFMP; 04 Mar 2014 05:38:10 -0000 Received: from [127.0.0.1] by omp1013.mail.sg3.yahoo.com with NNFMP; 04 Mar 2014 05:38:10 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 550574.40528.bm@omp1013.mail.sg3.yahoo.com Received: (qmail 48627 invoked by uid 60001); 4 Mar 2014 05:38:10 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.in; s=s1024; t=1393911490; bh=XiNgQrvrO31ZAlkRQbybKUEAdADxzB58Larb2WrPvuk=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=I1KF9tZ868/2kn0WLAbl9qVP5SV8aJfyURn1qO4+4+vIo3Bg7mkQLE/MQzUot8yAWCJSyHroz7hUSKItQTShVrEQI53ImkdpqnPMERHA8lG1YSymzDLNGmBy2uZuuJS+Ar5AHlmnTuR1mkZFgTwCtYT/7sUzFMMWo33qWhQFuTM= 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:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=qqg0V4P+pifduZXXtieoluPWNQOkjorzn4graO9eOkFXJXkn34y36X9AEU0d453jH+LRwEOn9BXTRfGl78bPEcdi7G9M/Eqnr7PTFJ6w5MoRWoPJRulAzbGkDegSMdqSDpL0WkA9D403Scfn47Lmtqf7M+colFcAcX64K2D4iOk=; X-YMail-OSG: SLgyCcQVM1mF1TKgP7Z5cXZf.G5K5vX11DYCiyvadcwaIm6 LzI7msf.TTl6EWJ7v1t3h_tnvnrn6S2j94HYH0_84xrs4S2HkrffLtidmVE6 6gF88c6jCbaTKTrNjJMm6OSpHv1tBcu8h2AccNUupisYg37aJuX6.1CPxORB _Py5v9jWHvMOFpPM9_LSGMtmcD3XEgJVpPJejtckI8GFUk_eA3NcQ9zewH.w _3PBpJXMWX36xVTNQ35wkY4asECW9uT3hcYarNGvrQbseqsuYj8LpENJytpK cNulriTfiy8_6IzkaYj1x7.ssv0nkcN6YyAlgs7coKQscqVbc1yxrLdJOBJu MQybvmp82gg.QqTsHyq_yQyMwOFalVWpJ5CkapFdsvxZJWRlHHHGm3g.ozwB VaJPasQUz4zNurH8fmsWCD3Ncpj1EEiof9G0jrs39ZmqrrVstHXoT_twJqil Ml4h_4CxV1_BmQKS_0S9b2SKW1iCiR.cKdZTU.5hCXvopuZ5Iz9yjUWR3K8O 5iC88V3xY5HOfQfDvZyyZ63roLunTRsdt4wzHk10pjzMhjwwHLQUv Received: from [155.70.23.45] by web192701.mail.sg3.yahoo.com via HTTP; Tue, 04 Mar 2014 13:38:10 SGT X-Rocket-MIMEInfo: 002.001, SGksCgpJIGFtIHVzaW5nIGJlbG93IGNvZGUgaW4gbXVsdGkgdGhyZWFkZWQgZW52aXJvbm1lbnQsIGJ1dCB3aGVuIG11bHRpcGxlIHRocmVhZHMgYXJlIGFjY2Vzc2luZyB0aGVuIGkgZ2V0IDogIm9yZy5wb3N0Z3Jlc3FsLnV0aWwuUFNRTEV4Y2VwdGlvbjogRVJST1I6IHR1cGxlIGNvbmN1cnJlbnRseSB1cGRhdGVkIiBleGNlcHRpb24uIEJ1dCBteSBjb25jZXJuIGlzIEkgbmVlZCB0byB1c2UgaXQgaW4gbXVsdGkgdGhyZWFkZWQgZW52LCAKZm9yIHRoZSBzYW1lIHJlYXNvbiBJIGFtIHVzaW5nIEZPUiBVUEQBMAEBAQE- X-Mailer: YahooMailWebService/0.8.177.636 Message-ID: <1393911490.4808.YahooMailNeo@web192701.mail.sg3.yahoo.com> Date: Tue, 4 Mar 2014 13:38:10 +0800 (SGT) From: ALMA TAHIR Reply-To: ALMA TAHIR Subject: pgsql-sql-owner To: "pgsql-sql-owner@postgresql.org" , "pgsql-sql@postgresql.org" , Tom Lane , "majordomo@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-1452436326-1035665438-1393911490=:4808" X-Pg-Spam-Score: -0.4 (/) 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 ---1452436326-1035665438-1393911490=:4808 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, =0Afor 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.....=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=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 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 = 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 + " final_c= ursor refcursor; "=0A=A0=A0=A0=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 + = " idInt 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 + " BEGI= N "=0A=A0=A0=A0=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 OPEN= call_log_cursor FOR =0ASELECT * FROM call_log WHERE aht_read_status =3D 0 = ORDER BY =0Arecord_sequence_number ASC limit 20 FOR UPDATE; "=0A=A0=A0=A0= =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 + " 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_l= og_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=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= + " UPDATE call_log SET aht_read_status =3D 1 WHERE CURRENT OF call_log_cu= rsor; "=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 id= Int || ARRAY [call_log_rec.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=0A=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 + " OPEN final_cursor FOR SELECT =0Arecord_sequenc= e_number FROM call_log WHERE record_sequence_number=A0 =3D =0AANY(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 fin= al_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;' l= anguage plpgsql");=0A=A0=A0=A0 =A0=A0=A0=A0=A0=0A 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.setAutoCommit(false);=0A=0A=A0=A0=A0 =A0=A0=A0= =A0=A0 // Procedure call.=0A=A0=A0=A0 =A0=A0=A0=A0=A0 CallableStatement pro= c =3D c.prepareCall("{ ? =3D call refcursorfunc() }");=0A=A0=A0=A0 =A0=A0= =A0=A0=A0 proc.registerOutParameter(1, Types.OTHER);=0A=A0=A0=A0 =A0=A0=A0= =A0=A0 System.out.println("BEFORE::: Thread name::: " + Thread.currentThrea= d().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=0A (ResultS= et) proc.getObject(1);=0A=A0=A0=A0 =A0=A0=A0=A0=A0 while (results.next()) {= =0A=A0=A0=A0=0A =A0=A0=A0=A0=A0=A0=A0=A0=A0 // do something with the result= s...=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 results from SP........");=0A=A0= =A0=A0=0A =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 S= ystem.out.println("AFTER::::Thread name::: " + =0AThread.currentThread().ge= tName()+ " record_sequence_number:::: =0A"+results.getString(1));=0A=A0=A0= =A0 =A0=A0=A0=A0=A0 }=0A=A0=A0=A0 =A0=A0=A0=A0=A0 c.commit();=0A=A0=A0=A0 = =A0=A0=A0=A0=A0 results.close();=0A=A0=A0=A0 =A0=A0=A0=A0=A0 proc.close();= =0A=0AThanks in advance Alma ---1452436326-1035665438-1393911490=:4808 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,=

I am using below code in mu= lti threaded environment, but when multiple threads are accessing then i ge= t : "org.postgresql.util.PSQLException: E= RROR: tuple concurrently updated"=0A exception. But my concern is I = need to use it in multi threaded env, =0Afor the same reason I am using FOR= UPDATE with cursor. Then where is the=0A issue??? Am I missing something??= ??? Please help me with the same.....
=  &n= bsp;      Statement stmt =3D c.createStatement();
  &nb= sp;       // Setup function to call.
          stmt.execute("CREATE= OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '"
            &nbs= p;            &= nbsp;           &nbs= p;    + " DECLARE "
   =0A =             &nb= sp;            =              + = "    call_log_rec call_log % rowtype; "
&n= bsp;            &nbs= p;            &= nbsp;           &nbs= p;   + "         &nb= sp;   call_log_cursor refcursor; "
  =              &n= bsp;            = ;            &n= bsp; + " final_cursor refcursor; "
   =0A =             &nb= sp;            =              + = " idInt int[]; "
       &nb= sp;            =             &nb= sp;         + " BEGIN "
   =0A        &nbs= p;            &= nbsp;           &nbs= p;     + "    OPEN call_log_cursor FOR = =0ASELECT * FROM call_log WHERE aht_read_status =3D 0 ORDER BY =0Arecord_se= quence_number ASC limit 20 FOR UPDATE; "
  &nbs= p;=0A            &nb= sp;            =             &nb= sp; + " LOOP "
        = ;            &n= bsp;            = ;         + " FETCH NEXT FROM call_= log_cursor INTO call_log_rec; "
     =             &nb= sp;            =             + " EXIT= WHEN call_log_rec IS NULL; "
   =0A  = ;            &n= bsp;            = ;            + " UPD= ATE call_log SET aht_read_status =3D 1 WHERE CURRENT OF call_log_cursor; "<= br clear=3D"none">          &n= bsp;            = ;            &n= bsp;      + " idInt :=3D idInt || ARRAY [call_log_= rec.record_sequence_number]; "
     &= nbsp;           &nbs= p;            &= nbsp;           + " END L= OOP;"
   =0A=0A     &n= bsp;            = ;            &n= bsp;        + " OPEN final_cursor FOR SE= LECT =0Arecord_sequence_number FROM call_log WHERE record_sequence_number&n= bsp; =3D =0AANY(idInt); "
      =             &nb= sp;            =            + "  = ;  RETURN final_cursor; "
     &= nbsp;           &nbs= p;            &= nbsp;           + " END;'= language plpgsql");
       = ;  =0A stmt.close();
     &= nbsp;    // We must be inside a transaction for cursors to w= ork.
          c.= setAutoCommit(false);

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

Thanks in advance Alma
---1452436326-1035665438-1393911490=:4808--