Обсуждение: AW: [HACKERS] Begin statement again

Поиск
Список
Период
Сортировка

AW: [HACKERS] Begin statement again

От
Zeugswetter Andreas
Дата:
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.





Re: AW: [HACKERS] Begin statement again

От
jwieck@debis.com (Jan Wieck)
Дата:
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) #

Re: AW: [HACKERS] Begin statement again

От
sferac@bo.nettuno.it
Дата:
On Fri, 13 Mar 1998, Zeugswetter 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)

Apologies for intrusion.

I think we don't need BEGIN/END at all, these statements aren't SQL standard.
END is an alias for COMMIT.
(why do we need two statements to do the same thing?).

from man commit:
       "...
       This   commands  commits  the  current  transaction.   All
       changes made by the transaction become visible  to  others
       and  are guaranteed to be durable if a crash occurs.
       COMMIT is functionally equivalent to the END command"
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

from man begin:
       "...
       commands  commits  the  current  transaction.   All
       changes made by the transaction become visible  to  others
       and are guaranteed to be durable if a crash occurs."

and BEGIN should be changed to SQL standard SET TRANSACTION statement.
-------
PS:
    I think PL/pgSQL is an eccellent idea. Go for it.
                                                            Ciao, Jose'



RE: AW: [HACKERS] Begin statement again

От
"Meskes, Michael"
Дата:
We, that is ecpg, will need BEGIN and END to include blocks of Pl/pgSQL.
I think this can be modelled after ORACLE's PL/SQL. in your embedded SQL
code you say:

    ...
    exec sql begin;
        <some PL/SQL code, for instance the call of a stored
procedure>
    exec sql end;
    ...

And I'd really like to call a stored procedure from my C program.

Michael
--
Dr. Michael Meskes, Projekt-Manager    | topystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux!      | Tel: (+49) 2405/4670-44

> ----------
> From:     sferac@bo.nettuno.it[SMTP:sferac@bo.nettuno.it]
> Sent:     Freitag, 13. März 1998 18:28
> To:     Zeugswetter Andreas
> Cc:     'Michael Meskes'; 'pgsql-hackers@hub.org'
> Subject:     Re: AW: [HACKERS] Begin statement again
>
> On Fri, 13 Mar 1998, Zeugswetter 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)
>
> Apologies for intrusion.
>
> I think we don't need BEGIN/END at all, these statements aren't SQL
> standard.
> END is an alias for COMMIT.
> (why do we need two statements to do the same thing?).
>
> from man commit:
>        "...
>        This   commands  commits  the  current  transaction.   All
>        changes made by the transaction become visible  to  others
>        and  are guaranteed to be durable if a crash occurs.
>        COMMIT is functionally equivalent to the END command"
>        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> from man begin:
>        "...
>        commands  commits  the  current  transaction.   All
>        changes made by the transaction become visible  to  others
>        and are guaranteed to be durable if a crash occurs."
>
> and BEGIN should be changed to SQL standard SET TRANSACTION statement.
> -------
> PS:
>     I think PL/pgSQL is an eccellent idea. Go for it.
>                                                             Ciao,
> Jose'
>
>

Re: AW: [HACKERS] Begin statement again

От
"Vadim B. Mikheev"
Дата:
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

Re: AW: [HACKERS] Begin statement again

От
dg@illustra.com (David Gould)
Дата:
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.


Re: AW: [HACKERS] Begin statement again

От
jwieck@debis.com (Jan Wieck)
Дата:
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) #