agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: pgsql-sq-owner
2+ messages / 2 participants
[nested] [flat]
* Re: pgsql-sq-owner
@ 2014-03-04 05:35 ALMA TAHIR <almaheena2003@yahoo.co.in>
2014-03-04 15:08 ` Re: pgsql-sq-owner Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 2+ messages in thread
From: ALMA TAHIR @ 2014-03-04 05:35 UTC (permalink / raw)
To: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org>; pgsql-sql; Tom Lane <tgl@sss.pgh.pa.us>
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
On Thursday, 27 February 2014 5:38 PM, ALMA TAHIR <almaheena2003@yahoo.co.in> wrote:
Hi,
It would be very helpful if anyone could help me with below issue.
I am using below stored proc:
CREATE OR REPLACE FUNCTION FETCH_CALL_LOGS() RETURNS refcursor AS $$
DECLARE
call_log_rec call_log % rowtype;
call_log_cursor refcursor;
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 record_sequence_number = call_log_rec.record_sequence_number;
END LOOP;
RETURN call_log_cursor;
END;
$$ LANGUAGE plpgsql;
and trying to read response in java:
java.sql.CallableStatement proc = c.prepareCall("{ ? = call fetch_call_logs() }");
c.setAutoCommit(false);
proc.registerOutParameter(1, java.sql.Types.OTHER);
proc.execute();
ResultSet rset2 = (ResultSet) proc
.getObject(1);
while (rset2.next()) {
System.out.println(rset2
.getString(1));
}
rset2.close();
// c.setAutoCommit(false);
proc.close();
c.close();
but i ma not able to get proper response back... if i comment out the fetch statement and tried doing some static update i am able to get proper
response back. Stucked up with this...
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.
But
its not happening .... if i return ref cursor only after select it
works fine but after fetch when i am returning the response back i am
not getting..Where am I doing the mistake or anything I am missing???? Please help me with the same ... it would be very helpful.....
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pgsql-sq-owner
2014-03-04 05:35 Re: pgsql-sq-owner ALMA TAHIR <almaheena2003@yahoo.co.in>
@ 2014-03-04 15:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2014-03-04 15:08 UTC (permalink / raw)
To: ALMA TAHIR <almaheena2003@yahoo.co.in>; pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org>; pgsql-sql; Tom Lane <tgl@sss.pgh.pa.us>
On 03/03/2014 09:35 PM, 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....
So I assume this is a second attempt at your previous thread ' Function
Issue'. I will respond on the previous thread to keep it intact.
>
>
--
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-03-04 15:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-04 05:35 Re: pgsql-sq-owner ALMA TAHIR <almaheena2003@yahoo.co.in>
2014-03-04 15:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox