Re: [HACKERS] MVCC works in serialized mode! - Mailing list pgsql-hackers

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