[ADMIN] Rules
Hi Fellows,
I have a table with 55 columns or so, plus one extra column of datatype
geometry, I am using PostGIS.
I intend to split my table into 3 tables. So each may have 25 to 30 columns
each.
I've created a View which relates all 3 tables with their pk and fk
accordingly. This VIEW has a RULE .
*ON INSERT TO public.mytable DO INSTEAD*
I listed 3 ACTIONS in order to insert data into all 3 different tables.
I am having trouble getting the main table´s primary key value. I use the
currval('mytable_sequence_id')
It works fine only if I do not insert multiple records at once. If I do
insert multiple records, the currval gets the last value and not each
inserted value. Hence, all my records in my 2 related tables have foreign
key = to the last primary key.
I guess I am doing something wrong, either I am missing something or my
approach is incorrect.
Do you know how should I accomplish this ? Any ideas ?
Thanks in advanced.
Ricardo
[ADMIN] Rules/Triggers executio order
I have a partitioned table to which I route datausing 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. Thanks in advance.
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
[ADMIN] Rules on a view overwrite default values. Any way to reinstate them?
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! Thanks, Brad ---(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
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
[ADMIN] rules
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 5:19 PM To: Nico De Ranter Cc: postgres-list Subject: Re: [ADMIN] IDENT authentication failed but I'm not using ident Nico De Ranter [EMAIL PROTECTED] writes: \connect: FATAL: IDENT authentication failed for user nico I changed /etc/postgresql/pg_hba.conf so it only contains localall all trust=20 and restarted postgres but I still get the same error message. Any idea why the import complains about IDENT authentication when=20 I'm not even using it and how to get around it? If you're getting that message then you *are* using IDENT auth. My bet is that you changed the wrong config file. /etc/postgresql is not a very standard name for a Postgres data directory ... BTW: is there a way to create a database and then change ownership to somebody else? No, but you can do it in one step. http://www.postgresql.org/docs/7.4/static/sql-createdatabase.html regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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
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
[ADMIN] Rules - Functions - Permissions
Hi, I'm trying create a SECURE TIER in my DB. I need dummys tables that accept INSERTS from any users, then execute a RULE that insert the datas into the REAL table. The REAL tables accept INSERT only from postgres user. This work fine ! But when I need the RULE calls a FUNCTION (plpgsql) for more detailed data manipulating, the FUNCTION runs with the normal user permissions, and not with the RULE permissions. Well, if a RULE calls the FUNCTION, the FUNCTION must run with the same RULEs permissions ... let-me show you. \c - postgres create table teste (codigo int4, nome varchar (30) ); create table teste2 (codigo int4, nome varchar (30) ); grant ALL on teste2 TO joe; create rule teste2 as on insert to teste2 do insert into teste (codigo,nome) values (new.codigo,new.nome); \c - joe insert into teste (codigo,nome) values (1,'tulio'); -- PERMISSION DENIED ! - OK insert into teste2 (codigo,nome) values (1,'tulio'); -- INSERTS - OK select * from teste2; -- SHOW THE ROWS - OK \c - postgres select * from teste; -- SHOW THE ROWS - OK In this example, all is OK, but ... \c - postgres create table teste (codigo int4, nome varchar (30) ); create table teste2 (codigo int4, nome varchar (30) ); grant ALL on teste2 TO joe; create function teste (integer,text) returns integer as ' begin insert into teste (codigo,nome) values ($1,$2); end;' language 'plpgsql'; create rule teste2 as on insert to teste2 do select teste (new.codigo::integer,new.nome::text); \c - joe insert into teste (codigo,nome) values (1,'tulio'); -- PERMISSION DENIED ! - OK insert into teste2 (codigo,nome) values (1,'tulio'); -- PERMISSION DENIED ON TESTE = NOT OK Sorry my English. Do you undestand ?? Could you help-me ? I realy need make HEAVY consistencys, and I need a FUNCTION ... ---(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
[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]
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]
[ADMIN] rules bug
Hello all, Have the problem with using RULEs (is it a RULE's bug?): Do the following: 1) Create table 'num' with column of a type SERIAL, e.g. CREATE TABLE num (id SERIAL, num INT4); 2) Create table 'num_log' (for logging insertions in table 'num'), e.g. CREATE TABLE num_log (num_id INT4, num_val INT4); 3) Create rule 'num_rule' (that makes logging), e.g. CREATE RULE num_rule AS ONINSERT TO num DO INSERT INTO num_log VALUES (NEW.id, NEW.num); 4) Insert into 'num', e.g. INSERT INTO num (num) VALUES (123); 5) SELECT * FROM num; id|num -- 2|123 6) SELECT * FROM num_log; num_id|num_val -- 1| 123 The problem is id != num_id, e.g. the 'id' logged with RULE differs from real 'id' of the inserted (into table 'num') row. Did anybody encountered the same problem, if so how it were solved? Is it a bug? Thanks! -- Vladimir Zolotych [EMAIL PROTECTED]
[ADMIN] Rules and clustering
Hello There, I noticed that if I have rules set on a table and if I do a cluster index-name on the table, it deletes all the rules. Is this normal? I am just wondering as I would like to cluster the data occasionally. I am not sure if this is a bug, or it is supposed to drop all the rules. If the latter is the case, are there any other operations that would drop the rules? Sincerely Yours, Jacques Dimanche VP of Research and Development Tridel Technologies, Inc.
