Re: foreign key from array element

On Tue, Sep 18, 2012 at 6:12 AM, Gabriele Bartolini <
gabriele(dot)bartolini(at)2ndquadrant(dot)it> wrote:

> Hi Rafal,
>
> Il 18/09/12 13:00, Rafal Pietrak ha scritto:
>
> I did have a look, and feel slightly encouraged reading: "Many people
>> feel that they're not qualified"; yes, that's me all right. Serously, I
>> will try to do by best ... by the weekend, when I get some spare time.
>>
>
> It is such a coincidence that yesterday I had started to write this
> article (http://blog.2ndquadrant.com/**postgresql-9-3-development-**
> array-element-foreign-keys/<http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/>)
> about this feature for 9.3 and this morning I noticed your message.
>
> It would be great if you could at least try the patch as a user and give
> feedback.
>
> Now that I have had a chance to digest the blog post, I want to offer some
feedback as someone who typically pushes the boundaries a bit when it comes
to object-relational features in PostgreSQL (and in fact I am still looking
at building an object-oriented API for the database into the LedgerSMB
PostgreSQL schema, although storage will be more relational), in the
question of "do we want this?" My answer is a cautious "yes."

My answer is cautious because I think that attempts to help developers
think in objects all the way down will always end in tears. You can see my
recent blog posting about nested storage gotchas (and this is a nested
storage design feature btw) at
http://ledgersmbdev.blogspot.com/2012/09/or-modelling-part-5-nested-data.htmland
I would therefore hope people aren't using this feature frequently and
only saving it for the (relatively rare) cases where it is appropriate.

The two big issues are that contention issues are far easier to solve for
inserts than updates, and that if there is any real separation of concerns,
then objects in the database are just different than they are in the
application (and in fact I think the LSP gets turned on its head in the
database). If you are trading inserts for updates, you are trading
simplicity in mapping your application for additional lock contention. It
isn't always obvious at the start of a project whether or not these will
ever become issues, but they may be painful to fix down the road.
Additionally select performance is not necessarily well optimized for
this, and you may have issues with being able to sufficiently index your
array so that it is useful in the sorts of queries you want to do. This may
force joins into bad orders, and may cause bad plans down the road.

.The second point is that if we see the database as modelling information,
while the application models behavior (this avoids nasty issues of mixing
transactional and non-transactional workloads and separates concerns) then
basic principles of object-oriented design end up having very different
implications. For example, "a square is-a rectangle" poses no LSP issues
regarding information modelling but is an LSP violation regarding behavior
modelling. Every calculation you can do on a rectangle you can do on a
square, but everything not everything you can do to a rectangle without
making it other than a rectangle can be done to a square. So I think that
when you get into this, you will find that there are a lot of hidden
gotchas in assuming a simple equivalence between application and database
classes.

So those are the cautions and why I don't think a feature like this is
suitable for routine usage, but truth be told a lot of the
object-relational features are definitely not for routine usage and make a
mess of things if people use them just because they can. I use table
inheritance and I totally understand a lot of people's hostility towards
this feature. Again, anytime you break 1NF you should probably have a
really good reason. I don't think this changes here.

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. So yes,
I certainly think I'd like to see this make it in.

Additionally I guess I am also somewhat eager to see more people really
grapple with the current rough edges in RI and object-relational modelling.
If this highlights what PostgreSQL can do, I am even more for it. If this
helps get people involved, even better. Finally, this may prove to be a
useful springboard towards even more capable designs regarding nested
storage down the road (though this has intimidating gotchas in actual use
too). For example, if this were at some point combined with an ability to
look inside composite types to find foreign keys (something like FOREIGN
KEY ((country_ref).country_id) REFERENCES country (id) or the like), then
row store functionality might be even more useful in terms of an
intermediate insert format in some cases.

So yeah, as far as the feature goes, as documented, I haven't tried it
fully yet (expect to do so this weekend), but it looks useful at least in
some cases.

Best Wishes,
Chris Ttravers