+39 0574 159 3000
PostgreSQL 9.3 development: Array ELEMENT Foreign Keys
As my French colleague Dimitri Fontaine was pointing out a few days ago, PostgreSQL 9.2 is out. This is another great release for PostgreSQL, but we are already ahead in the development of the next release: PostgreSQL 9.3.
The Italian team of 2ndQuadrant has been working since last year on adding a new feature to PostgreSQL: support of referential integrity between the elements of an array in a table (referencing) and the records of another table (referenced).
We renamed it “Array ELEMENT foreign keys” – thanks to the feedback received from the hackers list. As you may have guessed, it is not part of the SQL standard. We have submitted a patch for 9.3, but currently it is still missing a reviewer.
This patch is heavily based on the experience we did with the one we submitted for PostgreSQL 9.2 and did not make it. Here we are again, this time with a smaller patch – hoping to improve this feature when (and if) it is accepted in Postgres’ core. Array remove and replace functions, for instance, were part of the original patch and have already been included in 9.3.
This feature adds the ELEMENT REFERENCES column constraint, as well as the array ELEMENT table constraint in foreign keys. Current implementation allows only for NO ACTION and RESTRICT actions, even though specific actions have already been considered and will eventually be included in future releases.
A simple example is probably the easiest way to explain the feature:
[sql]
CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
first_name text,
last_name text,
…
);
CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
…
practice1_positions integer[] ELEMENT REFERENCES drivers,
practice2_positions integer[] ELEMENT REFERENCES drivers,
practice3_positions integer[] ELEMENT REFERENCES drivers,
qualifying_positions integer[] ELEMENT REFERENCES drivers,
final_positions integer[] ELEMENT REFERENCES drivers
);
[/sql]
We have 5 arrays of integers where each element points to a record in the drivers table, and referential integrity is enforced (e.g., we cannot insert in any of those arrays a driver that does not exist). For example, the first element in the final_positions array identifies the driver that won that particular race.
Before this patch, we would have had a junction (linking) table to enforce referential integrity. For example, a table called practice1_positions with:
- race_id (FK)
- position
- driver_id (FK)
Right, we could have kept the model simpler with just one single table, called race_position and another identifying column for the type of position (practice1, practice2, etc.). In any case, the model is still more complex due to the presence of a junction table between races and drivers.
Another important aspect of the array ELEMENT foreign key patch is that the position of the item in the array comes along with implicit – albeit optional – information on ordinality (useful for many use cases). Constraints on the cardinality of the array can be applied too, forcing for example the number of elements (consider the starting lineup of a football/soccer team, which has 11 available slots).
However, what I like the most of this model though is that it allows developers to think in terms of object aggregation even at database level, implementing many-to-many relationships without the need of linking tables – while keeping the same requirements of logical data integrity and consistency.
Apart from the tough challenge of being accepted in the core of PostgreSQL, it would be interesting to see how this feature is taken into consideration by ORMs – but I guess this is just speculation as things stand now.
In any case, I take the chance to invite everyone that wants to contribute to PostgreSQL to join the current commit fest and become a reviewer for this patch.
A very useful resource for new (not only) reviewers is the Wiki of PostgreSQL.
I strongly appreciate the inclusion of this ELEMENT REFERENCES constraint into the PostgreSQL core. Just because we need it. Normalising is not an option in our case since it concerns several dozens of multi-valued attributes which are list (catalogue) values referenced by integer ids (soil database). Normalising would simply blow our scheme.
(We created thirty or so environment databases without making use of arrays. But this one is different.)
Matthias
Great functionality…
Would that work with multi-columns primary keys?
Another syntax I think would be great, something like:
CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
…
practice1_positions drivers[]
…
);
Or something like it would be also great. And a “JOIN FETCH”-like ORMs to join all.
very cool feature !
what’s the syntax using `ALTER TABLE` ?
something like this ?
`alter table posts add constraint posts_tags_fk foreign key (tag_ids) element references tags(id);` ?
can’t wait february 2013 to test it
Great, this would be a very useful feature!
I’d love this feature; I find that this type of a feature can bridge the divide between the mature SQL relational database approaches and the emerging MongoDB types of software. In the webapp world that I live in, the handiest way to exchange data between server and client is as JSON objects, yet the handiest way to enforce referential integrity is to use a relational db. So this type of feature would be a huge win for me. Spread the word!
I also think this would be a very useful features, however looking at the documentation for versions 9.3 and 9.4 it looks like it was not included. Is there any update on the progress of accepting the patch into the PostgreSQL mainline? Thanks.
Looks like this won’t make it into 9.3. Really hoping it can in 9.4!
Unfortunately, this doesn’t look to be in the 9.4 roadmap either :/ http://wiki.postgresql.org/wiki/Todo#Arrays
It’s already possible to use arrays as junction tables as I outline in my blog post http://blog.j0.hn/post/57492309635/using-arrays-as-relation-tables-in-postgres
But you have to use triggers to enforce foreign keys. No bueno. It looks like we might have to wait until 9.5 to get array foreign key referencing.
Hi,
how to install this patch on Postgresql 9.3 (Ubuntu 11.4), some instruction?
Just today, I needed this feature for an application I am developing and I am sad it didn’t make it into 9.3.
It would be a very useful addition and will make ARRAYs really interesting, especially in these troubled NoSQL times… I guess I will have to live without integrity for a while, or maybe rely on triggers.
I also need this feature for my projects. However the patch seems to stay out of interest….
Any way for us to help?
Guys any update on this. I love PG, and this is currently top 1 of my missing features.
Any news if it’s going to be included?
It won’t. Performance concerns led to the feature not making the cut for 9.4, and there’s nobody currently working on it.
Why it may be incorporated into Postgres with documentation what implementation is not so bleeding fast?
Because the performance impact went beyond users of the feature, and into other parts of the system.
This would be very useful to me. Right now I use triggers and an N-M table to enforce referential integrity, but a direct implementation in the database would be a lot easier to manage. Is there any news on this feature? Was it included in Postgres 9.5 or 10?
It was not included in Pg 9.5 or 10, and as far as I know nobody has picked it up to work on it.
That’s extremely disappointing – I understand that this kind of FK can possibly have negative performance impact. However in the end it’s user decision to use it or not.
How one can influence Postgres team to pick-up this feature? Judging by no of question’s related to that it seams like popular request among community.