Re: A little RULE help?

On 01/25/2018 03:34 PM, Steven Winfield wrote:
> Maybe this?:
>
> https://www.postgresql.org/docs/10/static/sql-createrule.html
>
> "There is a catch if you try to use conditional rules for complex view
> updates: there must be an unconditional INSTEAD rule for each action you
> wish to allow on the view. If the rule is conditional, or is not
> INSTEAD, then the system will still reject attempts to perform the
> update action, because it thinks it might end up trying to perform the
> action on the dummy table of the view in some cases. If you want to
> handle all the useful cases in conditional rules, add an unconditional
> DO INSTEAD NOTHING rule to ensure that the system understands it will
> never be called on to update the dummy table. Then make the conditional
> rules non-INSTEAD; in the cases where they are applied, they add to the
> default INSTEAD NOTHING action. (This method does not currently work to
> support RETURNING queries, however.)"
>
> Thanks, I saw that – but none of my rules are conditional, and they are
> all INSTEAD OF, so I didn’t think that was the problem.

My mistake I saw:

UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)')
WHERE id = OLD.id;

and in my head transformed that into a conditional RULE.

>
> FYI, I gave up on RULE's a while back. Triggers are a lot easier to
> figure out and maintain.
>
> These rules (including the non-working UPDATE rule) seemed fairly small
> and quite readable,
>
> so I didn’t think maintenance would be a problem.
>
> Debugging this is a bit harder, however…

The reason I gave up on rules, figuring out how the query rewrite system
works is a bear. I understand(most of the time) what triggers do, so I
stick with what I understand.

>
> Steve.
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com