Re: foreign key from array element

On Thu, Sep 20, 2012 at 12:18 AM, Gabriele Bartolini <
Gabriele(dot)Bartolini(at)2ndquadrant(dot)it> wrote:

> Hi Chris,
>
> thank you very much for taking the time to read the article and get
> into the features proposed with our patch.

You are welcome. Also in case there is ambiguity, the feature I was
describing animosity towards was table inheritance. I have seen people
advocate getting rid of the feature altogether but it is really useful for
a set of problems out there. The problem of course is that in its current
form it is a bit of a dangerous feature.

>
>>
> I agree with you that this feature won't (and probably shouldn't) change
> modelling approaches in the majority of the cases. But will bring new
> opportunities, therefore make PostgreSQL even more versatile. I still
> believe that in some cases - not just indistinctively - aggregation in
> object oriented modelling can definitely be logically modelled using
> arrays, with referential integrity guaranteed by this feature.

BTW, I don't know if you have seen the series I have been doing on
Object-Relational modelling in PostgreSQL but if you haven't,
http://ledgersmbdev.blogspot.com/ (right now there are 9 posts up with an
epilogue coming).

I cover a lot of "dangerous" features--- composite types in columns,
non-1NF designs, table inheritance. Particularly the nested storage post
might be interesting in terms of both uses and misuses of this proposed
feature.

In fact it occurs to me that the main thing it buys is an ability to do
subset constraints on the foreign key set gracefully, for example, ensuring
that there are between 5 and 10 foreign keys referenced in a specific case
or the like.

>
>
> However, after thinking about the feature overnight, I can see a
>> number of use cases for it, ranging from recording something like race
>> results (where update contention is definitionally not an issue
>> because the record of an event aren't supposed to change) to sanity
>> checks in materialized views, and there are probably additional uses
>> that are not apparent yet.
>>
>
> I totally agree with you. This is exactly what we (as a community) need to
> do now as far as this feature is concerned. We need to have a larger use
> base and from there fully understand what the community needs. For
> instance, for 9.2 we had already developed actions on update and delete
> operations - assuming generic use cases. We have preferred for now to take
> out that part and start with a simpler patch where actions are forbidden.
> Through community feedback we found a name for the feature that was
> commonly accepted (we had called them EACH FOREIGN KEYS last year), and
> came up with an easy to understand syntax (and a better naming). It was
> important not to go too far down an unexplored territory. :)
>
>
I think the problem for the cascade and set null operations is determining
the behavior to be defined. would ON DELETE CASCADE delete the value from
the array or would it delete the whole row? What about ON DELETE SET NULL?
Do we change the value in the array to NULL or just remove it from the
array? So I think for now that's sane.

I think in terms of community, the object-relational features do need more
exposure, and more attention generally. Part of the reason I started
blogging about them was to bring more attention to them, and try to help
get more exposure to the current costs and benefits of using them. If
people are pushing the boundaries a bit more, I think a lot of things will
get improved upon.

Best Wishes,
Chris Travers