Skip site navigation (1) Skip section navigation (2)

Re: AW: [HACKERS] Begin statement again

From: dg(at)illustra(dot)com (David Gould)
To: vadim(at)sable(dot)krasnoyarsk(dot)su (Vadim B(dot) Mikheev)
Cc: jwieck(at)debis(dot)com, andreas(dot)zeugswetter(at)telecom(dot)at, meskes(at)topsystem(dot)de, pgsql-hackers(at)hub(dot)org
Subject: Re: AW: [HACKERS] Begin statement again
Date: 1998-03-25 06:16:16
Message-ID: 9803250616.AA20162@hawk.illustra.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)illustra(dot)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.


In response to

pgsql-hackers by date

Next:From: Thomas G. LockhartDate: 1998-03-25 07:41:24
Subject: Re: [HACKERS] Data type removal
Previous:From: t-ishiiDate: 1998-03-25 06:11:39
Subject: using alternative tcl include dir?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group