Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
Added to TODO: * Allow BEFORE INSERT triggers on views http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php --- Tom Lane wrote: Karl O. Pinc [EMAIL PROTECTED] writes: why can't I put a SELECT rule on a table? Because then it would be a view. As for $SUBJECT, the problem is that there will never be an insert into a view --- not at the level of a physical insert attempt anyway --- and thus there is nothing for a trigger to do. The reason there will never be an insertion trigger event is that we reject any INSERT on a view that isn't rewritten (by an unconditional DO INSTEAD rule) into something else. I recall a prior discussion about making it possible to use triggers on views as a substitute for DO INSTEAD rules, by removing the rewrite-time check and only erroring out if we actually get to the point of attempting a physical insert. Then a BEFORE INSERT trigger could do something appropriate with the data and return NULL to prevent the error. This seems like a good idea because triggers often are much easier to work with than rules --- eg, there's no problem with multiple evaluations of volatile functions, even if you send the data to several places. However, I'm not sure that the idea scales to cover updates and deletes; with no concept of physical tuple identity (ctid) for the view rows, it's not clear that you can write triggers that will reliably do the right things. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 11:24:40 PM, Jaime Casanova wrote: On 2/24/07, Karl O. Pinc [EMAIL PROTECTED] wrote: http://www.postgresql.org/docs/current/static/rules-views.html Actually, i found it very clear: if you create a SELECT rule on a table it becomes a view, this is what postgres does every time you create a view It does say that. But it does not say that if you do it exactly the same thing will happen. I figured postgres does that internally, when it knows it wants to make a view, but if I did it explicitly I'd have a table with a select rule on it that would operate just like a view but would still be a table. And why not let me have a table with a select rule on it that makes the table act like a view, but that I can otherwise fiddle with myself, like adding triggers, and make of it what I want? Then it's up to me to expose the foreign keys or whatever else the triggers need to work. That would be the painless solution that would work just fine for my purposes, however nice it'd be to be able to put BEFORE triggers on VIEWS -- after exposing all columns of the underlying tables etc. (As I fantasized about in a previous post.) why not simply create BEFORE TRIGGERS on the base table, CREATE VIEW on top and use an INSERT/UPDATE/DELETE rule on the view to rewrite those operations to the equivalents on the tables... that way you will have your TRIGGERS validating the data... Because the view has columns that the underlying table does not, that are computed but that I might want to update through, validate, etc. See my previous post. Somebody already asked this exact question. I could write triggers on all my underlying tables that do the instantiated view thing and update another table with all the right info. And _then_ do what you suggest, being sure to pass the data back to the true underlying tables. (Actually, at that point there'd be no point in CREATE VIEW at all.) But that's a lot of work and why go to the trouble when an ordinary view will do just fine (as far as output goes anyway, modification is where I have trouble.) Having a table with real data in it seems like a lot of overhead I don't need. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
Hi, I want to do some additional data validation when data is changed through a view, and I want pretty exception messages, and I want to do some slightly complex processing when determining what data to update where in what order. So, I figured I'd make a table, put some BEFORE EACH ROW triggers on it to do all the tricky stuff, sometimes including changing other tables, and then put a SELECT rule on the table to make it look like a view. Turns out there's undocumented restrictions, and this turns the table into a view which then can't have triggers on it. I can put other sorts of rules onto a table, why can't I put a SELECT rule on a table? What's the special case here? It's probably conceivably possible to do what I want using piles of functions called by CHECK constraints, and possibly many conditional rules, but that's just grody. For me, having a select rule on a table would have additional benefit in that the front-end would recognize the table as something that can be modified. (PhpPgAdmin) As of now it considers views as un-modifiable. If a relation looks like a table in all respects, why can't it _be_ a table? If it's a matter of nobody having done the work I might conceively possibly be able to come up with a patch. Any help would be appreciated. Thanks. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
Karl O. Pinc wrote: Hi, I want to do some additional data validation when data is changed through a view, and I want pretty exception messages, and I want to do some slightly complex processing when determining what data to update where in what order. So, I figured I'd make a table, put some BEFORE EACH ROW triggers on it to do all the tricky stuff, sometimes including changing other tables, and then put a SELECT rule on the table to make it look like a view. Turns out there's undocumented restrictions, and this turns the table into a view which then can't have triggers on it. huh? create your table, create your triggers on the table. Use a view on top of the table for viewing (there is no such thing as a select rule (well there is, but it is called a view)). Sincerely, Joshua D. Drake Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote: create your table, create your triggers on the table. Use a view on top of the table for viewing (there is no such thing as a select rule (well there is, but it is called a view)). Yes, and you can't put a BEFORE EACH ROW trigger on a view, which is my problem. I have _additional_ constraints to place on modifications done through views, and trickyness involved in modifying the underlying tables. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
Karl O. Pinc wrote: On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote: create your table, create your triggers on the table. Use a view on top of the table for viewing (there is no such thing as a select rule (well there is, but it is called a view)). Yes, and you can't put a BEFORE EACH ROW trigger on a view, which is my problem. I have _additional_ constraints to place on modifications done through views, and trickyness involved in modifying the underlying tables. Why can't you put the before each row trigger on the base table? Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote: create your table, create your triggers on the table. Use a view on top of the table for viewing (there is no such thing as a select rule (well there is, but it is called a view)). Maybe you don't understand, I want to modify the underlying tables by using the view, because the view presents something that is more understandable to the user. Sorry to be testy. It's been a long day going down a road and finding a roadblock at the end and the postgresql docs have never let me down before. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
. I have _additional_ constraints to place on modifications done through views, and trickyness involved in modifying the underlying tables. Write a function foo that returns a set, then a view: create view as select * from foo(). Incorporate all the trickiness in the function, including variables, multiple temporary tables, whatever you need. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
I also think that a view is supposed to be just that -- a *view* of underlying data, which in no way modifies the data. I don't know much about the design ideas behind SQL, but I think this view of views (haha) is an underlying assumption. If you are modifying data when you do a select on a view, you are probably not using SQL the way it was intended (not that that is a bad thing, but ...) Postgresql has rules which I *think* can rewrite select statements. Rules are kind of a pain, but maybe what you want. On 2/24/07, Webb Sprague [EMAIL PROTECTED] wrote: . I have _additional_ constraints to place on modifications done through views, and trickyness involved in modifying the underlying tables. Write a function foo that returns a set, then a view: create view as select * from foo(). Incorporate all the trickiness in the function, including variables, multiple temporary tables, whatever you need. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 06:47:56 PM, Joshua D. Drake wrote: Karl O. Pinc wrote: On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote: create your table, create your triggers on the table. Use a view on top of the table for viewing (there is no such thing as a select rule (well there is, but it is called a view)). Yes, and you can't put a BEFORE EACH ROW trigger on a view, which is my problem. I have _additional_ constraints to place on modifications done through views, and trickyness involved in modifying the underlying tables. Why can't you put the before each row trigger on the base table? Consider this: I have two columns in the view A and A1, A1 is the result of calling a reversible function on A. If the user updates A, the underlying A should be updated. If the user updates A1, the underlying A should be updated by computing the proper value for A by calling the inverse of the function used to compute A1. If the user specifies both A and A1, then check to see if the values are in conflict. If so raise and exception. If not update the underlying A. And this: Table X has an Id column and a Name column. It's joined with another table to make a view. 99.9% of the time, the Name value is unique in X. If the user updates the view and supplies a Name we want the view to check if it's unique and, if so, come up with the right Id so that the underlying table can be modified. If the user specifies an Id also, we again need to check for consistency with the specified Name and raise an exception if there's an inconsistency. We do not want the user using the view to update the Name through the view. The view is a 1 (of X) to many (of the table X joins with, say Y), and this confuses the user. We want the user to use the view to to move a row of Y -- re-relate it to a different row in X by changing the Name value. Get very many of these situations and you can see where it'd be much easier to have all the logic in one trigger that does all the work. Most of the problem comes down to raising exceptions. If you could raise exceptions in SQL code you might possibly be able to write large, multi-statement rules and not have lots of rules with lots of conditions attached. But why? Raising your own exceptions are nice because they can mean something to the user -- with data values put in the middle of explanatory text, etc. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 06:51:27 PM, Webb Sprague wrote: . I have _additional_ constraints to place on modifications done through views, and trickyness involved in modifying the underlying tables. Write a function foo that returns a set, then a view: create view as select * from foo(). Incorporate all the trickiness in the function, including variables, multiple temporary tables, whatever you need. I tried that, and have already sent a message to this list about it. The function needs to be passed NEW.* in order to know what data it's looking at. When you try to do that you get the following error message: ERROR: function expression in FROM may not refer to other relations of same query level See: http://archives.postgresql.org/pgsql-general/2007-02/msg01074.php Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 06:55:45 PM, Webb Sprague wrote: I also think that a view is supposed to be just that -- a *view* of underlying data, which in no way modifies the data. I don't know much about the design ideas behind SQL, but I think this view of views (haha) is an underlying assumption. If you are modifying data when you do a select on a view, you are probably not using SQL the way it was intended (not that that is a bad thing, but ...) The postgresql docs say something like: Having lots of views is the mark of good database design. That's because views present the same data in various ways, to different users as each user thinks of the data. In general, they allow good db design, and in a well designed db will be incomprensibe to the average user, partly because it contains lots of foreign keys to manage 1-to-many relationships. If it makes sense to make a view so the user can see the data in a form they can understand, then it makes sense to allow the user to update the same view of the data. Postgresql has rules which I *think* can rewrite select statements. Rules are kind of a pain, but maybe what you want. You can't rewrite select statments. That's where I started. Trying to do so turns your table into a view, which then can't have triggers. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
Karl O. Pinc [EMAIL PROTECTED] writes: why can't I put a SELECT rule on a table? Because then it would be a view. As for $SUBJECT, the problem is that there will never be an insert into a view --- not at the level of a physical insert attempt anyway --- and thus there is nothing for a trigger to do. The reason there will never be an insertion trigger event is that we reject any INSERT on a view that isn't rewritten (by an unconditional DO INSTEAD rule) into something else. I recall a prior discussion about making it possible to use triggers on views as a substitute for DO INSTEAD rules, by removing the rewrite-time check and only erroring out if we actually get to the point of attempting a physical insert. Then a BEFORE INSERT trigger could do something appropriate with the data and return NULL to prevent the error. This seems like a good idea because triggers often are much easier to work with than rules --- eg, there's no problem with multiple evaluations of volatile functions, even if you send the data to several places. However, I'm not sure that the idea scales to cover updates and deletes; with no concept of physical tuple identity (ctid) for the view rows, it's not clear that you can write triggers that will reliably do the right things. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
I recall a prior discussion about making it possible to use triggers on views as a substitute for DO INSTEAD rules, by removing the rewrite-time check and only erroring out if we actually get to the point of attempting a physical insert. Then a BEFORE INSERT trigger could do something appropriate with the data and return NULL to prevent the error. This seems like a good idea because triggers often are much easier to work with than rules --- It is also my experience that triggers are faster than rules. At least with table partitioning. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
(Important stuff last.) On 02/24/2007 07:48:58 PM, Tom Lane wrote: The reason there will never be an insertion trigger event is that we reject any INSERT on a view that isn't rewritten (by an unconditional DO INSTEAD rule) into something else. (Yup. But I tried to make my own view implimentation by putting a SELECT rule on a table and that failed because I couldn't make a BEFORE EACH ROW trigger. Problems putting a SELECT rule on a table would be just another way to phrase $SUBJECT. The annoying part was that I spent much of the day assuming it would work and then when I tested what I wanted I found that the CREATE RULE statement required that I supply _RETURN as a rule name, and that then it wouldn't create the rule anyway because the table had triggers. Usually this sort of thing happens when I mis-read the docs, but this time the docs provided no warning.) I recall a prior discussion about making it possible to use triggers on views as a substitute for DO INSTEAD rules, by removing the rewrite-time check and only erroring out if we actually get to the point of attempting a physical insert. I had no error check fallback, but if I wanted one I suppose I'd do a AFTER ... EACH ROW trigger that raised an exception if it was ever called. I did do a BEFORE EACH STATEMENT trigger that raised an exception and figured on deleting it for those views that I made that needed BEFORE EACH ROW triggers. Rules worked just fine for some views and I was going to leave those, but allways do the views my way for consistency instead of sometimes using CREATE VIEW and sometimes not. Then a BEFORE INSERT trigger could do something appropriate with the data and return NULL to prevent the error. Exactly. My plan was to always have the trigger return NULL so the underlying table would never be modified. This seems like a good idea because triggers often are much easier to work with than rules --- eg, there's no problem with multiple evaluations of volatile functions, even if you send the data to several places. However, I'm not sure that the idea scales to cover updates and deletes; with no concept of physical tuple identity (ctid) for the view rows, it's not clear that you can write triggers that will reliably do the right things I started with inserts, ran into problems, and came screaming to the list for help. (Thanks.) I somehow assumed that I'd be able to get a hold of NEW.* and OLD.* in my triggers -- because they were there for me in when I first tried to impliment the logic with rules. How is it that the rules can come up with NEW and OLD for a view and why wouldn't something be able to give triggers the same data. (At least BEFORE ... EACH ROW triggers, we wouldn't care about other triggers, would we?) Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 08:30:21 PM, Karl O. Pinc wrote: How is it that the rules can come up with NEW and OLD for a view and why wouldn't something be able to give triggers the same data. Ah, NEW and OLD are only good in the WHERE part of the rule, which is still in query land country before execution starts. At this point my ignorance is blinding me. Couldn't the backend somehow use the SELECT rule to produce and iterate over what's to be modified so there'd be something to have for OLD? Merge the WHERE part of the query plan for the modification with the underlying WHERE part? Likewise for NEW? It must do something like that for the WHERE part of the rule to get NEW and OLD. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
Karl O. Pinc [EMAIL PROTECTED] writes: On 02/24/2007 07:48:58 PM, Tom Lane wrote: However, I'm not sure that the idea scales to cover updates and deletes; with no concept of physical tuple identity (ctid) for the view rows, it's not clear that you can write triggers that will reliably do the right things How is it that the rules can come up with NEW and OLD for a view and why wouldn't something be able to give triggers the same data. NEW and OLD only include the user-visible columns. I'm not sure that that's sufficient. If you assume that the view exposes a primary key for each of its underlying tables, then you could use the pkey values to find and update a row in the underlying table(s), but this is not exactly guaranteeing that you're updating the same row that the view query saw initially. And what if you don't want the view to expose the pkey? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 08:48:04 PM, Karl O. Pinc wrote: Ah, NEW and OLD are only good in the WHERE part of the rule, which is still in query land country before execution starts. No. I'm wrong here. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 08:55:40 PM, Tom Lane wrote: Karl O. Pinc [EMAIL PROTECTED] writes: NEW and OLD only include the user-visible columns. I'm not sure that that's sufficient. If you assume that the view exposes a primary key for each of its underlying tables, then you could use the pkey values to find and update a row in the underlying table(s), but this is not exactly guaranteeing that you're updating the same row that the view query saw initially. And what if you don't want the view to expose the pkey? These are INSERT, UPDATE, and DELETEs, so why not get rid of the SELECT column criteria and modify the query plan to come up with a SELECT * ? In other words, why not expose everything to the trigger? You'd only be doing this if there wasn't otherwise a rule for, say, UPDATE, on the view anyhow. Oh. You'd have to keep any non-columm expressions that the view exposes. NEW sounds easy, but I don't know about OLD. Somehow the rules are doing something for OLD now. You'd have to come up with column name conventions (or some syntax) for NEW and OLD to handle conflicts should some joined tables have non-unique column names. (The ones exposed by the view already have names.) I don't know the right way to approach this problem but it feels tractable. (To somebody who's very unlikely to be writing the code. FYI, you're like the Pg-General-List-Code-Angel.) Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 2/24/07, Karl O. Pinc [EMAIL PROTECTED] wrote: (Important stuff last.) On 02/24/2007 07:48:58 PM, Tom Lane wrote: The reason there will never be an insertion trigger event is that we reject any INSERT on a view that isn't rewritten (by an unconditional DO INSTEAD rule) into something else. (Yup. But I tried to make my own view implimentation by putting a SELECT rule on a table and that failed because I couldn't make a BEFORE EACH ROW trigger. Problems putting a SELECT rule on a table would be just another way to phrase $SUBJECT. The annoying part was that I spent much of the day assuming it would work and then when I tested what I wanted I found that the CREATE RULE statement required that I supply _RETURN as a rule name, and that then it wouldn't create the rule anyway because the table had triggers. Usually this sort of thing happens when I mis-read the docs, but this time the docs provided no warning.) http://www.postgresql.org/docs/current/static/rules-views.html Actually, i found it very clear: if you create a SELECT rule on a table it becomes a view, this is what postgres does every time you create a view I started with inserts, ran into problems, and came screaming to the list for help. (Thanks.) I somehow assumed that I'd be able to get a hold of NEW.* and OLD.* in my triggers -- because they were there for me in when I first tried to impliment the logic with rules. How is it that the rules can come up with NEW and OLD for a view and why wouldn't something be able to give triggers the same data. (At least BEFORE ... EACH ROW triggers, we wouldn't care about other triggers, would we?) why not simply create BEFORE TRIGGERS on the base table, CREATE VIEW on top and use an INSERT/UPDATE/DELETE rule on the view to rewrite those operations to the equivalents on the tables... that way you will have your TRIGGERS validating the data... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
