X-Original-To: pgsql-hackers-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 70FA99F95E3 for ; Tue, 18 Jul 2006 17:44:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 88976-06 for ; Tue, 18 Jul 2006 20:44:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id A3F4B9F94F3 for ; Tue, 18 Jul 2006 17:44:29 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id c29so28854nfb for ; Tue, 18 Jul 2006 13:44:28 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=DdPgzNOW/aAv0bD79qdUTAfBoWSdD61xAh+WHBWZN+Fy73dnjXJXBCPnGXblfW2xsCBdGlL2nZuTLnFS2pbE9lSmMKG7YtRQ8fmShGUh6TCT9lEpaCB/ha8ESMc8LgWAlTiQLJ/tgrhq7XD62HOyZkofojnVZuCrPhYqZc17V6k= Received: by 10.78.175.14 with SMTP id x14mr1748760hue; Tue, 18 Jul 2006 13:44:28 -0700 (PDT) Received: by 10.78.157.11 with HTTP; Tue, 18 Jul 2006 13:44:28 -0700 (PDT) Message-ID: <5a0a9d6f0607181344g530b4d09s393f3f0b46764fbe@mail.gmail.com> Date: Tue, 18 Jul 2006 16:44:28 -0400 From: "Andrew Hammond" To: "Aaron Bono" Subject: Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Cc: pgsql-hackers@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_39847_19926516.1153255468032" References: <1153238828.426611.113540@35g2000cwc.googlegroups.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/727 X-Sequence-Number: 86582 ------=_Part_39847_19926516.1153255468032 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/18/06, Aaron Bono 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 ------=_Part_39847_19926516.1153255468032 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_39847_19926516.1153255468032--