Re: Connections/Statements/ResultSets (Was: Re: Pooling

Postgres doesn't support nested transactions

test=# begin;
LOG:  query: begin;
BEGIN
test=# begin;
LOG:  query: begin;
WARNING:  BEGIN: already a transaction in progress
BEGIN
test=#


On Fri, 2002-08-30 at 06:37, Michael Paesold wrote:
> Dave Cramer wrote:
>
> > The spec probably doesn't say anything but; with the way pg's driver is
> > currently written you can have as many resultsets/statements open as you
> > want. This is because the driver will read them all in when you do an
> > executeXXX. There is a plan however to move to a cursor backed result
> > set which will change the above statement. This is because a cursor must
> > be executed within a transaction, and you can only have one transaction
> > open per resultset. I suppose it's possible to still open a non-cursor
> > based resultset while the transaction is open?
> >
> > Dave
>
> Did you really want to say
> > ... you can only have one transaction open per resultset
> ?
> Shouldn't it be per connection?
> If so, there would be several scenarios, if I am correct.
>
> 1. connection.getAutoCommit is false
>
> Since it's possible to have several cursors within one transaction, one
> could also have more than one cursor based result set per connection.
> I don't know if this is a good idea, because if
>
> 2. connection.getAutoCommit is true
>
> then everything looks differnt. You would have to open a transaction for
> a cursor based result set. What happens to the auto commit state of the
> connection? Calling setAutoCommit while having a result set open would
> break the result set, since:
> "NOTE: If this method is called during a transaction, the transaction is
> committed."
> (J2SE 1.4 API on java.sql.Connection)
yes, look at the code in the driver to see what it does when you call
setAutoCommit(false); It actually issues a begin and end for every query

>
> > I suppose it's possible to still open a non-cursor
> > based resultset while the transaction is open?
>
> That would be nice! At least the non-cursor based resultset should not
> be removed from the driver. Have a look at createStatement
> (J2SE 1.4 API, don't have any older).
Yes, the way it will work is that if you call setFetchSize() then it
will use cursors, otherwise no
>
> This is the most specific prototype of createStatement:
> createStatement(int resultSetType, int resultSetConcurrency, int
> resultSetHoldability)
>
> I suppost the decision about cursor/non-cursor result set should be made
> based on these attributes (resultSetConcurrency, resultSetHoldability).
>
> Correct me, if I'm wrong.
>
> Best Regards,
> Michael Paesold
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>