Re: JDBC Large ResultSet problem + BadTimeStamp Patch

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: Steve Wampler <swampler(at)noao(dot)edu>
Cc: Peter Mount <peter(at)retep(dot)org(dot)uk>, "pgsql-interfaces(at)postgreSQL(dot)org" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: JDBC Large ResultSet problem + BadTimeStamp Patch
Date: 2000-10-11 18:56:41
Message-ID: 39E4B7E9.FB8605F2@selectacast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-interfaces

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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2000-10-11 21:25:39 Re: postmaster errors with index on temp table?
Previous Message Bruce Momjian 2000-10-11 18:40:47 Re: TIOGA

Browse pgsql-interfaces by date

  From Date Subject
Next Message keke abe 2000-10-12 07:28:23 COPY BINARY to stdout
Previous Message Steve Wampler 2000-10-11 18:27:40 Re: JDBC Large ResultSet problem + BadTimeStamp Patch