RE: A little RULE help?

On 01/25/2018 03:14 PM, Steven Winfield wrote:
>
> https://www.postgresql.org/docs/10/static/rules-triggers.html<https://www.postgresql.org/docs/10/static/rules-triggers.html>
>
> “For the things that can be implemented by both, which is best depends
> on the usage of the database. A trigger is fired once for each
> affected row. A rule modifies the query or generates an additional
> query. So if many rows are affected in one statement, a rule issuing
> one extra command is likely to be faster than a trigger that is called
> for every single row and must re-determine what to do many times.
> However, the trigger approach is conceptually far simpler than the
> rule approach, and is easier for novices to get right.”
>

Well like I said, it may be better for what you are doing. I am not sure
but I can say from personal implementation experience that for old
school partitioning (e.g; everything before 10), triggers were so much
faster than rules that the general rule was, "don't use rules". That may
have changed since that experience.

JD

/
------------------------------------------------------------------------
///

--
Command Prompt, Inc. || http://the.postgres.company/<http://the.postgres.company/> || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org<https://postgresconf.org>
***** Unless otherwise stated, opinions are my own. *****

There have been comments on- and off-list about rules generally being slower than rules, which seemed counterintuitive (for my case at least) so I’ve done some timings.
(Also note that I haven’t done any partitioning here, new- or old-style - a few people have mentioned RULEs in relation to partitioning).
These compare the INSERT and UPDATE rules against equivalent INSTEAD OF triggers. Best of three runs each time, times are in seconds.

rows 10^4 10^5 10^6
insert rule 0.9 15.0 179
insert trigger 1.3 19.7 224
delete rule 1.8 22.8 282
delete trigger 2.3 28.0 331

…so the rules are about 20% faster than the triggers. Significant, but not game-changing.
Note that this is on quite close to a “real life” table too - there is the maintenance of the primary key index and the gist index that supports the exclude constraint in all those timings, so a table without those would have shown a bigger disparity between the two methods.
This makes sense - the RULEs just have one query to rewrite before it is planned and executed, whereas the TRIGGERs have to be re-executed for each row.

Back to my original attempt at writing an UPDATE rule…

CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD (
UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id;
INSERT INTO rule_test (tt, foo, bar) VALUES (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *;
);

…I wondered whether the pseudo relations NEW and OLD were somehow being modified by the first command (the UPDATE), such that the second command (INSERT) could not function properly. That would fit with what I observe, but I’m not sure how I go about proving or fixing it.

Best,
Steven.