Re: problem with new autocommit config parameter and jdbc - Mailing list pgsql-hackers

On Wednesday 11 September 2002 06:11 pm, Stephan Szabo wrote:
> On Wed, 11 Sep 2002, snpe wrote:
> > On Wednesday 11 September 2002 02:55 pm, Stephan Szabo wrote:
> > > On Wed, 11 Sep 2002, snpe wrote:
> > > > If decision (transaction or not) is after parser (before execute)
> > > > this isn't problem.
> > > > I don't know when postgresql make decision, but that is best after
> > > > parser. I parser find error simple return error and nothing happen
> > >
> > > Are you saying that it's okay for:
> > > insert into nonexistant values (3);
> > > and
> > > insert into existant values (3);
> > > where 3 is invalid for existant to work
> > > differently?
> > > I think that'd be tough to get past some people, but you might
> > > want to write a proposal for why it should act that way. (Don't
> > > expect anything for 7.3, but 7.4's devel will start sometime.)
> >
> > I don't understand all, but when I tell 'error' I think "syntax error"
> > If error is contraint error again nothin change, only error return
>
> I don't understand what you mean here. Are you saying that both of
> those queries should not start transactions? Then that wouldn't
> be starting between the parser and execute since you won't know that
> the row violates a constraint until execution time.
>
> > > > > I disagree because I think that two serializable select statements
> > > > > in autocommit=off (without a commit or rollback of course) should
> > > > > see the same snapshot.
> > > >
> > > > Question ?
> > > > All select in one transaction return same data - no matter if any
> > > > change and commit data ?
> > >
> > > It depends on the isolation level of the transaction I believe.
> > > This sequence in read committed (in postgresql) and serializable give
> > > different results.
> > >
> > > T1: begin;
> > > T1: select * from a;
> > > T2: begin;
> > > T2: insert into a values (3);
> > > T2: commit;
> > > T1: select * from a;
> > >
> > > In serializable mode, you can't get "non-repeatable read" effects:
> > > SQL-transaction T1 reads a row.  SQL-transaction T2 then modifies
> > > or deletes that row and performs a COMMIT. If T1 then attempts to
> > > reread the row, it may receive the modified value of discover that the
> > > row has been deleted.
> >
> > If serialization strict connect with transaction then ok.
>
> 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)

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.
This is same with autocommit=false
It is maybe ok, I don't know.
For rest is ok (if level serializable select start transaction)

Thanks