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.





Fri, 13 Mar 1998 12:50:27 +-100
Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
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 ?

    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) #



Fri, 13 Mar 1998 13:43:29 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: AW: [HACKERS] Begin statement again
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

Wed, 25 Mar 1998 10:43:44 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
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.


Tue, 24 Mar 1998 22:16:16 -0800 (PST)
dg@illustra.com (David Gould)
Re: AW: [HACKERS] Begin statement again

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) #



Wed, 25 Mar 1998 10:36:36 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: AW: [HACKERS] Begin statement again