Обсуждение: Why should my rule be conditional?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I have a view that I want to make updateble, at least kind of. What should
happen on update (trying to implement in one rule):
- - make the original record invisible in the view by changing the value of a
flag in the table underlying table
- - insert a new record into the underlying table
- - update another table (I have not come that far)

Therefore I created following rule:
CREATE OR REPLACE RULE r_v_songs_upd
   AS ON UPDATE TO v_songs
   DO INSTEAD (
      UPDATE t_songs
         SET
            show = FALSE
         WHERE
            NEW.song_id = song_id
      ;
      INSERT INTO t_songs (
            song_title,
            year_of_composition,
            year_of_first_publication,
            predecessor_id
         ) VALUES (
            NEW.song_title,
            NEW.year_of_composition,
            NEW.year_of_first_publication,
            NEW.song_id
         )
   )
;

If I do the update on v_songs, the update part of the rule gets executed fine,
but the insert does not seem to do anything. So I changed to the rule for
testing into:
CREATE OR REPLACE RULE r_v_songs_upd
   AS ON UPDATE TO v_songs
   DO INSTEAD (
      UPDATE t_songs
         SET
            show = FALSE
         WHERE
            NEW.song_id = song_id
      ;
      INSERT INTO t_songs (
            song_title,
            year_of_composition,
            year_of_first_publication,
            predecessor_id
         ) VALUES (
'rübenkraut',1,2,null
         )
   )
;

An update now results in:
psql:data.pgsql:124: ERROR:  Cannot update a view
        You need an unconditional ON UPDATE DO INSTEAD rule

Why? I cannot see where my rule is some kind of conditional. I couldn't find
any hint, neither in the docs, nor googling, nor metacrawling.

Anybody an idea?

Cheers

Thiemo

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/7HZ1ood55Uv4ElYRApYCAJ9Bcom1yFl4juaUtLbT968SLfMkMQCcDb0u
KTg+Zsj1aVjO1ExEjZTYF6c=
=34Uv
-----END PGP SIGNATURE-----

Thiemo Kellner <thiemo@thiam.ch> writes:
> CREATE OR REPLACE RULE r_v_songs_upd
>    AS ON UPDATE TO v_songs
>    DO INSTEAD (
>       UPDATE t_songs
>          SET
>             show = FALSE
>          WHERE
>             NEW.song_id = song_id
>       ;
>       INSERT INTO t_songs (
>             song_title,
>             year_of_composition,
>             year_of_first_publication,
>             predecessor_id
>          ) VALUES (
>             NEW.song_title,
>             NEW.year_of_composition,
>             NEW.year_of_first_publication,
>             NEW.song_id
>          )
>    )
> ;

> If I do the update on v_songs, the update part of the rule gets executed fine,
> but the insert does not seem to do anything.

The above looks like a dead end to me; you can't make it work, and the
reason is that OLD and NEW are defined with reference to the view.  Once
you do the UPDATE, that row is no longer visible in the view (correct?)
and so there is no NEW row and the INSERT doesn't do anything.  Think of
the INSERT as being rewritten into an "INSERT ... SELECT ... FROM view"
sort of construct, and you'll see why.

A gross hack comes to mind:

CREATE OR REPLACE RULE r_v_songs_upd
   AS ON UPDATE TO v_songs
   DO INSTEAD (
      INSERT INTO t_songs (
            song_title,
            year_of_composition,
            year_of_first_publication,
            predecessor_id,
            show
         ) VALUES (
            NEW.song_title,
            NEW.year_of_composition,
            NEW.year_of_first_publication,
            NEW.song_id,
            NULL
         )
      ;
      UPDATE t_songs
         SET
            show = (CASE WHEN show IS NULL THEN TRUE ELSE FALSE END)
         WHERE
            NEW.song_id = song_id
   )
;

but I think I'd recommend looking into using a trigger instead.
The above looks pretty fragile in the presence of concurrent updates,
to name just one problem.

Triggers are notationally more daunting than rules, but conceptually
they are a lot simpler; you're only dealing with one row at a time,
and it can't change underneath you.  Most of the things I see people
trying to use rules for would be better accomplished with a trigger.

> An update now results in:
> psql:data.pgsql:124: ERROR:  Cannot update a view
>         You need an unconditional ON UPDATE DO INSTEAD rule

> Why?

Not sure; could be a bug, but without a complete reproducible example
I'm more inclined to blame pilot error.  Is t_songs itself a view?

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> The above looks like a dead end to me; you can't make it work, and the
> reason is that OLD and NEW are defined with reference to the view.  Once
> you do the UPDATE, that row is no longer visible in the view (correct?)
> and so there is no NEW row and the INSERT doesn't do anything.

