Re: [HACKERS] Inherited constraints and search paths (was - Mailing list pgsql-general

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!