Re: Why should my rule be conditional?

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