Thats right, I didn't take this into account assuming that NEW and OLD would
be unchangeable for the execution of the rule. Then again, why does PostgrSQL
not complain about not being able to insert null (NEW.song_id) into song_id
(this is the pimary key of t_songs)? Or is my use of rules just something,
developers did not foresee? I suppose, it must try to replace all the NEW.*
with null as value. Or if not, I think it ought to complain that NEW.* is not
valid any longer.

Well, I know of such problems with Oracle where you cannot change a table's
content from within a trigger on that table (there is a specific expression
to that but it slipped my mind; if there is interest in this bit of info I
shall see that I can reproduce it). Naïv as I am, I just tried and _didn't_
see what PostgrSQL was trying to do. I feel, the feedback of the server is
quite inadequat at this point.

> but I think I'd recommend looking into using a trigger instead.
> The above looks pretty fragile in the presence of concurrent updates,
> to name just one problem.

Ok, trigger then.

> Triggers are notationally more daunting than rules, but conceptually
> they are a lot simpler; you're only dealing with one row at a time,
> and it can't change underneath you.  Most of the things I see people
> trying to use rules for would be better accomplished with a trigger.

Actually, I can understand people there. Not because triggers might daunt more
(with a Oracle background, triggers are nothing new) but because people
probably don't know what to use. At least I couldn't get information about
when better to use rules and when triggers out of the documentation. They
seem to me quite ambiguous. At least, rules don't seem to provide
functionality triggers don't. Would it be a great loss to depracate rules? (I
know they are used for views. I guess the same funtionality could be
accomplished with triggers. Well, one wouldn't need to drop rules as element.
One would only need to "hide" the 'create rule' statement. Just my two
dimes.)

> > An update now results in:
> > psql:data.pgsql:124: ERROR:  Cannot update a view
> >         You need an unconditional ON UPDATE DO INSTEAD rule
> >
> > Why?
>
> Not sure; could be a bug, but without a complete reproducible example
> I'm more inclined to blame pilot error.  Is t_songs itself a view?

Nope, t_songs is just a nice table (see attachment). BTW: I am using 7.3.4. In
case this is a bug, this piece of info might be of interest. Nonetheless,
pilot error wouldn't be too surprising after all. :-)

Thanks a lot for your explanations.

Thiemo

P.S.: Is it acceptable to send attachments to postgres lists? I could find any
nettiquette or the like.

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/7JtNood55Uv4ElYRAhVLAJ9VxFWknXn9PEqFNJ0xAla+8Qn7YACglZnl
vnM0v5kxD4d3pI9Vme686Jg=
=8YoV
-----END PGP SIGNATURE-----

Thiemo Kellner <thiemo@thiam.ch> writes:
>> The above looks like a dead end to me; you can't make it work, and the
>> reason is that OLD and NEW are defined with reference to the view.  Once
>> you do the UPDATE, that row is no longer visible in the view (correct?)
>> and so there is no NEW row and the INSERT doesn't do anything.

> Thats right, I didn't take this into account assuming that NEW and OLD woul=
> d=20
> be unchangeable for the execution of the rule. Then again, why does PostgrS=
> QL=20
> not complain about not being able to insert null (NEW.song_id) into song_id=
> =20
> (this is the pimary key of t_songs)?

It's not trying to insert a row of nulls; it's simply not inserting any
row at all.  The transformed rule query looks like
    INSERT INTO t_songs SELECT ... FROM v_songs WHERE ...
and the WHERE condition is such that no rows will be selected.

> Or if not, I think it ought to complain that NEW.* is n=
> ot=20
> valid any longer.

No more than selecting from an empty table is invalid, or selecting with
a WHERE condition that matches no rows is invalid.

> Would it be a great loss to depracate rules?

The fact they don't do what you want doesn't make them worthless ...

There is some discussion of rules vs triggers in the docs:
http://www.postgresql.org/docs/7.4/static/rules-triggers.html
though I agree that this page is probably fairly unhelpful for
novices, and could stand to be rewritten.  In particular it focuses
too much on the question of efficiency and doesn't really explain the
very fundamental semantic differences.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> It's not trying to insert a row of nulls; it's simply not inserting any
> row at all.  The transformed rule query looks like
>     INSERT INTO t_songs SELECT ... FROM v_songs WHERE ...
> and the WHERE condition is such that no rows will be selected.

Ok, I thought NEW and OLD would be some kind of variables. As the code in the
rule gets rewritten as an other sql statement, I can see that I was
completely misslead. It might be a point to include in the documentation if
it's not already there and I just didn't read carefully enough.

> > Would it be a great loss to depracate rules?
>
> The fact they don't do what you want doesn't make them worthless ...

