Re: streaming result sets: progress - Mailing list pgsql-jdbc

On Friday 15 November 2002 10:30 am, nferrier@tapsellferrier.co.uk wrote:
> Thanks for testing all this Haris.
>
You velcome
> snpe <snpe@snpe.co.yu> writes:
> > I work with patch and I have any problem yet
> >
> > 1) ResultSet.setFetchSize, Statement.getFetchSize and
> > ResultSet.getFetchSize don't work
>
> I've updated my patch to cover these methods. Note that
> ResultSet.getFetchSize() should have worked for you. I haven't altered
> it, right now the method returns the number of rows in the current
> "batch" which seems like the right thing to do.
>
> > 2) any methods in DataBaseMetaData don't work
> > example, getBestRowIdentifier
> > getBestRowIdentifier create ResultSet without execSQL (direct) and
> > when we call ResultSet.next, after last row driverreturn error
> > (driver send command 'FETCH FORWARD 0 FROM ;'
>
> Ok... looks like the initing of the ResultSet isn't quite right
> there. I'll take a look at that tonight.
>
> > 3) if I have two or more query pgsql return warning 'BEGIN : already a
> > transaction in progress'
>
>  the next one.
>
> > 4) This is greatest problem :
> > If I create query and then want transaction without close query, I break
> > query. example :
> >
> >   a) create statement for 'select ..' and fetch any rows
> >
> >   b) do any update,delete or update and commit or rollback
> >
> > query is lost
> > I think that problem 4) can be make only with cursor outre of
> > transaction.
>
> The problem is that my code needs to wrap the query in a transaction
> so that the cursor can be used.
>
> But when that's done other statements fail...
>
> How do people handle this with their "manual" cursor based hacks? Is
> the best thing to wrap _everything_ in an independant transaction, eg:
> including inserts and updates? and do commits automatically but via
> the java code. This will lower performance of course.
>
> It's a tricky one.
>
I don't know. commit will always break open queries - we haven't savepoint
I have yet another example :
Simpe master detail screen (forms) in JAVA
I do select in master and go on detail - I make update on detail and commit
If I will return to master and continue query I must select again
Solution is cursor out of transaction - it is in TODO lists for postgreSQL,
but I don't know is it planed for 7.4
Another solution : I call old way query and save in memory only fetchSize rows
When I call ResultSet.next out of fetchSize I call new query and save next
fetchSize rows etc - this will lower performance, but if we set fetchSize big
almost queries will make only first step and this is normal situation, but big
qyery will work (slower, but work) - true solution is  cursor out of
transaction.
Regards
Haris Peco