Thread

1. AW: [HACKERS] Begin statement again

   Andreas Zeugswetter <andreas.zeugswetter@telecom.at> — 1998-03-13T11:50:27Z

   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 ?
   
   Andreas
   
   
   Michael Meskes wrote:
   Forget about my last question. I found the begin call in ecpglib.c. It doesn
   what you expect from a embedded SQL preprocessor, it starts a new
   transaction as soon as one ends. Nevertheless I thought about accepting
   explicit begin calls in the new version. But they will always generate a
   warning message as the code's always inside a transaction. So I could as
   well accept the begin call but not give it to the backend.
   
   
   
   
   
   

2. Re: AW: [HACKERS] Begin statement again

   Jan Wieck <jwieck@debis.com> — 1998-03-13T12:43:29Z

   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 ?
   
       BEGIN/END  in  PL/SQL and PL/pgSQL doesn't mean transactions!
       It's just to group statements to a block. You  cannot  commit
       something inside a PostgreSQL function. All changes made by a
       function are covered by the  statements  transaction  or  the
       upper transaction block.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #
   
   
   
   

3. Re: AW: [HACKERS] Begin statement again

   Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-03-25T03:43:44Z

   Jan Wieck wrote:
   > 
   > 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 ?
   > 
   >     BEGIN/END  in  PL/SQL and PL/pgSQL doesn't mean transactions!
   >     It's just to group statements to a block. You  cannot  commit
   >     something inside a PostgreSQL function. All changes made by a
   >     function are covered by the  statements  transaction  or  the
   >     upper transaction block.
   
   This will be changed - there is a way to implement nested transaction!
   And so, some day we will need in something to start/end transaction
   block inside functions.
   
   Vadim
   
   

4. Re: AW: [HACKERS] Begin statement again

   David Gould <dg@illustra.com> — 1998-03-25T06:16:16Z

   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.
   
   
   

5. Re: AW: [HACKERS] Begin statement again

   Jan Wieck <jwieck@debis.com> — 1998-03-25T09:36:36Z

   Vadim wrote:
   >
   > Jan Wieck wrote:
   > >
   > > 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 ?
   > >
   > >     BEGIN/END  in  PL/SQL and PL/pgSQL doesn't mean transactions!
   > >     It's just to group statements to a block. You  cannot  commit
   > >     something inside a PostgreSQL function. All changes made by a
   > >     function are covered by the  statements  transaction  or  the
   > >     upper transaction block.
   >
   > This will be changed - there is a way to implement nested transaction!
   > And so, some day we will need in something to start/end transaction
   > block inside functions.
   
       What exactly is planned for this?
   
       Will  it be possible to begin/commit a subtransaction so that
       updates done before and after it could still get rolled  back
       while things inside remain persistent?
   
       Or would it be possible to commit up to now and resume (maybe
       a new transaction)?
   
       What would the syntax be for these statements,  or  must  the
       function/trigger call special functions in the backend on the
       C level?
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #