Message-ID: <39E4B7E9.FB8605F2@selectacast.net>
Date: Wed, 11 Oct 2000 14:56:41 -0400
From: Joseph Shraibman <jks@selectacast.net>
Organization: Xtenit
MIME-Version: 1.0
To: Steve Wampler <swampler@noao.edu>
CC: Peter Mount <peter@retep.org.uk>,
	"pgsql-interfaces@postgreSQL.org" <pgsql-interfaces@postgresql.org>
Subject: Re: JDBC Large ResultSet problem + BadTimeStamp Patch
References: <Pine.LNX.4.21.0010111901340.8388-100000@maidast.demon.co.uk>
	<39E4B11C.204A69A3@noao.edu>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Steve Wampler wrote:
> 
> Peter Mount wrote:
> ...
> > Yes, there is a problem with multiple statements and transactions on the
> > same connection when it comes to thread safety.
> >
> > The problem as I see it is two fold.
> >
> > 1: How to deal with two statements within one transaction. Related to this
> > is the metadata methods that issue queries, how to deal with them while
> > within a transaction.
> >
> > 2: Currently the JDBC Specs only have transactions supported at the
> > Connection level, so I can't see how they thought that Statements could
> > possibly run within their own transactions, unless they thought that a
> > workaround of this is the use of batches.
> 
> Ah, that probably explains why I've seen "tuple arrived before metadata"
> messages when I've got several apps talking through CORBA to a java app
> that connects to postgres.  Do I need to synchronize both inserts and
> queries at the java app level to prevent this?  (I was hoping that
> the BEGIN/END block in a transaction would be sufficient, but this makes
> it sound as though it isn't.)
> 
It isn't.  I wish there were online mail archives.  But anyway the
reason it isn't is that if two statements use the same connection, when
one of them calls enters a transaction the other one does too.  So if
you do:
1) BEGIN;
2) SELECT blah FROM tablea FOR UPDATE;
3) UPDATE tablea SET blah = newblah;

since both statements are using the same connection they ARE NOT LOCKED
FROM EACH OTHER, and when one calls and END; or COMIT; both of them exit
their transactions.  That is why I'm using a connection pool now when I
have to do locking.  And if Peter wants to use cursors behind the scenes
it will be even worse, because the cursors themselves need to be in a
BEGIN; END;, and what happens if they user thinks he is in a transaction
but the cursor ended it for him a while ago?  Named transactions would
help with this, but the real answer would be to be able to have more
then one connection to a backend (maybe tunnelled over on TCP/IP link).
Right now each new connection requires forking off another backend,
which makes it impractical to connect whenever you have a new
transaction to do.