Timeline for how to chain postgres RULEs?
Current License: CC BY-SA 3.0
18 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 17, 2013 at 8:18 | vote | accept | Milovan Zogovic | ||
| Mar 16, 2013 at 19:22 | history | migrated | from stackoverflow.com (revisions) | ||
| Mar 16, 2013 at 7:34 | answer | added | Chris Travers | timeline score: 7 | |
| Mar 13, 2013 at 18:14 | answer | added | Andrew Lazarus | timeline score: 0 | |
| Mar 13, 2013 at 12:50 | comment | added | Milovan Zogovic | Thanks @wildplasser. I rewrote the question to better express what exactly is the problem.. | |
| Mar 13, 2013 at 12:33 | answer | added | wildplasser | timeline score: 0 | |
| Mar 13, 2013 at 11:18 | comment | added | wildplasser | Your problem is in the data model. The rules should have a where-clause that at least should pin the primary key of the target tables, {day, project_id}, {day, client_id} etc. These should also be FK in the person_hits table. Also: If I understand correctly the target tables are supposed to be materialised aggregates, and the action should be a decrement of the hit-cound, instead of a delete action. | |
| Mar 13, 2013 at 11:13 | comment | added | Milovan Zogovic | I've included real example in latest update (gist.github.com/assembler/5151102). You can run it on your machine to simulate the problem.. | |
| Mar 13, 2013 at 11:11 | comment | added | wildplasser | I fail to see the logic behind the rules. Is it the intention that if a row is deleted for a specific day, the corrseponding records for {week,month,year} will also be deleted? That would mean that the year table will only contain records for the current day... Also: Please add some table definitions. Oops, I missed the github link. | |
| Mar 13, 2013 at 11:03 | comment | added | Milovan Zogovic | Thanks @ChrisTravers. I have included the script which reproduces the problem. | |
| Mar 13, 2013 at 9:11 | comment | added | Chris Travers | (I would say rules are no longer a standard beginner's tool. They are however a very useful advanced tool once you really get what you are doing.) | |
| Mar 13, 2013 at 9:10 | comment | added | Chris Travers | Can you include a query plan (explain output). Don't run explain analyse since that will hang. Just standard explain. | |
| Mar 13, 2013 at 9:09 | comment | added | Chris Travers | Rules are not deprecated in favor of triggers. They are very different and a bit of a hassle to work with. The big reason is that for simple udateable views, they are far more flexible than triggers when it comes to performance tuning. There are many Pg folks who would like them to be deprecated and many more who do not feel they should ever be deprecated. | |
| Mar 13, 2013 at 8:21 | comment | added | Milovan Zogovic |
by joining pg_locks with pg_class I found that all of the relations that should be affected (per_day, per_week, per_month...) are indeed in pg_locks.
|
|
| Mar 13, 2013 at 7:45 | comment | added | Milovan Zogovic | I'll check the pg_locks and pg_stat activitiy and will let you know what i find.. thanks | |
| Mar 13, 2013 at 7:43 | comment | added | Milovan Zogovic | According to postgresql docs (postgresql.org/docs/9.2/static/rules-triggers.html), rules are not deprecated in favor of triggers. Can you point me to reference that state that triggers should be used instead of rules? I need rules for performance reasons millions of rows should be deleted as result of single row deletion. Rules perform a LOT faster in this situation. | |
| Mar 12, 2013 at 17:29 | comment | added | user1822 |
Rules are "somewhat" deprecated. There is a clear recommendation from the Postgres team to use triggers instead of rules. And what exactly does "blocks forever" mean? Do you see locks in pg_locks? What's the state of your statement in pg_stat_activity?
|
|
| Mar 12, 2013 at 17:05 | history | asked | Milovan Zogovic | CC BY-SA 3.0 |