Re: foreign key from array element - Mailing list pgsql-general

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