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

Mon, 11 Jan 1999 10:58:46 +0100
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
WG: [HACKERS] MVCC works in serialized mode!
Zeugswetter Andreas IZ5 wrote:
> 
> >>
> >>         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)

I would say - it's up to client -:)
And remember - this is standard behaviour and so this mode
should be implemented in any 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
> 
> Yes, if you do a commit the memory will be freed alright. I meant a client,
              ^^^^^^^^^^^
No! This part of code run after _each_ statement...

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

I'm not sure but it seems that COMMIT without WORK is allowed
by standard (at least, in some levels).

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

I'm sure that Informix has nice lock manager, 
but in SERIALIZABLE+chained mode Informix will hold lock on selected 
rows untill COMMIT/ABORT: this increases possibility of
lock escalation and remember that read/write concurrent access 
to the same row causes blocking (not give a commit for 5h
after simple select... -:))

Un-chained mode seems more suitable for locking systems.
BTW, is there ability to switch to chained mode in Informix?
SyBase has it.

> >>         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 in standard. And I read that DDL statements also start
transaction (if there is no active one).
Ability to run DDL & DML statements in the same
transaction is implementation dependent. 

Vadim

Tue, 12 Jan 1999 16:51:48 +0700
Vadim Mikheev <vadim@krs.ru>
Re: WG: [HACKERS] MVCC works in serialized mode!