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:

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
);

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.

This Post Has 8 Comments

  1. Username* says:

    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

  2. Matheus de Oliveira says:

    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.

  3. jney says:

    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

  4. Glyn Astill says:

    Great, this would be a very useful feature!

  5. Stephan Doliov says:

    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!

  6. Joe Van Dyk says:

    Looks like this won’t make it into 9.3. Really hoping it can in 9.4!

  7. John Fawcett says:

    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.

  8. ppeess says:

    Hi,

    how to install this patch on Postgresql 9.3 (Ubuntu 11.4), some instruction?

Leave A Reply