Re: [HACKERS] MVCC works in serialized mode!

Zeugswetter Andreas IZ5 wrote:
>
> >> Shouldn't we change default transaction mode now?
> >> And use option/SET TRANSACTION MODE to switch to
> >> un-chained mode if one like it?
> >
> >No comments on this?
> >I would like to make BEGIN implicit...
> >Objections?
>
> Yes, I object. I think the default behavior should be the one that
> is sufficient in most cases.
>
> As to the isolation level:
> Informix and DB/2 default to read committed mode. In most cases this
> is
> sufficient.
> Therefore most clients don't need the extra set transaction
> isolation statement.

And this is the same as in Oracle & SyBase.
I don't object - currently READ COMMITTED is not
supported by DELETE/UPDATE code and so default is
SERIALIZABLE, to be changed latter.

>
> As to transaction blocks:
> Actually most clients never use commit work, and remember

In Oracle, COMMIT is implicit if DB connection is closed
in "normal" way - by using PQfinish in our case.

> that postgresql will never free it's memory before commit. This will
> hog up
> memory for otherwise lightweight clients.

This is the bug, but I believe that it's fixed for
most cases ~1year ago by this

#ifdef TBL_FREE_CMD_MEMORY
EndPortalAllocMode();
StartPortalAllocMode(DefaultAllocMode, 0);
#endif

in xact.c

> I also think that the begin work; commit work; statement block is
> somewhat part of the postgresql philosophy. We do not stand alone
> with this approach (Informix).

First, BEGIN/END is used in standard for grouping queries
(in PL etc), not for transaction control, and so currently
using of BEGIN/END is ambiguous.
Second, Informix is using locking => chained transaction
mode is way to lock-escalation.

> Actually the other DB's start the transaction with the first
> modifying
> statement, in my opinion a rather weird approach.
> I have seen programs that do a dummy update, just to simulate a
> begin work, what a waste.

I believe that this is not how Oracle works.
I assumed that any DML statement will start transaction.

> But this alone will not work, because a select statement, that needs
>
> a sort would have started the transaction earlier (Since it created
> an implicit
> temp table).
^^^^^^^^^^
This is changed - backend don't create temp table now.

> I could go on, but you see I don't really like this ......
>
> If you want to change the behavior, I think we will need a
> changeable default
> at database level.

Ok, for compatibility reasons, I agreed that default
must be un-chained mode, with ability to switch in
compile/run time.

Vadim