constraints, inheritance and "_RETURN" ON SELECT rules - Mailing list pgsql-hackers

I posted about this a couple of days ago, but the post was not
complete. Trying again:

-- suppose the following table exists
CREATE TABLE many_tables (   table_id text,  -- defines which virtual table encoded   att0 text,   att1 text,   att2
text,  att3 text
 
);
-- with some example data
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '1', 'fudge', '0.08', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '2', 'pepper', '0.44', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('dog', '1', 'morgan', 'Golden Retriever', '7');
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('bird', '1', 'boo', 'a grumpy parrot', 'Grey');

-- Goal:
-- 1) Allow incremental application migration to a relational schema
design.
-- 2) Improve performance, even for existing applications.
--
-- Method:
-- 1) Migrate the data out of many_tables into relational tables which
have
--    appropriate data-types, constraints etc.
-- 2) Place rules on many_tables to support DML.
-- 3) Use inheritance + constraint to create separate child tables.
-- 4) Define "_RETURN" ON SELECT rule to viewify each child table with
data
--    from the appropriate relational table.

CREATE TABLE cat (   cat_id INTEGER PRIMARY KEY     -- was att0 , cat_name TEXT NOT NULL         -- was att1 ,
aloofnessNUMERIC(4,3)         -- was att2        DEFAULT 1.0        CHECK (0.0 <= aloofness AND aloofness <= 1.0)
 
);

BEGIN;
INSERT INTO cat (cat_id, cat_name, aloofness)
SELECT CAST(att0 AS integer), att1, CAST(att2 AS numeric(4,3))
FROM many_tables WHERE table_id = 'cat';
DELETE FROM many_tables WHERE table_id = 'cat';

CREATE OR REPLACE RULE many_tables_cat_insert AS
ON INSERT TO many_tables
WHERE NEW.table_id = 'cat'
DO INSTEAD
INSERT INTO cat (cat_id, cat_name, aloofness)
VALUES ( CAST(NEW.att0 AS integer)      , NEW.att1      , CAST(NEW.att2 AS numeric(1,3))    -- gleefully ignore the
otherattributes
 
);

CREATE OR REPLACE RULE many_tables_cat_update AS
ON UPDATE TO many_tables
WHERE OLD.table_id = 'cat' AND NEW.table_id = 'cat'
DO INSTEAD
UPDATE cat
SET cat_id = CAST(NEW.att0 AS integer) , cat_name = NEW.att1 , aloofness = CAST(NEW.att2 AS numeric(1,3))
WHERE cat_id = CAST(OLD.att0 AS integer);

CREATE OR REPLACE RULE many_tables_cat_delete AS
ON DELETE TO many_tables
WHERE OLD.table_id = 'cat'
DO INSTEAD
DELETE FROM cat
WHERE cat_id = CAST(OLD.att0 AS integer);

CREATE TABLE many_tables_cat (CHECK (table_id = 'cat')) INHERITS
(many_tables);

CREATE OR REPLACE RULE "_RETURN" AS
ON SELECT TO many_tables_cat DO INSTEAD
SELECT CAST('cat' AS text)     AS table_id    , CAST(cat_id AS text)    AS att0    , cat_name                AS att1
,CAST(aloofness AS text) AS att2    , CAST(null AS text)      AS att3
 
FROM cat;

COMMIT;

-- test
SELECT * FROM cat; --ok
SELECT * FROM many_tables; -- oops!


ahammond=# CREATE TABLE many_tables_cat (CHECK (table_id = 'cat'))
INHERITS (many_tables);
CREATE TABLE
ahammond=# \d many_tables_cat
Table "public.many_tables_cat" Column  | Type | Modifiers
----------+------+-----------table_id | text |att0     | text |att1     | text |att2     | text |att3     | text |
Check constraints:   "many_tables_cat_table_id_check" CHECK (table_id = 'cat'::text)
Inherits: many_tables

ahammond=# CREATE OR REPLACE RULE "_RETURN" AS
ahammond-# ON SELECT TO many_tables_cat DO INSTEAD
ahammond-# SELECT CAST('cat' AS text)     AS table_id
ahammond-#      , CAST(cat_id AS text)    AS att0
ahammond-#      , cat_name                AS att1
ahammond-#      , CAST(aloofness AS text) AS att2
ahammond-#      , CAST(null AS text)      AS att3
ahammond-# FROM cat;
CREATE RULE
ahammond=# \d many_tables_cat
View "public.many_tables_cat" Column  | Type | Modifiers
----------+------+-----------table_id | text |att0     | text |att1     | text |att2     | text |att3     | text |
View definition:SELECT 'cat'::text AS table_id, cat.cat_id::text AS att0, cat.cat_name
AS att1, cat.aloofness::text AS att2, NULL::text AS att3  FROM cat;

ahammond=# SELECT * FROM many_tables;
ERROR:  could not open relation 1663/16385/209728: No such file or
directory

Ideally, I think this should work as expected. I don't know for sure
how the constraint should fit into things, but I suspect that it should
remain and become more of an assertion. You're kind of stuck trusting
the DBA if the constraint refers to a column which doesn't even exist
in the source of the view.

Either that, or viewifying inherited tables should fail. Which is
probably the correct behaviour if the above can't reasonably be
supported.

Drew