I know. That's not what I meant. I tried to say that if triggers can do
everything rules can but just more, there might not be much reason to keep
rules and confusion. However, the doument of your link below quite clearly
states the differences, quite what I needed and was not able to find in the
documentation (of 7.3 but it is there too). Looking at the chapter it is
supposed to be in some chapter 13.7. However, looking in the index (7.3
interactive) there is only one chapter 13 and that's regression test without
a subchapter 7. Do you know how I can navigate to the document in case I
sould need it some month in the future? I hope I don't bother you too much
going on about documentation. Is there somebody who could explain the
documentation system used at docs.postgresql.org?

> There is some discussion of rules vs triggers in the docs:
> http://www.postgresql.org/docs/7.4/static/rules-triggers.html

Many thanks for your patience

Thiemo

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/7LQmood55Uv4ElYRAmzfAKCVZeSI7+58mhGeCT5enI/DOhKEQwCeJE/q
jMDd4HLU/fk5q130Sq1GbEI=
=jytX
-----END PGP SIGNATURE-----

Thiemo Kellner <thiemo@thiam.ch> writes:
> However, the doument of your link below quite clearly
> states the differences, quite what I needed and was not able to find in the
> documentation (of 7.3 but it is there too). Looking at the chapter it is
> supposed to be in some chapter 13.7. However, looking in the index (7.3
> interactive) there is only one chapter 13 and that's regression test without
> a subchapter 7.

I think you're assuming that 7.3 section numbers would apply to 7.4,
which they don't (the html page names are somewhat more likely to carry
across, though).

In 7.3 and before the situation is even more confusing because the docs
are made up of several separate "books" each with its own chapter
numbering.  "Rules vs. Triggers" is section 13.7 of the Programmer's
Guide book (in 7.3 anyway), whereas you seem to have been seeking it in
the Administrator's Guide.

7.4 has just one chapter numbering sequence for the entire SGML document
set, which I think is a considerable improvement.  We're not going to be
revisiting the 7.3 documentation though.  My advice: update to 7.4 ;-)

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am Samstag, 27. Dezember 2003 00.09 schrieb Tom Lane:
> Thiemo Kellner <thiemo@thiam.ch> writes:
> > However, the doument of your link below quite clearly
> > states the differences, quite what I needed and was not able to find in
> > the documentation (of 7.3 but it is there too). Looking at the chapter it
> > is supposed to be in some chapter 13.7. However, looking in the index
> > (7.3 interactive) there is only one chapter 13 and that's regression test
> > without a subchapter 7.
>
> I think you're assuming that 7.3 section numbers would apply to 7.4,
> which they don't (the html page names are somewhat more likely to carry
> across, though).

I am afraid, that I am not:
http://www.postgresql.org/docs/7.3/interactive/rules-triggers.html

> In 7.3 and before the situation is even more confusing because the docs
> are made up of several separate "books" each with its own chapter
> numbering.  "Rules vs. Triggers" is section 13.7 of the Programmer's
> Guide book (in 7.3 anyway), whereas you seem to have been seeking it in
> the Administrator's Guide.

Ok, true. I rather ignored the Programmer's Guide as it's index shows only
three super chapters. :-(

> 7.4 has just one chapter numbering sequence for the entire SGML document
> set, which I think is a considerable improvement.  We're not going to be
> revisiting the 7.3 documentation though.  My advice: update to 7.4 ;-)

Not now, at least not with my productive server. I will waint until Gentoo
releases 7.4 in it's "stable" branch.

Thanks

Thiemo

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/7TiQood55Uv4ElYRAhBwAKCDwvZQAbIBL0V/1gkaR57sGvykKgCeNDFN
phOQsryzi8dL+xBFyNSbqF4=
=ZTld
-----END PGP SIGNATURE-----

Hi guys,

Once again, i´m running into some dificulty with postgres, i need to know if
it possible to encrypt and compile, objects such as functions, and triggers
so that other users can execute it without seing the code it self, if so
could you point me in the right diretion. In SQL server 2000, i would
achieve this using WITH ENCRYPTION command, any help is very much
apreciated, thanks,

Iandé

Iandé Coutinho wrote:
> Once again, i´m running into some dificulty with postgres, i need to know if
> it possible to encrypt and compile, objects such as functions, and triggers
> so that other users can execute it without seing the code it self, if so
> could you point me in the right diretion. In SQL server 2000, i would
> achieve this using WITH ENCRYPTION command, any help is very much
> apreciated, thanks,

Sorry -- this has been discussed before, but no one has ever stepped up
to implement it. I might do it myself someday if I can find the time,
but it isn't real high on my list, so don't hold your breath.

Joe

Hi guys,

Once again, i´m running into some dificulty with postgres, i need to know if
it possible to encrypt and compile, objects such as functions, and triggers
so that other users can execute it without seing the code it self, if so
could you point me in the right diretion. In SQL server 2000, i would
achieve this using WITH ENCRYPTION command, any help is very much
apreciated, thanks,

Iandé