agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: ALMA TAHIR <almaheena2003@yahoo.co.in>
To: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: majordomo@postgresql.org <majordomo@postgresql.org>
Subject: pgsql-sql-owner
Date: Tue, 4 Mar 2014 13:38:10 +0800 (SGT)
Message-ID: <1393911490.4808.YahooMailNeo@web192701.mail.sg3.yahoo.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.....
Statement stmt = c.createStatement();
// Setup function to call.
stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '"
+ " DECLARE "
+ " call_log_rec call_log % rowtype; "
+ " call_log_cursor refcursor; "
+ " final_cursor refcursor; "
+ " idInt int[]; "
+ " BEGIN "
+ " OPEN call_log_cursor FOR
SELECT * FROM call_log WHERE aht_read_status = 0 ORDER BY
record_sequence_number ASC limit 20 FOR UPDATE; "
+ " LOOP "
+ " FETCH NEXT FROM call_log_cursor INTO call_log_rec; "
+ " EXIT WHEN call_log_rec IS NULL; "
+ " UPDATE call_log SET aht_read_status = 1 WHERE CURRENT OF call_log_cursor; "
+ " idInt := idInt || ARRAY [call_log_rec.record_sequence_number]; "
+ " END LOOP;"
+ " OPEN final_cursor FOR SELECT
record_sequence_number FROM call_log WHERE record_sequence_number =
ANY(idInt); "
+ " RETURN final_cursor; "
+ " END;' language plpgsql");
stmt.close();
// We must be inside a transaction for cursors to work.
c.setAutoCommit(false);
// Procedure call.
CallableStatement proc = c.prepareCall("{ ? = call refcursorfunc() }");
proc.registerOutParameter(1, Types.OTHER);
System.out.println("BEFORE::: Thread name::: " + Thread.currentThread().getName());
proc.execute();
ResultSet results =
(ResultSet) proc.getObject(1);
while (results.next()) {
// do something with the results...
System.out.println("Hurrey got the results from SP........");
System.out.println("AFTER::::Thread name::: " +
Thread.currentThread().getName()+ " record_sequence_number::::
"+results.getString(1));
}
c.commit();
results.close();
proc.close();
Thanks in advance Alma
Message-ID: <1393911490.4808.YahooMailNeo@web192701.mail.sg3.yahoo.com>
Permalink: ../1393911490.4808.YahooMailNeo@web192701.mail.sg3.yahoo.com/
Also on: postgresql.org/message-id/1393911490.4808.YahooMailNeo@web192701.mail.sg3.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: almaheena2003@yahoo.co.in, pgsql-sql-owner@postgresql.org, tgl@sss.pgh.pa.us, majordomo@postgresql.org
Subject: Re: pgsql-sql-owner
In-Reply-To: <1393911490.4808.YahooMailNeo@web192701.mail.sg3.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox