Re: exploiting features of pg to obtain polymorphism

On Mon, Oct 16, 2006 at 11:41:25AM +0200, Ivan Sergio Borgonovo wrote:

> > You can put a unique constraint and a serial default on the
> > parent table (such as a primary key). Insertion on a child
> > table will fail if the key in question already exists in the
> > base table. It may have come from another child table.
>
> Is it really true?
> http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
True enough. I just tried it against 7.4.13.

However, it works for us because

a) we don't need unique parent table fields apart from the
parent table pk

b) we never insert *explicitely* into the primary key field,
neither via the base table nor via any child tables

To make this safe we should probably put triggers onto the
tables to make sure the pk isn't alter (IOW set it to
DEFAULT in a BEFORE INSERT/UPDATE trigger).

> > Updating the base table updates all the relevant child
> > tables, too. Delete will extend from base to child tables,
> > too. That way I'll have a unique serial across all the child
> > tables. I just need to take care to not use ONLY on
> > update/delete on the base table or to INSERT into the base
> > table directly (the latter isn't really harmful to the
> > issue, however).
>
> It would be nice if at least delete fired by triggers on the parent worked.
> But it doesn't since rows inserted in children don't get inserted in parents (that's OK on a OO perspective).
They do get inserted into the parent. But actions on the
child tables do not fire parent table triggers :-(

> > > Audit tables have their own pk/fk relationships and their
> > > triggers but according to my knowledge they won't be considered
> > > unless you operate on those table directly. If you operate on the
> > > data tables those triggers pk/fk won't be seen.
> > True. But I still get the unique pks since I don't operate
> > on them directly. Eventually, PG will enforce those
> > constraints, too.
>
> You get a serial in children, not uniqueness.
I do but only because I never change the PKs explicitely,
not (yet) because PG enforces it.

Establishing use cases may over time contribute to raising
inheritance improvements further up the TODO list in terms
of priorities.

> > > even if I've the suspect the code is not complete enough
> > > to implement the features
> > Yes. Eventually it is going to be something like Veil. Or
> > rather, I suppose it will *be* (as in use) Veil.
>
> I didn't understand. Are you referring to this?
> http://veil.projects.postgresql.org/curdocs/index.html
Yes. And, BTW, it got nothing much to do with inheritance
:-) But it could, thinking that tables might inherit from
a Veil-enabled parent table or some such.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346