From: Andrew Hammond <andrew.george.hammond@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
Date: 18 Jul 2006 09:07:08 -0700
Message-ID: <1153238828.426611.113540@35g2000cwc.googlegroups.com> (raw)
I have a client with the following EAV inspired schema.
CREATE TABLE many_tables (
table_id text primary key, -- defines which virtual table is
encoded
attribute1 text,
attribute2 text,
attribute3 text,
attribute4 text,
...
);
I'd like to use a mix of constraint based paritioning, rules
_and_views_ to implement a real schema underneath this mess, like the
following.
CREATE TABLE cat (
cat_id INTEGER PRIMARY KEY,
cat_name TEXT NOT NULL,
aloofness NUMERIC(1,3) DEFAULT 1.0 CHECK (0.0 <= aloofness AND
aloofness <= 1.0)
);
CREATE RULE many_tables_cat_insert AS
ON INSERT TO many_tables WHERE table_id = 'cat' DO INSTEAD
INSERT INTO cat (cat_id, cat_name, aloofness) VALUES (
CAST(attribute1 AS integer),
attribute2,
CAST(attribute3 AS numeric(1,3))
-- gleefully ignore the other attributes
);
-- etc for UPDATE, and DELETE rules
-- This part doesn't work
CREATE VIEW many_tables_a (CHECK (table_id = 'cat')) INHERITS
(many_tables) AS
SELECT 'cat' AS table_id,
CAST(cat_id AS text) AS attribute1,
cat_name AS attribute2,
CAST(aloofness AS text) AS attribute3,
null AS attribute4, ...
FROM cat;
So, I guess I'm stuck doing the UNION ALL approach in this instance.
This won't get me the partitioning win, nor the flexibility and
cleanliness of design that I'd get with inheritance.
As far as I can see, supporting the above would it mean adding
inheritance and constraint support to views. Does anyone have a better
approach?
Drew
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew.george.hammond@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
In-Reply-To: <1153238828.426611.113540@35g2000cwc.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox