Transaction scope??? - Mailing list pgsql-jdbc

Hi..

Recently I had an issue about browsing large resultsets, and I sent a
e-mail to this list subject: "Server side resultset search (for
performance reasons)"

The solution was to use cursors and fetch first 1000 tuples and when the
user seek above the last fetched tuple the logic makes a reposition of
the cursor and fetches 1000 tuples again.

This way I keep the java VM memory comsumption low, and the user
interface response time is ok, so far.

But.....

Mistakenly I was tinking that the transaction scope was for the current
Statement object, recently I found that the transaction scope is about
the Connection Object (tell me if this is no correct).

I can't find an easy (or not too brute force) way to do the following:

1) A user opens the application, this is one Postgresql connection per user.
2) The user opens a Windows related to edit 1 table (customers_table for
example) the logic inside this window is to create a Statement object
for this window and to make a CURSOR for this window, I mean OPEN a
TRANSACTION (in order to use cursors).
3) The user opens another window related to edit other table
(orders_table for example) so again, This window has a new Statement
Object and ResultSet Object (just like the previous window), but they
share the same Connection Object.

So far everything is ok, but..

The user wants to insert a new order in the second open window
(orders_table) and the application COMMIT The current transaction, in
order to perform the INSERT (I was assumming that the COMMIT only afects
the current Statement Object, because after this COMMIT, I OPEN the
transaction again re-issuing the query, but now the resultset has all
the previous tuples plus the one inserted).

Today I discover that after the insertion in the second window the
cursor in the first open window is invalid, and I cant get the table
rows from that ResultSet Object, so I'm assuming that the transaction is
related with the Connection Object, so I have to change the design.

This is sad, but if I have to go-back and start againg I'll do it, but I
don't know if there is a way to do it in Postgresql, if I'm missing some
feature that I can use for it.

What I'm actually thinking is (but I don't agree completely):

1) If each window has a new Connection Object the problem is solved (I
think), but I think that connections are a expensive resource for
Postgresql (I assume this because in the postgresql.conf there is by
default 32 connections Max, so if I use maybe 10 connection per seat
(user) with 50 users I'll need 500 connections, I don't know if this is
bad for performance or the server, I mean maybe this is not a smart way
to use the system resources).

2) Better but more difficult is that each user (seat) has 2 connection
objects, one for INSERT, UPDATE, DELETE and other for BROWSING.
The one with the browsing always will have open transaction. (In this
design, I don't know how to react when the user sends an invalid comand
to this Connection Object, by means of the Statement Object, because
when detects an invalid command inside a transaction, I cannot issue new
CURSOR creation, until I do a ROLLBACK or COMMIT and open the
transaction again). Maybe I'll need one Connection Object by window.

Please give some minutes to this post and send a reply....

Thanks everybody....

Jesus Sandoval