Re: problem with new autocommit config parameter and jdbc

On Wed, 11 Sep 2002, snpe wrote:

> On Wednesday 11 September 2002 06:11 pm, Stephan Szabo wrote:
>
> > I again am not sure I understand, are you saying that under serializable
> > select should start a transaction but it shouldn't under read committed?
> > That seems like a bad idea to me, either it should or it shouldn't in
> > my opinion.
> >
> > Perhaps it'd be better if you wrote up what you think it should do in
> > all these cases and then we could look at them as a whole.
> > (Cases I can see right now are, select under serializable, select under
> > read committed, garbage command, select to non existant table,
> > insert to non existant table, insert that fails due to unique constraint,
> > insert that fails due to exception raised by a before trigger,
> > insert that fails due to exception raised by an after trigger,
> > insert that does nothing due to before trigger, update that fails
> > due to any of those after some rows have already successfully been
> > modified and probably some others).
>
> One question first ?
>
> What mean ?
> ERROR: current transaction is aborted, queries ignored until end of
> transaction block
> I am tried next (autocommit=true in postgresql.conf)

The transaction has encountered an unrecoverable error (remember, all
errors are currently considered unrecoverable) and the transaction
is in a potentially unsafe state.

> 1. begin;
> 2. select * from tab;
> query work
> 3. show t; -- force stupid syntax error
> 4. select * from tab;
> ERROR: current transaction is aborted, queries ignored until end of
> transaction block
> 5.end;
> 6. select * from tab;
> query work
>
> I must rollback or commit transaction when I make stupid syntax error.

Note that even with end you get effectively a rollback in this case
since the transaction as a whole ended in an error state.

> This is same with autocommit=false
> It is maybe ok, I don't know.

Well, at least until we have savepoints or nested transactions,
there's only a limited amount of freedom in the implementation.

> For rest is ok (if level serializable select start transaction)

Like I said above, having the transaction starting of select being
dependent on the isolation level variable sounds like a bad idea.
In addition that still doesn't deal with select statements with side
effects.