Thread: TX semantics backward compatibility

Hi,

We've got a product which supposedly supports the major RDBMSs including
PostgreSQL. We tested it so far only with PostgreSQL 7.2 and found no
problem. Now, it turned out that it does not work with more recent
versions (certainly not with 8.0). The problem is with the kind of code
where we test the existence of a table in a transaction with:

select count(*) from doesitexist where 1 = 2;

If there is an error, we assume that the table does not exists and go
ahead without rollback and commit. When later we issue an SQL statement
again, we get the error message you probably all know.

Of course, we rely on we-do-not-exactly-now-how-many places on a
transaction still being usable after a (read-only) statement level error.

Is it correct that the behaviour in this regard was different with
earlier versions (like with 7.2)? (I.e. a statement level error left you
with a still usable transaction?) Or have we introduced some subtle
change into our code (or our testing environment) which makes the
difference? Or may something have changed in the client driver we use
(JDBC)? Is there a simple way to revert to the old behaviour in this regard?

Many thanks in advance,

Peter

=?ISO-8859-1?Q?Kov=E1cs_P=E9ter?= <peter.kovacs@chemaxon.hu> writes:
> The problem is with the kind of code
> where we test the existence of a table in a transaction with:

> select count(*) from doesitexist where 1 = 2;

AFAIK the behavior of that has not changed since forever: if doesitexist
doesn't exist you'll get an error, and unless you take steps like
establishing a savepoint then the error aborts your transaction.
If you think the behavior has changed you will have to give a complete
example.

> Of course, we rely on we-do-not-exactly-now-how-many places on a
> transaction still being usable after a (read-only) statement level error.

PG has *never* behaved that way.

> Or may something have changed in the client driver we use (JDBC)?

Certainly many things have changed in both the server and JDBC since
7.2.  You might try the combinations of older-JDBC-newer-server and
older-server-newer-JDBC to see if you can narrow down which component
is causing you problems.  If it seems to be JDBC, the pgsql-jdbc list
is a better place to ask about it.

            regards, tom lane

On Thu, 2005-06-23 at 12:12, Tom Lane wrote:
> =?ISO-8859-1?Q?Kov=E1cs_P=E9ter?= <peter.kovacs@chemaxon.hu> writes:
> > The problem is with the kind of code
> > where we test the existence of a table in a transaction with:
>
> > select count(*) from doesitexist where 1 = 2;
>
> AFAIK the behavior of that has not changed since forever: if doesitexist
> doesn't exist you'll get an error, and unless you take steps like
> establishing a savepoint then the error aborts your transaction.
> If you think the behavior has changed you will have to give a complete
> example.
>
> > Of course, we rely on we-do-not-exactly-now-how-many places on a
> > transaction still being usable after a (read-only) statement level error.
>
> PG has *never* behaved that way.
>
> > Or may something have changed in the client driver we use (JDBC)?
>
> Certainly many things have changed in both the server and JDBC since
> 7.2.  You might try the combinations of older-JDBC-newer-server and
> older-server-newer-JDBC to see if you can narrow down which component
> is causing you problems.  If it seems to be JDBC, the pgsql-jdbc list
> is a better place to ask about it.

What versions of postgresql supported the abortive non-autocommit mode
that caused so many problems, could it be that 7.2 did, and it was on
for this fellow?

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> What versions of postgresql supported the abortive non-autocommit mode
> that caused so many problems, could it be that 7.2 did, and it was on
> for this fellow?

If memory serves, that mistake was only in 7.3.  In any case, it would
not have had the effect of auto-recovering from errors.

The only explanation that I can think of for his report is that the 7.2
JDBC driver was automatically canceling the transaction and starting a
new one after detecting an error ... which is bizarre enough to be hard
to believe, but I'm far from an authority on JDBC.

            regards, tom lane