Thread: clarification needed in postgresql... + transactions...
hi all,
i need a clarification in java + postgresql.
currently iam using tomcat 4.0, jdk 1.4, postgresql
7.3.x.
i using the below code to fetch data from database,
con =
DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test","wsas",
"wsas");
//con.setAutoCommit(false);
System.out.println(con.getAutoCommit());
preStmt = con.prepareStatement("BEGIN;SELECT
fn_list_allpatients('cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name";
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\";END;");
resultSet = preStmt.executeQuery();
//con.setAutoCommit(true);
while (resultSet.next())
{
System.out.println(resultSet.getString(1) +
resultSet.getString("patient_title"));
}
if i dont use the setautocommit to false and true
respectively the above code is not working,
i need to know will the above code create any problem
in multiuser application...
currently iam facing a problem that some transactions
are ideal even after closing the connection to
database...
please advice..
thanks
dinakar
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
On Mon, 19 Jan 2004, dinakar wrote:
> hi all,
>
> i need a clarification in java + postgresql.
>
> currently iam using tomcat 4.0, jdk 1.4, postgresql
> 7.3.x.
>
> i using the below code to fetch data from database,
>
> con =
> DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test","wsas",
> "wsas");
>
> //con.setAutoCommit(false);
> System.out.println(con.getAutoCommit());
> preStmt = con.prepareStatement("BEGIN;SELECT
> fn_list_allpatients('cursor_name');");
> resultSet = preStmt.executeQuery();
> String strCn = "cursor_name";
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\";END;");
> resultSet = preStmt.executeQuery();
> //con.setAutoCommit(true);
> while (resultSet.next())
> {
> System.out.println(resultSet.getString(1) +
> resultSet.getString("patient_title"));
> }
>
> if i dont use the setautocommit to false and true
> respectively the above code is not working,
Writing BEGIN and END in your own code is frowned upon. Using
setAutoCommit and commit should be all you need.
> currently iam facing a problem that some transactions
> are ideal even after closing the connection to
> database...
You are probably not closing the connection. This could be the case of
just a missing close() or poor exception handling. If you post a self
contained test case someone will likely be able to identify your problem.
Kris Jurka
> hi all,
>
> i need a clarification in java + postgresql.
>
> currently iam using tomcat 4.0, jdk 1.4, postgresql
> 7.3.x.
>
> i using the below code to fetch data from database,
>
> con =
>
DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test",
"wsas",
> "wsas");
>
> //con.setAutoCommit(false);
> System.out.println(con.getAutoCommit());
> preStmt = con.prepareStatement("BEGIN;SELECT
> fn_list_allpatients('cursor_name');");
> resultSet = preStmt.executeQuery();
> String strCn = "cursor_name";
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\";END;");
> resultSet = preStmt.executeQuery();
> //con.setAutoCommit(true);
> while (resultSet.next())
> {
> System.out.println(resultSet.getString(1) +
> resultSet.getString("patient_title"));
> }
>
> if i dont use the setautocommit to false and true
> respectively the above code is not working,
I think this is not a problem in the behaviour of the JDBC driver.
I don't know the details in the case of PostgreSQL, but I'm quite sure that
its behaviour follows the behaviour of every other DBMS I know, i.e., when
you
execute a commit, not only updates/deletes/inserts are committed, but also
all the cursor are closed.
So, you are trying to access a cursor already closed with the FETCH
statement and this is most probably the reason because it doesn't work.
Having said this, I think that there are some odd things in your code (the
problem here is that we don't know what your fn_list_allpatients function
shoud do):
- why are you executing the query again? after the first executeQuery you
should have already everything you need in resultSet.
- you are using 'cursor_name' both as a parameter for the function and as
the name for the cursor. Maybe they are two different things, isn't it?
> i need to know will the above code create any problem
> in multiuser application...
Difficult to say: what are you doing in the function? [from a Java point of
view, difficult to say, not knowing the environment in which you are running
the code above and not knowing which is the scope and definition of the
resultSet variable (we can just imagine that this is a java.sql.ResultSet,
but we cannot say, for example, if it is static and you are running that
code inside of a servlet)... however these are not issues which are
pertinent with pgsql-jdbc.]
> currently iam facing a problem that some transactions
> are ideal even after closing the connection to
> database...
Please, explain better. What do you mean with ideal? (sorry, maybe this is
due to the fact I'm italian and there could be meanings I don't know for an
english word)
Bye
Alessandro Depase
Hi Depase,
sorry it was due spell check.. the word 'idle' changed
to 'ideal'..
let me explain my problem once again..
currently i am using Tomcat 4.x, Postgresql 7.3.x,
Linux OS, JSP, Java for buliding a web application.
i received a mail from my client stating that : they
are facing some problem in postgres database server.
when ever the database grows in size, postgres is
trying to use Vacuum database option to shrink it.
while do so, the vacuum process would be unable to
free up dead rows, because they'd still be accessible
to some idle transactions...
whenever my application throws any exception the
transactions are left unhandled so like this some
transactions are idle even after some 5 to 6 days...
now the problem comes to the server memory. it is
occupying lot of memory..
---previously i was using the below code.....
preStmt = con.prepareStatement("BEGIN; SELECT
fn_list_patientsaudit('" + pstrPatientName + "'," +
intNoRecords + "," + intStart + ",'cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name" ;
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\"; END;");
resultSet = preStmt.executeQuery();
while (resultSet.next()) {
---------
------
}
instead of getting the cursor name from the postgres,
i am giving my own name to the cursor.
--client reports the problem... now i have changed my
code to below...
con.setAutoCommit(false);
preStmt = con.prepareStatement("BEGIN;SELECT
fn_list_allpatients('cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name";
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\";END;");
resultSet = preStmt.executeQuery();
while (resultSet.next())
{
-----
-----
}
con.setAutoCommit(true);
iam using finally block to close my connection and
connction related objects...
will this work without any problem ?. i mean will this
work for 500 users simul. without any problem...
please advice...
thanks,
dinakar
--- Alessandro Depase <alessandro.depase@libero.it>
wrote:
> > hi all,
> >
> > i need a clarification in java + postgresql.
> >
> > currently iam using tomcat 4.0, jdk 1.4,
> postgresql
> > 7.3.x.
> >
> > i using the below code to fetch data from
> database,
> >
> > con =
> >
>
DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test",
> "wsas",
> > "wsas");
> >
> > //con.setAutoCommit(false);
> > System.out.println(con.getAutoCommit());
> > preStmt = con.prepareStatement("BEGIN;SELECT
> > fn_list_allpatients('cursor_name');");
> > resultSet = preStmt.executeQuery();
> > String strCn = "cursor_name";
> > preStmt = con.prepareStatement("FETCH ALL IN \"" +
> > strCn + "\";END;");
> > resultSet = preStmt.executeQuery();
> > //con.setAutoCommit(true);
> > while (resultSet.next())
> > {
> > System.out.println(resultSet.getString(1) +
> > resultSet.getString("patient_title"));
> > }
> >
> > if i dont use the setautocommit to false and true
> > respectively the above code is not working,
>
> I think this is not a problem in the behaviour of
> the JDBC driver.
> I don't know the details in the case of PostgreSQL,
> but I'm quite sure that
> its behaviour follows the behaviour of every other
> DBMS I know, i.e., when
> you
> execute a commit, not only updates/deletes/inserts
> are committed, but also
> all the cursor are closed.
> So, you are trying to access a cursor already closed
> with the FETCH
> statement and this is most probably the reason
> because it doesn't work.
>
> Having said this, I think that there are some odd
> things in your code (the
> problem here is that we don't know what your
> fn_list_allpatients function
> shoud do):
> - why are you executing the query again? after the
> first executeQuery you
> should have already everything you need in
> resultSet.
> - you are using 'cursor_name' both as a parameter
> for the function and as
> the name for the cursor. Maybe they are two
> different things, isn't it?
>
> > i need to know will the above code create any
> problem
> > in multiuser application...
>
> Difficult to say: what are you doing in the
> function? [from a Java point of
> view, difficult to say, not knowing the environment
> in which you are running
> the code above and not knowing which is the scope
> and definition of the
> resultSet variable (we can just imagine that this is
> a java.sql.ResultSet,
> but we cannot say, for example, if it is static and
> you are running that
> code inside of a servlet)... however these are not
> issues which are
> pertinent with pgsql-jdbc.]
>
> > currently iam facing a problem that some
> transactions
> > are ideal even after closing the connection to
> > database...
>
> Please, explain better. What do you mean with ideal?
> (sorry, maybe this is
> due to the fact I'm italian and there could be
> meanings I don't know for an
> english word)
>
> Bye
> Alessandro Depase
>
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
Hi Depase,
sorry it was due spell check.. the word 'idle' changed
to 'ideal'..
let me explain my problem once again..
currently i am using Tomcat 4.x, Postgresql 7.3.x,
Linux OS, JSP, Java for buliding a web application.
i received a mail from my client stating that : they
are facing some problem in postgres database server.
when ever the database grows in size, postgres is
trying to use Vacuum database option to shrink it.
while do so, the vacuum process would be unable to
free up dead rows, because they'd still be accessible
to some idle transactions...
whenever my application throws any exception the
transactions are left unhandled so like this some
transactions are idle even after some 5 to 6 days...
now the problem comes to the server memory. it is
occupying lot of memory..
---previously i was using the below code.....
preStmt = con.prepareStatement("BEGIN; SELECT
fn_list_patientsaudit('" + pstrPatientName + "'," +
intNoRecords + "," + intStart + ",'cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name" ;
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\"; END;");
resultSet = preStmt.executeQuery();
while (resultSet.next()) {
---------
------
}
instead of getting the cursor name from the postgres,
i am giving my own name to the cursor.
--client reports the problem... now i have changed my
code to below...
con.setAutoCommit(false);
preStmt = con.prepareStatement("BEGIN;SELECT
fn_list_allpatients('cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name";
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\";END;");
resultSet = preStmt.executeQuery();
while (resultSet.next())
{
-----
-----
}
con.setAutoCommit(true);
iam using finally block to close my connection and
connction related objects...
will this work without any problem ?. i mean will this
work for 500 users simul. without any problem...
please advice...
thanks,
dinakar
--- Alessandro Depase <alessandro.depase@libero.it>
wrote:
> > hi all,
> >
> > i need a clarification in java + postgresql.
> >
> > currently iam using tomcat 4.0, jdk 1.4,
> postgresql
> > 7.3.x.
> >
> > i using the below code to fetch data from
> database,
> >
> > con =
> >
>
DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test",
> "wsas",
> > "wsas");
> >
> > //con.setAutoCommit(false);
> > System.out.println(con.getAutoCommit());
> > preStmt = con.prepareStatement("BEGIN;SELECT
> > fn_list_allpatients('cursor_name');");
> > resultSet = preStmt.executeQuery();
> > String strCn = "cursor_name";
> > preStmt = con.prepareStatement("FETCH ALL IN \"" +
> > strCn + "\";END;");
> > resultSet = preStmt.executeQuery();
> > //con.setAutoCommit(true);
> > while (resultSet.next())
> > {
> > System.out.println(resultSet.getString(1) +
> > resultSet.getString("patient_title"));
> > }
> >
> > if i dont use the setautocommit to false and true
> > respectively the above code is not working,
>
> I think this is not a problem in the behaviour of
> the JDBC driver.
> I don't know the details in the case of PostgreSQL,
> but I'm quite sure that
> its behaviour follows the behaviour of every other
> DBMS I know, i.e., when
> you
> execute a commit, not only updates/deletes/inserts
> are committed, but also
> all the cursor are closed.
> So, you are trying to access a cursor already closed
> with the FETCH
> statement and this is most probably the reason
> because it doesn't work.
>
> Having said this, I think that there are some odd
> things in your code (the
> problem here is that we don't know what your
> fn_list_allpatients function
> shoud do):
> - why are you executing the query again? after the
> first executeQuery you
> should have already everything you need in
> resultSet.
> - you are using 'cursor_name' both as a parameter
> for the function and as
> the name for the cursor. Maybe they are two
> different things, isn't it?
>
> > i need to know will the above code create any
> problem
> > in multiuser application...
>
> Difficult to say: what are you doing in the
> function? [from a Java point of
> view, difficult to say, not knowing the environment
> in which you are running
> the code above and not knowing which is the scope
> and definition of the
> resultSet variable (we can just imagine that this is
> a java.sql.ResultSet,
> but we cannot say, for example, if it is static and
> you are running that
> code inside of a servlet)... however these are not
> issues which are
> pertinent with pgsql-jdbc.]
>
> > currently iam facing a problem that some
> transactions
> > are ideal even after closing the connection to
> > database...
>
> Please, explain better. What do you mean with ideal?
> (sorry, maybe this is
> due to the fact I'm italian and there could be
> meanings I don't know for an
> english word)
>
> Bye
> Alessandro Depase
>
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
Hi Jurka,
let me explain my problem once again..
currently i am using Tomcat 4.x, Postgresql 7.3.x,
Linux OS, JSP, Java for buliding a web application.
i received a mail from my client stating that : they
are facing some problem in postgres database server.
when ever the database grows in size, postgres is
trying to use Vacuum database option to shrink it.
while do so, the vacuum process would be unable to
free up dead rows, because they'd still be accessible
to some idle transactions...
whenever my application throws any exception the
transactions are left unhandled so like this some
transactions are idle even after some 5 to 6 days...
now the problem comes to the server memory. it is
occupying lot of memory..
---previously i was using the below code.....
preStmt = con.prepareStatement("BEGIN; SELECT
fn_list_patientsaudit('" + pstrPatientName + "'," +
intNoRecords + "," + intStart + ",'cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name" ;
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\"; END;");
resultSet = preStmt.executeQuery();
while (resultSet.next()) {
---------
------
}
instead of getting the cursor name from the postgres,
i am giving my own name to the cursor.
--client reports the problem... now i have changed my
code to below...
con.setAutoCommit(false);
preStmt = con.prepareStatement("BEGIN;SELECT
fn_list_allpatients('cursor_name');");
resultSet = preStmt.executeQuery();
String strCn = "cursor_name";
preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\";END;");
resultSet = preStmt.executeQuery();
while (resultSet.next())
{
-----
-----
}
con.setAutoCommit(true);
iam using finally block to close my connection and
connction related objects...
will this work without any problem ?. i mean will this
work for 500 users simul. without any problem...
please advice...
thanks,
dinakar
--- Kris Jurka <books@ejurka.com> wrote:
>
>
> On Mon, 19 Jan 2004, dinakar wrote:
>
> > hi all,
> >
> > i need a clarification in java + postgresql.
> >
> > currently iam using tomcat 4.0, jdk 1.4,
> postgresql
> > 7.3.x.
> >
> > i using the below code to fetch data from
> database,
> >
> > con =
> >
>
DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test","wsas",
> > "wsas");
> >
> > //con.setAutoCommit(false);
> > System.out.println(con.getAutoCommit());
> > preStmt = con.prepareStatement("BEGIN;SELECT
> > fn_list_allpatients('cursor_name');");
> > resultSet = preStmt.executeQuery();
> > String strCn = "cursor_name";
> > preStmt = con.prepareStatement("FETCH ALL IN \"" +
> > strCn + "\";END;");
> > resultSet = preStmt.executeQuery();
> > //con.setAutoCommit(true);
> > while (resultSet.next())
> > {
> > System.out.println(resultSet.getString(1) +
> > resultSet.getString("patient_title"));
> > }
> >
> > if i dont use the setautocommit to false and true
> > respectively the above code is not working,
>
> Writing BEGIN and END in your own code is frowned
> upon. Using
> setAutoCommit and commit should be all you need.
>
> > currently iam facing a problem that some
> transactions
> > are ideal even after closing the connection to
> > database...
>
> You are probably not closing the connection. This
> could be the case of
> just a missing close() or poor exception handling.
> If you post a self
> contained test case someone will likely be able to
> identify your problem.
>
> Kris Jurka
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
Don't forget to actually commit the transaction (connection.commit()).
Also you probably need
to handle either a commit() or a rollback() in the finally block,
depending on how your application
works. Setting autocommit to true doesn't actually issue a commit, I'm
pretty sure.
On Jan 20, 2004, at 6:25 AM, dinakar wrote:
> Hi Jurka,
>
> let me explain my problem once again..
>
> currently i am using Tomcat 4.x, Postgresql 7.3.x,
> Linux OS, JSP, Java for buliding a web application.
>
> i received a mail from my client stating that : they
> are facing some problem in postgres database server.
> when ever the database grows in size, postgres is
> trying to use Vacuum database option to shrink it.
>
> while do so, the vacuum process would be unable to
> free up dead rows, because they'd still be accessible
> to some idle transactions...
>
> whenever my application throws any exception the
> transactions are left unhandled so like this some
> transactions are idle even after some 5 to 6 days...
>
> now the problem comes to the server memory. it is
> occupying lot of memory..
>
> ---previously i was using the below code.....
>
> preStmt = con.prepareStatement("BEGIN; SELECT
> fn_list_patientsaudit('" + pstrPatientName + "'," +
> intNoRecords + "," + intStart + ",'cursor_name');");
>
> resultSet = preStmt.executeQuery();
>
> String strCn = "cursor_name" ;
>
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\"; END;");
>
> resultSet = preStmt.executeQuery();
>
> while (resultSet.next()) {
> ---------
> ------
> }
>
>
> instead of getting the cursor name from the postgres,
> i am giving my own name to the cursor.
>
>
> --client reports the problem... now i have changed my
> code to below...
>
> con.setAutoCommit(false);
> preStmt = con.prepareStatement("BEGIN;SELECT
> fn_list_allpatients('cursor_name');");
>
> resultSet = preStmt.executeQuery();
>
> String strCn = "cursor_name";
>
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\";END;");
>
> resultSet = preStmt.executeQuery();
> while (resultSet.next())
> {
> -----
> -----
> }
>
> con.setAutoCommit(true);
>
>
> iam using finally block to close my connection and
> connction related objects...
>
> will this work without any problem ?. i mean will this
> work for 500 users simul. without any problem...
>
>
> please advice...
>
> thanks,
> dinakar
>
> --- Kris Jurka <books@ejurka.com> wrote:
>>
>>
>> On Mon, 19 Jan 2004, dinakar wrote:
>>
>>> hi all,
>>>
>>> i need a clarification in java + postgresql.
>>>
>>> currently iam using tomcat 4.0, jdk 1.4,
>> postgresql
>>> 7.3.x.
>>>
>>> i using the below code to fetch data from
>> database,
>>>
>>> con =
>>>
>>
> DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/
> wsas_test","wsas",
>>> "wsas");
>>>
>>> //con.setAutoCommit(false);
>>> System.out.println(con.getAutoCommit());
>>> preStmt = con.prepareStatement("BEGIN;SELECT
>>> fn_list_allpatients('cursor_name');");
>>> resultSet = preStmt.executeQuery();
>>> String strCn = "cursor_name";
>>> preStmt = con.prepareStatement("FETCH ALL IN \"" +
>>> strCn + "\";END;");
>>> resultSet = preStmt.executeQuery();
>>> //con.setAutoCommit(true);
>>> while (resultSet.next())
>>> {
>>> System.out.println(resultSet.getString(1) +
>>> resultSet.getString("patient_title"));
>>> }
>>>
>>> if i dont use the setautocommit to false and true
>>> respectively the above code is not working,
>>
>> Writing BEGIN and END in your own code is frowned
>> upon. Using
>> setAutoCommit and commit should be all you need.
>>
>>> currently iam facing a problem that some
>> transactions
>>> are ideal even after closing the connection to
>>> database...
>>
>> You are probably not closing the connection. This
>> could be the case of
>> just a missing close() or poor exception handling.
>> If you post a self
>> contained test case someone will likely be able to
>> identify your problem.
>>
>> Kris Jurka
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
> http://hotjobs.sweepstakes.yahoo.com/signingbonus
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--------------------
Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com
On Tue, 20 Jan 2004, Andrew Rawnsley wrote: > Setting autocommit to true doesn't actually issue a commit, I'm pretty > sure. > Actually the JavaDoc claims "If this method is called during a transaction, the transaction is committed." Our driver in fact only does a commit only if you are changing the autocommit flag. It does nothing if the value is the same. It also has some rather complicated rules on when this commit should happen in the presence of open ResultSets which looks complicated. Kris Jurka
> Hi Depase,
>
> sorry it was due spell check.. the word 'idle' changed
> to 'ideal'..
>
> let me explain my problem once again..
>
> currently i am using Tomcat 4.x, Postgresql 7.3.x,
> Linux OS, JSP, Java for buliding a web application.
>
> i received a mail from my client stating that : they
> are facing some problem in postgres database server.
> when ever the database grows in size, postgres is
> trying to use Vacuum database option to shrink it.
>
> while do so, the vacuum process would be unable to
> free up dead rows, because they'd still be accessible
> to some idle transactions...
>
> whenever my application throws any exception the
> transactions are left unhandled so like this some
> transactions are idle even after some 5 to 6 days...
>
> now the problem comes to the server memory. it is
> occupying lot of memory..
>
> ---previously i was using the below code.....
>
> preStmt = con.prepareStatement("BEGIN; SELECT
> fn_list_patientsaudit('" + pstrPatientName + "'," +
> intNoRecords + "," + intStart + ",'cursor_name');");
>
> resultSet = preStmt.executeQuery();
>
> String strCn = "cursor_name" ;
>
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\"; END;");
>
> resultSet = preStmt.executeQuery();
>
> while (resultSet.next()) {
> ---------
> ------
> }
>
>
> instead of getting the cursor name from the postgres,
> i am giving my own name to the cursor.
>
>
> --client reports the problem... now i have changed my
> code to below...
>
> con.setAutoCommit(false);
> preStmt = con.prepareStatement("BEGIN;SELECT
> fn_list_allpatients('cursor_name');");
>
> resultSet = preStmt.executeQuery();
>
> String strCn = "cursor_name";
>
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\";END;");
>
> resultSet = preStmt.executeQuery();
> while (resultSet.next())
> {
> -----
> -----
> }
>
> con.setAutoCommit(true);
> iam using finally block to close my connection and
> connction related objects...
>
> will this work without any problem ?. i mean will this
> work for 500 users simul. without any problem...
Let's see if I understood it well (I have the problem that I don't know yet
what your function is doing, anyway, so my answer will not be the final
answer you need, and, above all, when you have 500 users simul., there could
be a lot of other problems overlapping with this one...):
- you have a function which makes a lot of things, the last of which is a
select, which you store in a cursor. Should your function only perform a
select, it could be better to prepare and execute it directly, rather than
executing a function (to be evaluated, but you are doing 2 prepare/execute,
which can be reduced to 1 and they are compelling you to turn autocommit
off, with the problems you are telling us). If the function only performs a
select, but you are using it as a wrapper which let you modify the sql code
without recompiling the Java class, please consider that there are several
other ways to reach this (from just putting the select in a property, to
creating a singleton which holds the queries to which ask for your one using
a logical name etc. etc.)
- you are using the double executeQuery as a workaround to get resultSet
from function (maybe it could help see the new feature of the 7.4 release,
which should let you do such a thing in a more standard way).
If the above are true (but there could be other explanations, I suppose: I'm
just trying to understand the not-usual code I see), I think that a finally
clause with a close of all objects can help, but, just to be a little bit
more sure, not knowing the real internals of PostgreSQL (I'm just a designer
as you, involved in other projects, not one of the PostgreSQL team...), I
would add a setAutoCommit(true) in the finally block before closing.
But you can face other problems. For example, you could get an exception
while closing the connection (for example, for a network problem). Will the
connection stay up, in this case, or PostgreSQL can detect the failure?
Maybe on a network problem it can detect the failure (this is just a
supposition, but if is there anyone knowing the answer and reading this, it
would be interesting to know an 'official' answer), but what about other
possible problems?
Well, sometimes, if you use a connection pooler (are you using it, isn't it?
It can really help when you have a lot of 'simultaneous' users - I can't
belive that they are 500 simultaneous accesses to database, i.e. in the same
fraction of second: it could lead to, at least, 18 milion users in 10
working hours... 500 simul. web sessions is much more believable, using the
architecture you told us), it can help, because you are not really closing
the connection, just releasing it and the connection pooler, depending upon
the pooler you are using and your configuration, can try to close (rollback,
usually) any open transaction and, upon error, to invalidate the connection.
Sometimes the connection pool performs some checks also before giving you
the connection, so if there is a temporary problem, it can resolve it. There
could be a lot of other scenarios, so it is very difficult to answer your
simply question ('will this work without any problem?').
But all of these (all my answer) are design consideration, not related with
the working of the jdbc driver and, so, with this mailing list, I suppose.
However I hope my answer can help you...
Bye
Alessandro Depase
> please advice...
>
> thanks,
> dinakar