Re: exploiting features of pg to obtain polymorphism - Mailing list pgsql-general

On Fri, 13 Oct 2006 16:37:42 +0200
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> On Thu, Oct 12, 2006 at 04:40:32PM +0200, Ivan Sergio Borgonovo
> wrote:
>
> > Anyway it doesn't solve the problem of having lists that
> > can contain different elements with same parent and maintain
> > ref. integrity.
> Only to some degree.
>
> 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
"
If we declared cities.name to be UNIQUE or a PRIMARY KEY, this would
not stop the capitals table from having rows with names duplicating
rows in cities. And those duplicate rows would by default show up in
queries from cities. In fact, by default capitals would have no
unique constraint at all, and so could contain multiple rows with the
same name. You could add a unique constraint to capitals, but this
would not prevent duplication compared to cities.
"

> 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).

But while the behaviour seems correct under an OO point of view it looks "unexpected".

What seems to happen using inherits is you're creating *different* tables that share serials.

> > 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 wrote some example code here:
http://www.webthatworks.it/drupal/2006/10/db/postgresql_inheritance_surprises
that shows you don't get any of the above with the exception of serial.
Corrections are welcome.

> > 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

> > There is one point of contact between what I did already
> > and what I would like to do but I still haven't had a good
> > idea how to implement it. The use of metadata. But
> > definitively I can't see polymorphism in your use of
> > inheritance.
> Surely not to the extent a C++ programmer would hope for.

That was my fault.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it