Обсуждение: exploiting features of pg to obtain polymorphism maintaining ref. integrity

Is there any good documentation, example, tutorial, pamphlet, discussion... to exploit pg features to obtain
"polymorphic"behavior without renouncing to referential integrity? 

Inheritance seems *just* promising.

Any methodical a approach to the problem in pg context?

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

> Is there any good documentation, example, tutorial, pamphlet, discussion... to exploit pg
> features to obtain "polymorphic" behavior without renouncing to referential integrity?
>
> Inheritance seems *just* promising.
>
> Any methodical a approach to the problem in pg context?

I don't know if this is what you are after, but is was a VERY interesting discussion that sounds
similar to what your are looking for?

http://archives.postgresql.org/pgsql-sql/2006-05/msg00179.php
http://archives.postgresql.org/pgsql-general/2006-05/msg01125.php

basically the OP, wanted to create a template/parent schema.

next he would create a new schema for each client and then create inherited tables from the parent
schema.

Then he would create views/fuctions that would work on the tables in the current schema.

Last he would set the search_path to the schema of any client of enterest and use the default
views/funtions of the newly set search_path of currently set schema.


One advantage (possible the polymorphism you were looking for) with this design was that any
modifications made to the template/parent schema would cascade to all of the client schemas as a
feature of table inheritance.  And he could also summarize all of the data from all clients by
selecting from the template/parent schema.

Regards,

Richard Broersma Jr.

On Fri, 6 Oct 2006 18:12:22 -0700 (PDT)
Richard Broersma Jr <rabroersma@yahoo.com> wrote:

> > Is there any good documentation, example, tutorial, pamphlet,
> > discussion... to exploit pg features to obtain "polymorphic"
> > behavior without renouncing to referential integrity?
> >
> > Inheritance seems *just* promising.
> >
> > Any methodical a approach to the problem in pg context?
>
> I don't know if this is what you are after, but is was a VERY
> interesting discussion that sounds similar to what your are looking
> for?
>
> http://archives.postgresql.org/pgsql-sql/2006-05/msg00179.php
> http://archives.postgresql.org/pgsql-general/2006-05/msg01125.php

I read it. I even kept a copy in my imap. I didn't look at it as an example of polymorphism. I'll see if I can find
differentuses of it. 

What I was looking at was eg. list of slightly different nature but with same items.

eg.
List -> Items
List -> ListGroup -> Items

create table List (
  idList integer
,  otherListstuff [...]
);

create table ListGroup (
  idListGroup integer
, idList integer
);

create table Items (
  idItem integer
, idGeneralizedList integer
);

Each List may contain many ListGroup or many Items. I can do it... I'll do it at the cost of loosing ref. integrity.

Inheritance seems the place to look at to solve this kind of problem, but pg inheritance support is not the one I'm
usedto deal with in C++ for example. 

1) I can't have virtual tables (or I didn't find the way to have them)
This have the side effect of "unexpected" behavior when you fill child/parent because there is no distinction between
declarationand instantiation. 
2) pk/pk triggers etc. aren't inherited

Anyway inheritance continue to look like a good place to start from.

I'm trying to keep all the data coherence tasks in the db.

The sql I'm writing is not "static" or in my view it is not the "final" one but rather a "definition" of the final one.
So I want to be able to define inside my sql in the most natural way my coherency requirement.

I'm already storing metadata about tables in other tables so that at db design people will be able to specify these
"extra"coherency information. 
At this stage the sql is actually the one that will go into the db.
These metadata are used to build up stored procedures that will take care of garbage collection for example or to
generatetriggers to keep data consistent. 
I'm wondering if it may be a good idea to have a pre-processor to overcome the missing behavior of pg inheritance (eg.
automaticallycreate the missing pk/fk/triggers in the children, making the parent "private" so to simulate virtual
parents);but it looks enough complicate to overweight the advantages of reaching the target. 

So... you let me see that schema example under a new light and I'll think if I can exploit it for my tasks.
I wrote "exploiting [unnamed] features" cos I still don't know pg enough and cos I was hoping the list came up with
somethingcreative as the use of schema that maybe wouldn't come up if I explicit mention "inheritance". 

I saw this too, more on the track of what I was looking for, but it wasn't inspirational as I hoped:

http://www.varlena.com/varlena/GeneralBits/98.php



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

On Fri, Oct 06, 2006 at 11:09:29PM +0200, Ivan Sergio Borgonovo wrote:

> Is there any good documentation, example, tutorial, pamphlet, discussion... to exploit pg features to obtain
"polymorphic"behavior without renouncing to referential integrity? 

In GNUmed we use it to aggregate text fields over a
range of child tables and for auditing:

 http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/?root=gnumed

look at gmAudit*.sql and gmclinical.sql

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

On Sun, 8 Oct 2006 14:09:53 +0200
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> On Fri, Oct 06, 2006 at 11:09:29PM +0200, Ivan Sergio Borgonovo
> wrote:
>
> > Is there any good documentation, example, tutorial, pamphlet,
> > discussion... to exploit pg features to obtain "polymorphic"
> > behavior without renouncing to referential integrity?
>
> In GNUmed we use it to aggregate text fields over a
> range of child tables and for auditing:

Really interesting, nice sql to see, including the use of "metadata" and functions. It looks more like what I was
lookingfor. 
I'm reading the developers doc to get a clearer picture.

Are there other places from where I could learn those/similar techniques? (or any other place I could learn those
techniqueswithout having to "reverse engineer" gnumed). 

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

Resending since it definitively seems it has been lost in outer spaces.
Sorry for duplicates if any.

On Sun, 8 Oct 2006 14:09:53 +0200
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> On Fri, Oct 06, 2006 at 11:09:29PM +0200, Ivan Sergio Borgonovo
> wrote:
>
> > Is there any good documentation, example, tutorial, pamphlet,
> > discussion... to exploit pg features to obtain "polymorphic"
> > behavior without renouncing to referential integrity?
>
> In GNUmed we use it to aggregate text fields over a
> range of child tables and for auditing:
>
>  http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/?root=gnumed
>
> look at gmAudit*.sql and gmclinical.sql

I finally got the time to look at the files you suggested.
They helped me to realize that if I put pk/fk in the children I won't have inheritance problems on pk/fk cos they will
bepreserved. As stupid as this observation may seem, at least it tells me what I can do. 

Anyway it doesn't solve the problem of having lists that can contain different elements with same parent and maintain
ref.integrity. 

Now back to gm code.

I see you've data tables with their pk/fk relations and triggers in one schema that inherit from audit tables in
another.

You've a function that helps to put tables that have to be audited in another table, nothing special compared with an
insertwith the exception of some extra control on input. 

Audit tables have their own pk/fk relationships and their triggers but according to my knowledge they won't be
consideredunless you operate on those table directly. 
If you operate on the data tables those triggers pk/fk won't be seen.

Considering you forbid direct insert, update and delete on those tables, while pk/fk etc... are still a security net it
seemsthat those relationship will never be really used. 

Later on you grant the same operations to gm-doctors. This further puzzle me even if I've the suspect the code is not
completeenough to implement the features I'm expecting from the framework I see. 

Finally I read:
comment on table audit.audited_tables is
    'All tables that need standard auditing must be
     recorded in this table. Audit triggers will be
     generated automatically for all tables recorded
     here.';

But I can't see anything doing this.

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
ideahow to implement it. The use of metadata. But definitively I can't see polymorphism in your use of inheritance. 

One way I could eg. make lists that can contain elements of different kind with same parent could be to have a simple
methodto add the missing (not inherited) ref. integrity triggers on children. 

Specifying pk/fk again on inherited fields of children seems to come with a lot of trouble and still will put me in the
samecondition of not having polymorphism (list of elements with common parent). Furthermore this won't solve the
problemthat declaration=instantiation in sql 
While inherits seems syntax sugar and maybe a way to avoid to add metadata to feed to a stored procedure I'll have to
protectthe parent class from being directly accessed (since most of the time the behavior of parents is unwelcome). 
So the only structure there is in pg that looks OO seems not used to support polymorphism in the way I'm interested, it
seemsmainly a way to avoid typing over and over the definition of some columns and to group tables. 

Actually if I could use inherits generated metadata (pg_inherits and Co.) that will come at the cost of having
instantiatedparent tables with their "real" relation with children. 

But there is no simpler syntax than defining a table to *define a table*, and there is no way I know to define virtual
tables.

Supposing I was able to create virtual parents I could add metadata to children, feed those metadata to sp to create
triggersto maintain ref. integrity in lists that contain different elements with same parent. 

Otherwise I'll have to have an external parser that start from pseudo sql where I can specify if a table is a virtual
tableetc... or put virtual table definitions inside tables to be fed to a sp... but this definitively looks overkill. 

The example here while suggestive
http://www.varlena.com/varlena/GeneralBits/98.php
doesn't seem to have any relation with OO and the second sentence explain it all:
"In the PostgreSQL implementation, this is not true object inheritance."

Any second chance to find an OO use of inherits, cos this seems the only OO construct of pg.

Don't take this as a sparkle of a flame war (we already had 2 very long one in few weeks) on how pg is /under .*/
etc...I'm not aware of any usable/mature rdbms with reasonable OO features. 


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

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

> Now back to gm code.
>
> I see you've data tables with their pk/fk relations and triggers in one schema that inherit from audit tables in
another.
Yes.

> You've a function that helps to put tables that have to be audited in another table, nothing special compared with an
insertwith the exception of some extra control on input. 
Yes.

> Audit tables have their own pk/fk relationships and their triggers but according to my knowledge they won't be
consideredunless 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.

> Considering you forbid direct insert, update and delete on those tables, while pk/fk etc... are still a security net
itseems that those relationship will never be really used. 
True as of today.

> Later on you grant the same operations to gm-doctors. This further puzzle me
Well, all objects are owned by "gm-dbo". Our bootstrapper
does that. So I need to grant access rights to some people.
Namely those in the group gm-doctors.

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

> Finally I read:
> comment on table audit.audited_tables is
>     'All tables that need standard auditing must be
>      recorded in this table. Audit triggers will be
>      generated automatically for all tables recorded
>      here.';
>
> But I can't see anything doing this.
gmAuditSchemaGenerator.py in server/bootstrap/

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

> Any second chance to find an OO use of inherits,
Not that I know.

> cos this seems the only OO construct of pg.
Surely not. SPs can be overloaded. Datatypes can be
extended.

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

On Fri, 2006-10-06 at 23:09 +0200, Ivan Sergio Borgonovo wrote:
> Is there any good documentation, example, tutorial, pamphlet, discussion... to exploit pg features to obtain
"polymorphic"behavior without renouncing to referential integrity? 
>
> Inheritance seems *just* promising.
>
> Any methodical a approach to the problem in pg context?
>

I'm not sure if this answers your question, but here's how I do
inheritance in the relational model.

Just make a "parent" table that holds a more generic object like:

CREATE TABLE person (name TEXT PRIMARY KEY, age INT, height NUMERIC);

Then a "child" table like:

CREATE TABLE student (name TEXT REFERENCES person(name), gpa NUMERIC);

Every person, student or otherwise has a record in "person". If, and
only if, they are a student they have a record in the "student" table.

To select all people, select only from the "person" table. To select all
students, select from the join of the two tables.

Regards,
    Jeff Davis

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

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