WG: [HACKERS] MVCC works in serialized mode!

Vadim wrote:

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.

Good :-)

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.

Yes, but that is not really what we would want a client to do.
(Not give a commit for 5 h)

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

Yes, if you do a commit the memory will be freed alright. I meant a client,
that does
only selects, and therefore never does a commit. In chained mode the memory
allocated for the selects would not be freed until the disconnect.

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.

Sorry, I am not so good with my wording, I meant to say transaction block
not statement block. I have been crying to take the begin/end syntax out of
the PostgreSQL SQL syntax for very long now. The SQL statements should be:
begin work;
commit work;
not begin and end which is unfortunately still allowed.

Second, Informix is using locking => chained transaction
mode is way to lock-escalation.

I don't understand ? Informix has no lock escalation (the term meaning the
promotion of a row to a page and then Table lock, if soandso many locks for
a table are already held) and does not use chained mode.

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.

Not in DB/2.

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 was referring to the other DB's.

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.

Sounds great !

Andreas