Thread: Transactions in rules..

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..

Regards

fabrizio@macrongolf.com
http://macrongolf.com
http://eteampoint.com
http://macron.com

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

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


On Mon, 9 Jul 2001, 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..
>
> Regards
>
> fabrizio@macrongolf.com
> http://macrongolf.com
> http://eteampoint.com
> http://macron.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

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