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.klaver@aklaver.com