Thread
-
triggers, views and rules (not instead)
Zeugswetter Andreas IZ5 <andreas.zeugswetter@telecom.at> — 1998-02-20T14:46:46Z
Jan wrote: The only things not working for copy are rewrite rules. But I think we should restrict rules to the view handling in the future and move forward by implementing a pure and really powerful procedural language. Hm, it looks like you are not really a fan of the rewrite system, eventhough you seem to have the most insight in these matters. I wonder why? Why I like the rewrite system is: 1. select rewrite -- select trigger would be no good (optimizer) 2. The client can be really dumb, like MS Access or some other standard ODBC tool which does not know anything about funcs procs and the like (even without using passthrough) 3. it is a lot more powerful than views 4. it allows the optimizer to get involved (this is where triggers fail per definition) 5. once understood it is very easy to use easier than trigger with c stored procedure at least I guess if triggers could also trigger simple select statements, I could do most of what I want using triggers except of course the select stuff. But as I said I like the rules system very much, especially after your recent fixes Jan :-) So please stick to supporting all 3: triggers, views and rules. Wow :-) Andreas -
Re: [HACKERS] triggers, views and rules (not instead)
Jan Wieck <jwieck@debis.com> — 1998-02-20T16:52:38Z
Andreas wrote: > > Jan wrote: > > The only things not working for copy are rewrite rules. But I > think we should restrict rules to the view handling in the > future and move forward by implementing a pure and really > powerful procedural language. > > Hm, it looks like you are not really a fan of the rewrite system, > eventhough you seem to have the most insight in these matters. I wonder why? Confusing - eh? Well I know much about the internals of the postgres rule system and due to this I know where the limits are. Especially in the case of qualifications it somtimes gets totally confused about what to compare against what. Try to add a delete rule on a view that is simply a select * from another table and then delete some tuples :-) > > Why I like the rewrite system is: > 1. select rewrite -- select trigger would be no good (optimizer) Exactly that's what is done if you create a view. Postgres creates a regular table (look at pg_class and into the database directory) and then sets up a relation level instead rewrite rule on select. > 2. The client can be really dumb, like MS Access or some other > standard ODBC tool > which does not know anything about funcs procs and the like > (even without using passthrough) Yupp - the client must not know why and how and where the data is left and coming from. But that's true in any case - a trigger for each row on insert can do anything different and push the data wherever it wants. > 3. it is a lot more powerful than views As said - views are only one special rule case in Postgres. > 4. it allows the optimizer to get involved (this is where triggers > fail per definition) > 5. once understood it is very easy to use > easier than trigger with c stored procedure at least Optimizing again and again. If the rules aren't instead, the querytree get's additional queries for every rule appended. Have a table field that references an entry in another table and this entry should have a refcount. So on update you must decrease the refcount from the old ref and increase it on the new. You create two rules so the UPDATE will result in 1 scan and 2 nestloops with scans inside - really optimized if the referenced value doesn't change. And don't think that a rule qual of NEW != CURRENT might help - that will result in 2 mergejoins where the scanned tuples are compared. BTW, this sample doesn't work currently because the rules queries are appended at the end of the querytree, thus the decrement scan having the same qual will not find the old tuple at all because it's already outdated (command_counter_increment between processing the queries). Referencing CURRENT in a rule is not what most people think it is. The old 4.2 postgres had a second, instance level rule system (prs2 stubs) that fired the rules actions when actually the old tuple and the new projected tuple where handy. There you could have made also things like 'UPDATE NEW SET a = 4' that really modified the in memory tuple in the executors expression context. Who the hell removed all that? It was so nice :-( A really simple to write trigger can compare old != new and only if send down the other two queries. This time they wont be nestloops, they are simple scans. And the trigger can arrange that the queries it uses are only parsed on it's first of all calls and store the generated execution plans permanently for quick execution (look at SPI_prepare). For the stored C procedures you're totally right. I don't like the C functions because it requires postgres superuser rights to develop them and thus I created PL/Tcl where joe user can hack around without having complete access to the whole database (look at src/pl/tcl). And someday after 6.3 release I'll really start on a plain PL/pgSQL implementation that would give a normal user the opportunity to create functions and triggers on a high level. There is light at the end of the tunnel - hope that it isn't the coming train :-) > > I guess if triggers could also trigger simple select statements, I could do > most of what I want using triggers except of course the select stuff. > But as I said I like the rules system very much, especially after your > recent > fixes Jan :-) So please stick to supporting all 3: triggers, views and > rules. Wow :-) Well - a trigger cannot build a view. The relation underlying the view doesn't contain any tuples and a select trigger will never be fired. As long as there is no possibility to return tuple sets from non-SQL functions. But a trigger can do things like the pg_hide_passwd stuff much more powerful. You could define the trigger so that it checks if the user is a superuser and overwrite the passwd value only in the case where he/she isn't. If fired at the right place it would too work for things like the copy command etc. We must stay with all 3 features. And I will take a look at the INSERT ... SELECT view problem really soon as it is a rule system problem that breaks views. But this is only the SELECT rewriting part of the rule system which I really like (optimizable). The other areas (insert, update, delete) of the rule system are dangerous and I really think a powerful PL/pgSQL language could make them obsolete. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #