Thread: rules

Hi!
I try to learn how to create rules in postgres, but something goes wrong,
cause nothing happens (or eventually some errors).
I have two tables
create table one (
    one_id     int4 primary key not null default nextval('schem_seq'),
    one_name text,
    one_lock text
);

create table many (
    many_id            int4 primary key not null default
                nextval('schem_seq'),
    many_onef        int4 not null, //this is foreign key equal
to one_id from one table
    many_name        text,
    many_lock        text
    );

I try to create two rules
-first one on update one_id in one table
CREATE RULE rupd_one AS ON UPDATE TO one
    DO UPDATE many
        SET many_onef = new.one_id
        WHERE many_onef = current.one_id;

-second one on delete record from one table
CREATE RULE rdel_one AS ON DELETE TO one
    DO DELETE FROM many
        WHERE many_onef = old.one_id;

In short these two seem don't work. I'm not sure of either syntax or some
other errors - psql at first rule don't report any errors, just output
UPDATE 0
and second one usually something like this:
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
        This probably means the backend terminated abnormally before or
while processing the request.
    Thanks for any response
    Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski      e-mail: rems@gdansk.sprint.pl           * *
-----------------------------------------------------------------*****----------

> I try to create two rules
> -first one on update one_id in one table
> CREATE RULE rupd_one AS ON UPDATE TO one
>    DO UPDATE many
>         SET many_onef = new.one_id
>         WHERE many_onef = current.one_id;
>
> -second one on delete record from one table
> CREATE RULE rdel_one AS ON DELETE TO one
>    DO DELETE FROM many
>         WHERE many_onef = old.one_id;
>
> In short these two seem don't work. I'm not sure of either syntax or some
> other errors - psql at first rule don't report any errors, just output
> UPDATE 0
> and second one usually something like this:
> PQexec() -- Request was sent to backend, but backend closed the channel
> before responding.
>         This probably means the backend terminated abnormally before or
> while processing the request.

    There  seems  nothing  wrong  with the rules, except that you
    should use OLD instead of CURRENT  in  the  update  rule  too
    because CURRENT will disappear in v6.5.

    But  from  the  'UPDATE  0'  I  guess you're using a Postgres
    version prior to v6.4 - right? I fixed the  rule  system  for
    v6.4,  before that it was so badly broken that you should not
    even try.


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

[5 Mar 99,, 16:58] Jan Wieck wrote:

>
>     But  from  the  'UPDATE  0'  I  guess you're using a Postgres
>     version prior to v6.4 - right? I fixed the  rule  system  for
>     v6.4,  before that it was so badly broken that you should not
>     even try.
Thanks a lot. I forgot to mention that I indeed use Postgres v6.3.2

>
>     There  seems  nothing  wrong  with the rules, except that you
>     should use OLD instead of CURRENT  in  the  update  rule  too
>     because CURRENT will disappear in v6.5.
>
>     But  from  the  'UPDATE  0'  I  guess you're using a Postgres
>     version prior to v6.4 - right? I fixed the  rule  system  for
>     v6.4,  before that it was so badly broken that you should not
>     even try.
>
You're right - I use ver. 6.3.2
Ufff - I feel better, when I know :-)
thanks
    Rem

-------------------------------------------------------------------*------------
Remigiusz Sokolowski      e-mail: rems@gdansk.sprint.pl           * *
-----------------------------------------------------------------*****----------