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

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).