Tom Lane wrote:
...
I just ran into another inheritance-related oddness. Well maybe it is not really an oddness -- you tell me.
The problem stems from the fact that I did not originally plan on using inhertiance and so did not include the ONLY keyword in the FROM clause of queries coded into my user interface application. To get around having to modify lots of queries in the application so as to include ONLY, I instead switched the configuration parameter SQL_INHERITANCE to OFF. This works fine for cases where I select from tables directly, i.e., the query correctly returns only the rows from the parent table.
However, when I do a select from a view, which itself does a select from a parent table, the query result does include the child table rows, i.e., the SQL_INHERITANCE setting is ignored in this situation. Should the SQL_INHERITANCE setting still rule?
TEST.SQL:
\set ON_ERROR_STOP ON \connect - postgres
--DROP DATABASE test; CREATE DATABASE test WITH TEMPLATE = template1;
\connect test postgres
SET search_path = public, pg_catalog;
CREATE TABLE person ( person_pk serial NOT NULL, last_name character varying(24), first_name character varying(24), CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL))) ) WITHOUT OIDS;
CREATE TABLE person_change_history ( "action" character varying(6), update_date timestamp without time zone DEFAULT now() NOT NULL, update_user name DEFAULT "current_user"() NOT NULL )INHERITS (person) WITHOUT OIDS;
CREATE OR REPLACE RULE person_ru AS ON UPDATE TO person DO INSERT INTO person_change_history SELECT *, 'UPDATE' FROM ONLY person WHERE (person.person_pk = old.person_pk );
/* My views were originally created with the default SQL_INHERITANCE setting, which results in PG not automagically inserting the ONLY keyword. */ SET SQL_INHERITANCE TO ON;
CREATE VIEW persons AS SELECT * FROM person;
/*
I set it to OFF so that I do not have to go back and do major modifications to the application.
*/
SET SQL_INHERITANCE TO OFF;
INSERT INTO person (first_name, last_name) VALUES ('Doug', 'Funny');
INSERT INTO person (first_name, last_name) VALUES ('Patty', 'Mayonaise');SELECT * FROM person;
/*
person_pk | last_name | first_name -----------+-----------+------------
1 | Funny | Doug
2 | Mayonaise | Patty
(2 rows)
*/
SELECT * FROM person_change_history;
/*
person_pk | last_name | first_name | action | update_date | update_user -----------+-----------+------------+--------+-------------+-------------
(0 rows)
*/
SELECT * FROM persons;
/*
person_pk | last_name | first_name -----------+-----------+------------
1 | Funny | Doug
2 | Mayonaise | Patty
(2 rows)
*/
-- A.O.K. so far.
UPDATE person SET last_name = 'Mayonnaise' WHERE last_name = 'Mayonaise'; /* UPDATE 1 */
SELECT * FROM person;
/*
person_pk | last_name | first_name -----------+------------+------------
1 | Funny | Doug
2 | Mayonnaise | Patty
(2 rows)
*/
-- Still O.K.
SELECT * FROM person_change_history;
/*
person_pk | last_name | first_name | action | update_date | update_user -----------+-----------+------------+--------+---------------------------+-------------
2 | Mayonaise | Patty | UPDATE | 2005-05-20 17:10:53.81593 | postgres
(1 row)
*/
-- Still O.K.
SELECT * FROM persons;
/*
person_pk | last_name | first_name -----------+------------+------------
1 | Funny | Doug
2 | Mayonnaise | Patty
2 | Mayonaise | Patty
(3 rows)
*/
--Zing...ouch!
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
