pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
6+ messages / 3 participants
[nested] [flat]
* using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
@ 2006-07-18 16:07 Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 20:44 ` Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Hammond @ 2006-07-18 16:07 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
2006-07-18 16:07 using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
@ 2006-07-18 20:44 ` Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 21:20 ` Re: [SQL] using constraint based paritioning to fix EAV Hannu Krosing <hannu@skype.net>
2006-07-19 07:45 ` Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>
0 siblings, 2 replies; 6+ messages in thread
From: Andrew Hammond @ 2006-07-18 20:44 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] using constraint based paritioning to fix EAV
2006-07-18 16:07 using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 20:44 ` Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
@ 2006-07-18 21:20 ` Hannu Krosing <hannu@skype.net>
2006-07-18 22:00 ` Re: using constraint based paritioning to fix EAV Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 22:06 ` Re: [SQL] using constraint based paritioning to fix EAV Hannu Krosing <hannu@skype.net>
1 sibling, 2 replies; 6+ messages in thread
From: Hannu Krosing @ 2006-07-18 21:20 UTC (permalink / raw)
To: Andrew Hammond <andrew.george.hammond@gmail.com>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-hackers@postgresql.org
Ühel kenal päeval, T, 2006-07-18 kell 16:44, kirjutas Andrew Hammond:
> 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,
> ...
> );
Maybe you can approach the problem from another end, and make the
many_tables table the virtual one and all the others into real tables ?
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: using constraint based paritioning to fix EAV
2006-07-18 16:07 using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 20:44 ` Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 21:20 ` Re: [SQL] using constraint based paritioning to fix EAV Hannu Krosing <hannu@skype.net>
@ 2006-07-18 22:00 ` Andrew Hammond <andrew.george.hammond@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andrew Hammond @ 2006-07-18 22:00 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Hannu Krosing wrote:
> Ühel kenal päeval, T, 2006-07-18 kell 16:44, kirjutas Andrew Hammond:
> > 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,
> > ...
> > );
>
> Maybe you can approach the problem from another end, and make the
> many_tables table the virtual one and all the others into real tables ?
The many_tables table already exists. It works about as poorly as you'd
expect. My goal is to migrate away from this attrocious design. My
question is how best to do that. I'd like to take advantage of table
partitioning and constraint based exclusion, but I can't figure out how
to do it since the underlying tables are heterogenous in nature.
Perhaps I'm going about this the wrong way though. I think I'll
partition based on table_id and leave the native storage in the crappy
text fields. Then create views of what should be the real, relational
tables. That lets the developers migrate and (I hope) eases the
performance burden somewhat. Once the code migration is complete I can
finalize the data move.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] using constraint based paritioning to fix EAV
2006-07-18 16:07 using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 20:44 ` Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 21:20 ` Re: [SQL] using constraint based paritioning to fix EAV Hannu Krosing <hannu@skype.net>
@ 2006-07-18 22:06 ` Hannu Krosing <hannu@skype.net>
1 sibling, 0 replies; 6+ messages in thread
From: Hannu Krosing @ 2006-07-18 22:06 UTC (permalink / raw)
To: Andrew Hammond <andrew.george.hammond@gmail.com>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-hackers@postgresql.org
Ühel kenal päeval, K, 2006-07-19 kell 00:20, kirjutas Hannu Krosing:
> Ühel kenal päeval, T, 2006-07-18 kell 16:44, kirjutas Andrew Hammond:
> > 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,
> > ...
> > );
>
> Maybe you can approach the problem from another end, and make the
> many_tables table the virtual one and all the others into real tables ?
Oops, I see you kind of are :)
Maybe you can solve some of the problems doing a VIEW over pl/SQL
function ?
And/or maybe using triggers instead of rules.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
2006-07-18 16:07 using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 20:44 ` Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
@ 2006-07-19 07:45 ` Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>
1 sibling, 0 replies; 6+ messages in thread
From: Zeugswetter Andreas DCP SD @ 2006-07-19 07:45 UTC (permalink / raw)
To: Andrew Hammond <andrew.george.hammond@gmail.com>; Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-hackers@postgresql.org
> 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.
If you do your own rules anyway, why can't you use inheritance and
create the rules
on the parent table and the constraints on the child tables ?
You can still use the child tables directly if you want.
Andreas
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-07-19 07:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-18 16:07 using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints) Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 20:44 ` Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 21:20 ` Re: [SQL] using constraint based paritioning to fix EAV Hannu Krosing <hannu@skype.net>
2006-07-18 22:00 ` Re: using constraint based paritioning to fix EAV Andrew Hammond <andrew.george.hammond@gmail.com>
2006-07-18 22:06 ` Re: [SQL] using constraint based paritioning to fix EAV Hannu Krosing <hannu@skype.net>
2006-07-19 07:45 ` Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>
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