Thread: AW: [HACKERS] Begin statement again
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.
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) #
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'
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'
>
>
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
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.
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) #