From: Joseph Shraibman <jks@selectacast.net>
To: Steve Wampler <swampler@noao.edu>
Cc: Peter Mount <peter@retep.org.uk>
Cc: pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgresql.org>
Subject: Re: JDBC Large ResultSet problem + BadTimeStamp Patch
Date: Wed, 11 Oct 2000 14:56:41 -0400
Message-ID: <39E4B7E9.FB8605F2@selectacast.net> (raw)
References: <Pine.LNX.4.21.0010111901340.8388-100000@maidast.demon.co.uk>
<39E4B11C.204A69A3@noao.edu>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jks@selectacast.net, swampler@noao.edu, peter@retep.org.uk
Subject: Re: JDBC Large ResultSet problem + BadTimeStamp Patch
In-Reply-To: <39E4B7E9.FB8605F2@selectacast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox