Re: clarification needed in postgresql... + transactions...

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(dot)depase(at)libero(dot)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