Transactions in rules..

Fabrizio Mazzoni wrote:

Can i use transactions in rules??
I was trying something like:

create rule x_test as
on insert to view1
do instead
(
begin;
insert into test1 values (new.a,new.b);
insert into test2 values (new.c,new.d);
commit;
);

But when i give this command in psql i always get an error..

There is no way to execute a statement without having a
transaction in PostgreSQL. If you're not inside of a
transaction block, even a simple SELECT from the client
(psql, application) will have it's own transaction.

For your above case, if you don't do BEGIN/COMMIT, the INSERT
will have it's own Xact with automatic COMMIT (if no ERROR
happens). The rule actions will allways belong to the same
Xact the INSERT does, so either all is committed or nothing.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Fabrizio Mazzoni writes:

Can i use transactions in rules??
I was trying something like:

create rule x_test as
on insert to view1
do instead
(
begin;
insert into test1 values (new.a,new.b);
insert into test2 values (new.c,new.d);
commit;
);

The statements added by the rule will automatically be executed in the
same one transaction as the original statement that fired the rule. Your
syntax example would sort of invoke a nested transaction, which doesn't
exist. All in all, you don't need to worry about it.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Cannot do this, as currently there is no support for nested transactions.

On Mon, 9 Jul 2001, Fabrizio Mazzoni wrote: