Re: Referential Integrity problem

On 19 Mar 2003, James Gregory wrote:

On Wed, 2003-03-19 at 00:08, Stephan Szabo wrote:

On 19 Mar 2003, James Gregory wrote:

I hope this one is just some misunderstanding on my part.

Referential integrity constraints currently apply only to the explicitly
named table. In addition, the saleable_item primary key on id is not
inherited by product (and so there can be duplicates in product - even if
you put a unique constraint on product(id), you still can have duplicates
between saleable_item and product).

Ar. Is there a way to do what I need to do? No insertions should ever
occur in the "supertable" - is the best way forward to write a trigger
that just tests if the id exists in the supertable? With this assertion
that no inserts will occur in the supertable, is it sufficient to
qualify my references to say saleable_item.id?

No, because the triggers still are only going to reference explicitly
saleable_item. I believe there's a somewhat complicated work-around using
a new table that contains just ids that is referenced by saleable_item,
product and chart_item with triggers for dealing with changes to
saleable_item and product.

This behaviour seems inconsistent (to me anyway). Is it likely to
change? Why isn't the primary key inherited?

Am I correct then in my understanding that postgres's inheritance is
merely a table templating system rather than inheritance, per se? - that
is, it seems to me that if it were inheritance that storing a list of
saleable_items and filling it with products should be entirely
equivalent to storing a list of products. Why is this not so?

Inheritance needs alot of work. There have been various discussions in
the past (see -general and -hackers archives), but I don't believe that a
plan that satisfied everyone was ever reached.

On 19 Mar 2003, James Gregory wrote:

Ok, so, it seems to me that what I need to do is create trigger
functions to implement this check. They will basically consist of a
check to maintain referential integrity with this inherited structure,
and throw an exception if the proposed modification would break that.
Then, assuming all the checks passed and no exception was thrown it
would simply return the new row.

The script sounds like a bad idea, just from a maintenance standpoint.
So, is there a way to infer what I need to know from within a trigger?
Alternatively, can I specify the table name as a parameter, and use that
to do the system table queries?

You can give arguments at CREATE TRIGGER time and do stuff based on the
arguments from there. You'll need to be careful to make sure all of the
concurrency and modification of the pk side of the constraint works
properly.

As a note, you'll need triggers on saleable_item and product to deal with
modifications to the pk-side of the constraint. The one on saleable_item
isn't strictly necessary since you don't plan to modify it, but it's
probably good from a consistancy stanpoint. You'll also need triggers on
any other saleable_item subclasses.

On Wed, 2003-03-19 at 00:08, Stephan Szabo wrote:

On 19 Mar 2003, James Gregory wrote:

I hope this one is just some misunderstanding on my part.

Referential integrity constraints currently apply only to the explicitly
named table. In addition, the saleable_item primary key on id is not
inherited by product (and so there can be duplicates in product - even if
you put a unique constraint on product(id), you still can have duplicates
between saleable_item and product).

Ar. Is there a way to do what I need to do? No insertions should ever
occur in the "supertable" - is the best way forward to write a trigger
that just tests if the id exists in the supertable? With this assertion
that no inserts will occur in the supertable, is it sufficient to
qualify my references to say saleable_item.id?

This behaviour seems inconsistent (to me anyway). Is it likely to
change? Why isn't the primary key inherited?

Am I correct then in my understanding that postgres's inheritance is
merely a table templating system rather than inheritance, per se? - that
is, it seems to me that if it were inheritance that storing a list of
saleable_items and filling it with products should be entirely
equivalent to storing a list of products. Why is this not so?

Thanks for your help,

James.

On Wed, 2003-03-19 at 00:47, Stephan Szabo wrote:

On 19 Mar 2003, James Gregory wrote:

On Wed, 2003-03-19 at 00:08, Stephan Szabo wrote:

On 19 Mar 2003, James Gregory wrote:

I hope this one is just some misunderstanding on my part.

Referential integrity constraints currently apply only to the explicitly
named table. In addition, the saleable_item primary key on id is not
inherited by product (and so there can be duplicates in product - even if
you put a unique constraint on product(id), you still can have duplicates
between saleable_item and product).

Ar. Is there a way to do what I need to do? No insertions should ever
occur in the "supertable" - is the best way forward to write a trigger
that just tests if the id exists in the supertable? With this assertion
that no inserts will occur in the supertable, is it sufficient to
qualify my references to say saleable_item.id?

No, because the triggers still are only going to reference explicitly
saleable_item. I believe there's a somewhat complicated work-around using
a new table that contains just ids that is referenced by saleable_item,
product and chart_item with triggers for dealing with changes to
saleable_item and product.

Ok, so, it seems to me that what I need to do is create trigger
functions to implement this check. They will basically consist of a
check to maintain referential integrity with this inherited structure,
and throw an exception if the proposed modification would break that.
Then, assuming all the checks passed and no exception was thrown it
would simply return the new row.

So I would need one trigger for inserts and updates, and another for
deletes.

So at this point I have two options - write a script to write a
different trigger function for each inherited table, or, use some
postgres magic in this trigger function to infer what table the new
record is intended for, and then query the system tables to do the right
thing.

The script sounds like a bad idea, just from a maintenance standpoint.
So, is there a way to infer what I need to know from within a trigger?
Alternatively, can I specify the table name as a parameter, and use that
to do the system table queries?

Thanks,

James.