Re: [HACKERS] Begin statement again

David Gould wrote:
>Andreas wrote:
>>
>> I think we should depreciate the BEGIN/END keywords in SQL to allow them
I am only talking about the syntax here.
>> to be used for the new PL/SQL. So definitely leave them out of ecpg now.
>> Only accept BEGIN WORK and BEGIN TRANSACTION. (do a sequence of commit work; begin work)
>> BTW.: why is a transaction always open ? A lot of programs would never need a
I meant: why is a transaction always open in an ecpg program
>> transaction. Is it because of cursors ?
>
>Because without transactions it is darn near impossible to build a database
>that can guarantee data consistancy. Transactions are _the_ tool used to
>build robust systems that remain usable even after failures.

I shoud probably have said: A lot of programs would never need a transaction
that span more than one statement.

>For example take the simple single statment:
>
>insert into customers values("my name", customer_number("my name"));
>
>Assuming that there is an index on the name and id # columns, what happens
>if the system dies after the name index is updated, but the id # index
>is not? Your indexes are corrupt. With transactions, the whole thing just
>rolls back and remains consistant.
>
>Since PostgreSQL is more powerful than many databases, it is just about
>impossible for a client application to tell what is really happening and
>whether a transaction is needed even if the client only is using very
>simple SQL that looks like it doesn't need a transaction.
>
>Take the SQL statement above and add a trigger or rule on the customers
>table like so:
>
>create rule new_cust on insert to customers do after
> insert into daily_log values ("new customer", new.name);
> update statistics set total_customers = total_customers + 1 ...
>
>Now you really need a transaction.
>
>Oh, but lets look at the customer_number() function:
>
>begin
> return (select unique max(cust_no) + 1 from customers);
>end
>
>This needs to lock the whole table and cannot release those locks until
>the insert to customer is done. This too must be part of the transaction.
>
>Fortunately, unlike say 'mySQL', posgreSQL does the right thing and always
>has a transaction wrapped around any statement.

Yes, but this is handeled implicitly by the backend even if the user does not say
begin work;
blabla
commit work;
In that sense every statement is atomic.

In a client server environment the implicit begin work; commit work; can save
a lot of time since it saves 2 network roundtrips.
And of course it would be bad practice if the user is forced to do commit work;
and then for ease of programming and execution speed only does this every 100 statements.

What I am saying here is, that an ecpg program should be able to run with
autocommit mode on. (Michael Meskes)

Andreas