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 03:14 am, Stephan Szabo wrote:
> > On Wed, 11 Sep 2002, snpe wrote:
> > > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote:
> > > > On Wed, 11 Sep 2002, snpe wrote:
> > > > > yes, we're going around in circles.
> > > > >
> > > > > Ok.I agreed (I think because Oracle do different)
> > > > > Transaction start
> > > > > I type invalid command
> > > > > I correct command
> > > > > I get error
> > > > >
> > > > > Why.If is it transactin, why I get error
> > > > > I want continue.
> > > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL
> > > > > Server)
> > > >
> > > > Right, that's a separate issue (I alluded to it earlier, but wasn't
> > > > sure that's what you were interested in).  PostgreSQL treats all errors
> > > > as unrecoverable.  It may be a little loose about immediately rolling
> > > > back due to the fact that historically autocommit was on and it seemed
> > > > better to not go into autocommit mode after the error.
> > > >
> > > > I doubt that 7.3 is going to change that behavior, but a case might be
> > > > made that when autocommit is off the error immediately causes a
> > > > rollback and new transaction will start upon the next statement (that
> > > > would normally start a transaction).
> > >
> > > Why rollback.This is error (typing error).Nothing happen.
> >
> > Postgresql currently has no real notion of a recoverable error.
> > In the case of the error you had, probably nothing bad would happen
> > if it continued, but what if that was a unique constraint violation?
> > Continuing would currently probably let you see the table in an
> > invalid state.
> >
> 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 think that we need clear set : what is start transaction ?
> > > I think that transaction start with change data in database
> > > (what don't change data this start not transaction.
> > > Oracle dot this and I think that is correct))
> >
> > 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.