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

	As to transaction blocks: 
	Actually most clients never use commit work, and remember
	that postgresql will never free it's memory before commit. This will
hog up 
	memory for otherwise lightweight clients.

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

	Andreas


Mon, 28 Dec 1998 10:30:01 +0100
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
Re: [HACKERS] MVCC works in serialized mode!

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Zeugswetter
> Andreas IZ5
> Sent: Monday, December 28, 1998 6:30 PM
> To: hackers@postgreSQL.org
> Cc: 'vadim@krs.ru'
> Subject: Re: [HACKERS] MVCC works in serialized mode!
> 
> 
> 	>>    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. 
>

I have a question about new default(isolation level / transaction mode).

How do we upgrade existent programs,if default is different 
from current ?
I think the isolation level of current PostgreSQL is SERIALIZABLE 
and the transaction mode is un-chained..

As to the isolation level,even SERIALIZABLE isolaton level can't guarantee 
the integrity of current level.
So we must change existent programs anyway ?

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp
 
> 	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.
> 
> 	As to transaction blocks: 
> 	Actually most clients never use commit work, and remember
> 	that postgresql will never free it's memory before commit. This will
> hog up 
> 	memory for otherwise lightweight clients.
> 
> 	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).
> 	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.
> 	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).
> 	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.
> 
> 	Andreas
> 
> 
> 
> 
> 

Wed, 6 Jan 1999 09:30:01 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] MVCC works in serialized mode!
Hiroshi Inoue wrote:
> 
> I have a question about new default(isolation level / transaction mode).
> 
> How do we upgrade existent programs,if default is different
> from current ?
> I think the isolation level of current PostgreSQL is SERIALIZABLE
> and the transaction mode is un-chained..
> 
> As to the isolation level,even SERIALIZABLE isolaton level can't guarantee
> the integrity of current level.
> So we must change existent programs anyway ?

You're right!
The word from Oracle: (-:))

"Because Oracle does not use read locks, even in serializable 
transactions, data read by one transaction can be overwritten 
by another. Transactions that perform database consistency
checks at the application level should not assume that the data 
they read will not change during the execution of the transaction 
(even though such changes are not visible to the transaction). 
Database inconsistencies can result unless such application-level 
consistency checks are coded with this in mind, even when using 
serializable transactions.
...
Although Oracle serializable mode is compatible with SQL92 and 
offers many benefits as compared with read-locking implementations, 
it does not provide semantics identical to such systems. 
Application designers must take into account the fact that reads 
in Oracle do not block writes as they do in other systems. 
Transactions that check for database consistency at the application 
level may require coding techniques such as the use of
SELECT FOR UPDATE. This issue should be considered when 
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
applications using serializable mode are ported to Oracle 
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
from other environments."
^^^^^^^^^^^^^^^^^^^^^^^

1. All our applications run in serializable mode now.
2. "Environment" is changed from locking to
   multi-versioning.

This has to be explained in release notes.

Should we implement ability to run backend in mode compatible
with old versions (it seems easy to do - just use AccessExclusive 
Lock for UPDATE/INSERT/DELETE in Executor and don't release
AccessShare Lock in heap_endscan) ?

Vadim

Sun, 10 Jan 1999 23:26:27 +0700
Vadim Mikheev <vadim@krs.ru>
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

Mon, 11 Jan 1999 00:05:08 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] MVCC works in serialized mode!
Hello all,

> -----Original Message-----
> From: root@dune.krs.ru [mailto:root@dune.krs.ru]On Behalf Of Vadim
> Mikheev
> Sent: Monday, January 11, 1999 1:26 AM
> To: Hiroshi Inoue
> Cc: hackers@postgreSQL.org
> Subject: Re: [HACKERS] MVCC works in serialized mode!
>
>
> Hiroshi Inoue wrote:
> >
> > I have a question about new default(isolation level / transaction mode).
> >
> > How do we upgrade existent programs,if default is different
> > from current ?
> > I think the isolation level of current PostgreSQL is SERIALIZABLE
> > and the transaction mode is un-chained..
> >
[snip]
>
> 1. All our applications run in serializable mode now.
> 2. "Environment" is changed from locking to
>    multi-versioning.
>
> This has to be explained in release notes.
>
> Should we implement ability to run backend in mode compatible
> with old versions (it seems easy to do - just use AccessExclusive
> Lock for UPDATE/INSERT/DELETE in Executor and don't release
> AccessShare Lock in heap_endscan) ?
>

Yes,if it's easy as you say.
But I don't know whether other people mind it or not.
If no one mind it,SERIALIZABLE is permissible for me.

BTW before user's code of our own,there are no problems with
interface library such as ODBC/JDBC etc ?
Those work well with chained mode or read committed isolation
level ?

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp


Tue, 12 Jan 1999 09:40:22 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] MVCC works in serialized mode!