Re: [ADMIN] Rules/Triggers executio order
On Wed, Oct 18, 2006 at 06:42:21PM -0600, Benjamin Krajmalnik wrote: I have a partitioned table to which I route data using a trigger. I am changing it to use a set of rules which executes INSTEAD on insert. The parent table currently has a trigger. The system is a live system. I would prefer to not have to suspend the data flow. If I create the rules, and given the fact that they execute INSTEAD of the insertion into the parent table, will the trigger still execute. I will be removing the trigger immediately after the creation of the rules, but just want to be safe and make sure I get no duplicate entries. An INSTEAD OF rule replaces the query that you originally had with the one re-written by the rule, so no, the triggers shouldn't fire. But you should test to make sure. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Rules on a view overwrite default values. Any way to
On Mon, 2005-01-03 at 11:02, Bradley Kieser wrote: Hi, I use views extensively to implement security on tables. This requires each table to have a view. All updates, inserts and deletes take place through the view. The view has rules for each of these operations defining security for that table. Under other conditions, the view also removes complex views of the underlying data from the application layer by supplying a view where the rules for update, insert and delete implement business logic. The problem is that rules on a view mean that the default values for NOT NULL columns (used extensively) no longer trigger! E.g. Table A had column updated_time which is not null default now(). Inserting into the view on table A where updated_time has not been supplied will not fill in now(). It will attempt to put in a null value and hence the insert will fail in the insert rule on that view. Is there any way to tell PG to implement the triggers on the underlying table? It will make it extremely difficult to implement this schema if I have to try to put in null field handling... and it really should be PG doing this not me! No doubt if this is a bug, it will be fixed in 8.x! Try changing the udpate triggers you're creating to pass in DEFAULT (similar to NULL in the way it's NOT quoted, etc...) in the update / insert query to the lower level and see if that fixes things. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Rules on a view overwrite default values. Any way to
Hey Scott, that's ingenious, only thing is that I use NOT NULL for many data columns too, where the value may or may not be passed in. Will try with a COALESCE and will post back here. Thx again! Brad Scott Marlowe wrote: On Mon, 2005-01-03 at 11:02, Bradley Kieser wrote: Hi, I use views extensively to implement security on tables. This requires each table to have a view. All updates, inserts and deletes take place through the view. The view has rules for each of these operations defining security for that table. Under other conditions, the view also removes complex views of the underlying data from the application layer by supplying a view where the rules for update, insert and delete implement business logic. The problem is that rules on a view mean that the default values for NOT NULL columns (used extensively) no longer trigger! E.g. Table A had column updated_time which is not null default now(). Inserting into the view on table A where updated_time has not been supplied will not fill in now(). It will attempt to put in a null value and hence the insert will fail in the insert rule on that view. Is there any way to tell PG to implement the triggers on the underlying table? It will make it extremely difficult to implement this schema if I have to try to put in null field handling... and it really should be PG doing this not me! No doubt if this is a bug, it will be fixed in 8.x! Try changing the udpate triggers you're creating to pass in DEFAULT (similar to NULL in the way it's NOT quoted, etc...) in the update / insert query to the lower level and see if that fixes things. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Rules on a view overwrite default values. Any way to
Hmm... I have tested this and it works a treat for cols that I ONLY want to put the default values into, but sadly the coalesce function doesn't accept default as one of its parameters, so I can't use this for columns that I want to default only if null. :-( Scott Marlowe wrote: On Mon, 2005-01-03 at 11:02, Bradley Kieser wrote: Hi, I use views extensively to implement security on tables. This requires each table to have a view. All updates, inserts and deletes take place through the view. The view has rules for each of these operations defining security for that table. Under other conditions, the view also removes complex views of the underlying data from the application layer by supplying a view where the rules for update, insert and delete implement business logic. The problem is that rules on a view mean that the default values for NOT NULL columns (used extensively) no longer trigger! E.g. Table A had column updated_time which is not null default now(). Inserting into the view on table A where updated_time has not been supplied will not fill in now(). It will attempt to put in a null value and hence the insert will fail in the insert rule on that view. Is there any way to tell PG to implement the triggers on the underlying table? It will make it extremely difficult to implement this schema if I have to try to put in null field handling... and it really should be PG doing this not me! No doubt if this is a bug, it will be fixed in 8.x! Try changing the udpate triggers you're creating to pass in DEFAULT (similar to NULL in the way it's NOT quoted, etc...) in the update / insert query to the lower level and see if that fixes things. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Rules on a view overwrite default values. Any way to reinstate them?
Bradley Kieser [EMAIL PROTECTED] writes: The problem is that rules on a view mean that the default values for NOT NULL columns (used extensively) no longer trigger! The way you're supposed to fix this is to attach default values to the view itself. ALTER TABLE myview ALTER COLUMN updated_time SET DEFAULT now(); Now an INSERT on myview will include the correct expression before view expansion happens. I'm not sure how long we've had this, but it's definitely in 7.4. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Rules on a view overwrite default values. Any way to
On Mon, 3 Jan 2005, Bradley Kieser wrote: Hi, I use views extensively to implement security on tables. This requires each table to have a view. All updates, inserts and deletes take place through the view. The view has rules for each of these operations defining security for that table. Under other conditions, the view also removes complex views of the underlying data from the application layer by supplying a view where the rules for update, insert and delete implement business logic. The problem is that rules on a view mean that the default values for NOT NULL columns (used extensively) no longer trigger! E.g. Table A had column updated_time which is not null default now(). Inserting into the view on table A where updated_time has not been supplied will not fill in now(). It will attempt to put in a null value and hence the insert will fail in the insert rule on that view. I believe the easiest way is to attach a default to the view column using ALTER TABLE viewname ALTER COLUMN viewcolumn SET DEFAULT defaultexpr. ---(end of broadcast)--- TIP 3: 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: [ADMIN] Rules on a view overwrite default values. Any way to
Thanks Tom and Stephan! This works perfectly! Brad Tom Lane wrote: Bradley Kieser [EMAIL PROTECTED] writes: The problem is that rules on a view mean that the default values for NOT NULL columns (used extensively) no longer trigger! The way you're supposed to fix this is to attach default values to the view itself. ALTER TABLE myview ALTER COLUMN updated_time SET DEFAULT now(); Now an INSERT on myview will include the correct expression before view expansion happens. I'm not sure how long we've had this, but it's definitely in 7.4. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] rules
Sorry, wrong question.
-Original Message-
From: Jie Liang
Sent: Wednesday, May 19, 2004 10:20 AM
To: Tom Lane
Cc: postgres-list; [EMAIL PROTECTED]
Subject: [ADMIN] rules
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { }
Jie Liang
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
RE: [ADMIN] rules problem
Here is my $0.02 :
* when you create "id SERIAL", Postgres remembers to call function nextval on each
insertion,
* the rule's NEW.id item uses the function nextval itself instead of it's result
This explains why the ID's are what you see :
* first of all, you insert the log, calling nextval for the SERIAL (id=1 in the log)
* then you actually insert the data into the colors table (first row has id=2)
* then you insert a second time : first into the log (id=3) then into the actual table
(id=4)
This make me think about date constants : 'now' is a constant that have a different
value each time you call it. In your case, the rule must use then constant 'nextval',
which increments the actual sequence on each call.
Either this is a bug... or a feature...
I don't see any genral workaround here. Maybe there is another way of retreiving the
actual inserted data (other than NEW.id)
Yours,
Nicolas Huillard
G.H.S
Directeur Technique
Tél : +33 1 43 21 16 66
Fax : +33 1 56 54 02 18
mailto:[EMAIL PROTECTED]
http://www.ghs.fr
-Message d'origine-
De: Vladimir V. Zolotych [SMTP:[EMAIL PROTECTED]]
Date: lundi 8 mai 2000 18:00
À: [EMAIL PROTECTED]
Objet: [ADMIN] rules problem
Hello all,
Encountered the problem with using RULEs. Cannot log
(e.g. write some info about insertions into sepearate table)
insertions properly. Detailed description (not long or sophisticated)
follows:
I do:
1) CREATE TABLE colors (id SERIAL, color TEXT);
2) Create table for log info:
CREATE TABLE colors_log (color_id INT4, color TEXT);
3) Create RULE that actually makes log:
CREATE RULE log_color
AS ON INSERT
TO colors
DO INSERT INTO colors_log VALUES (NEW.id, NEW.color);
4) Make some insertions:
INSERT INTO colors (color) VALUES ('red');
The same for 'green', 'blue'.
5) SELECT * FROM colors;
id|color
--+-
2|red
4|green
6|blue
Here appears the first question:
why 'id' is 2, 4, 6, not 1, 2, 3?
7) SELECT * FROM colors_log;
color_id|color
+-
1|red
3|green
5|blue
The problem is: the 'id's differ. E.g.,
In colors_log table the saved 'id' are wrong.
Thanks!
--
Vladimir Zolotych [EMAIL PROTECTED]
