From: Andrew Hammond <andrew.george.hammond@gmail.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
Date: Tue, 18 Jul 2006 16:44:28 -0400
Message-ID: <5a0a9d6f0607181344g530b4d09s393f3f0b46764fbe@mail.gmail.com> (raw)
In-Reply-To: <bf05e51c0607181131s488e93bane69a37001ab0912@mail.gmail.com>
References: <1153238828.426611.113540@35g2000cwc.googlegroups.com>
<bf05e51c0607181131s488e93bane69a37001ab0912@mail.gmail.com>
On 7/18/06, Aaron Bono <postgresql@aranya.com> wrote:
>
> On 18 Jul 2006 09:07:08 -0700, Andrew Hammond <
> andrew.george.hammond@gmail.com> wrote:
>
> > 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?
>
>
>
> If you don't mind having redundant data, you could change the ON INSERT
> trigger to copy the data into cat, add an ON UPDATE trigger (you should do
> this anyway) and revoke direct insert/update to cat. Then you don't need
> many_tables_a or a UNION.
>
There's already a performance problem, I suspect that would just exacerbate
it. Since I want to encourage developers to use the relational tables
instead of the many_tables table, refusing DML wouldn't be a good idea.
Of course I don't know if this achieves your intended result or not. What
> is your business requirement for this?
>
The current virtual table design has performance (as well as maitenance)
issues. Performance tuning is problematic. A major re-design of the
application is not something that can be done until the next major release.
However, if I can slide a proper relational schema underneath this
hodge-podge table while retaining compatability for legacy apps then it
becomes possible to fix parts of the application to use the relational
tables incrementally on an as-needed basis.
If I could get constraint based exclusioning to work with the partitioning,
then I would be able to realize performance improvements immediately (which
is always good for a consultant). Unfortunately I don't see any way to do
this. Inheritance seems to fit backwards from what I'm actually trying to
do.
I've seen a few EAV designs in practice. They've all been problematic. I'd
like to have a better way of dealing with them. Which is why I'm tentatively
suggesting support for inheritance and constraints in views. If there's some
other way to achieve constraint based exclusion across a UNION of
heterogenous tables, I'd love to hear it.
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, postgresql@aranya.com, pgsql-hackers@postgresql.org
Subject: Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
In-Reply-To: <5a0a9d6f0607181344g530b4d09s393f3f0b46764fbe@mail.gmail.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