Re: AW: [HACKERS] Begin statement again

Andreas wrote:
>
> I think we should depreciate the BEGIN/END keywords in SQL to allow them
> 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
> 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.

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.

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - Linux. Not because it is free. Because it is better.