Re: [HACKERS] rules ON SELECT

> Hi,
>
>     I'm  currently thinking about multiple action and non-INSTEAD
>     rules ON SELECT. I'm not sure what users  might  expect  when
>     they get fired.
>
>     Well  if a user types SELECT ... FROM tab and there are rules
>     ON SELECT TO tab, then of course. But what about if the  user
>     issues  an  INSERT  INTO x SELECT ... FROM tab or an UPDATE x
>     SET col = tab.attr? In fact tab is scanned and returns  data.
>     Should the rule ON SELECT then be fired too?
>
>     And  what  the  hell  is  all that good for? Do we need other
>     rules ON SELECT than those that build views  (which  we  have
>     now)?  Tracing which data one user uses? Cannot be what rules
>     are made for.

I would guess that SELECT ... FROM tab and INSERT INTO x SELECT ... FROM
tab would use the rules, but I can see where and UPDATE is using the
data from the table just like a SELECT, so I can see it would be
confusing for some if it DID do the rule in that case, and for others if
it DID NOT use the rule.  That is a no-win situation, and usually means
that there is a bad interface design.  However, in our case, it is just
a "much too powerful" interface design.

Please use your judgement.  I can see that you are saying that there
really is no reason for SELECT rules except to create views.  However,
if someone wants a computed column as part of a table, AND they want the
table name to be the same, isn't a SELECT rule the only way to do that.
They could of course rename the table, and create a view with the old
name.  This may be the clearest way to do it.

I think if you make it consistent and/or print something to the user
when they try and do something strange, that usually keeps people happy.
If they understand what it is doing, the will usually accept it,
especially if it is a SUPERSET of SQL.

>
>     If nobody votes against, I would only add some code  checking
>     that  there  is  at  max one INSTEAD SELECT rule that returns
>     exactly the relations tuple structure  ON  SELECT  (currently
>     with  CREATE TABLE, CREATE RULE someone can setup a situation
>     that crashes the backend on  SELECT).  So  SELECT  rules  are
>     totally restricted to view building and nothing else.
>
>     After  that I'll tidy up the rewrite code (the work I've done
>     screwed it up a  little  with  nearly  duplicate  functions).
>     Anything except for bug fixing is then delayed for 6.5.
>
>     I  still have in mind that we wanted to have views of UNIONS,
>     DISTINCT views and some more. But since they require  totally
>     different  semantics  (the resulting plan must have something
>     like a subselect of union in the case of an UPDATE...)   this
>     is  far  too  much  and has bad bad traps deep inside. We all
>     don't want to fall into one during BETA.

Sounds good to me.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026