Hacker News Reader

Comment by einhverfr | Hacker News Reader

I think you'd need partial unique indexes to make that remotely concurrency safe.

The combination of postgres's range types[1] and exclusion constraints[2] allow you to do this safely. My 'primary key' constraints look something like:

    ALTER TABLE foo ADD CONSTRAINT temporal_pk EXCLUDE USING GIST (
      identity_id WITH =,
      valid_range WITH &&
    );

It'd be nice if temporal foreign keys were supported too, where the existence of contiguous child rows through the valid_range of the parent was enforced, but I've had to use a trigger for that.

[1]: http://www.postgresql.org/docs/9.3/static/rangetypes.html [2]: http://www.postgresql.org/docs/9.3/static/ddl-constraints.ht...

I don't think partial unique index would have an impact on concurrency safety. An appropriate one would be useful for making the query for the most recent value faster, but that doesn't mean it would be concurrency-safe without a transaction.

Sure it does. You need to ensure that you only have one non-invalidated record. If you don't have partial unique index, you can't guarantee that two concurrent transactions won't invalidate the same row and add two different rows. With a partial unique index, you can make that guarantee.

How do you implement unique constraints then?

The main take-away from my original comment should be "never destroy data". You may write new data, but you may never delete or overwrite data. How you design the database after that is up to you. The particular example I used is of course not going to be good for enforcing, say, uniqueness of email addresses across users.

It has tended to make me really consider the notion of uniqueness within a database. What does the unique constraint mean? I think that people think of uniqueness to mean "at this point in time", but if you enforce it in the database, it means "for all time." In relational algebra, the unique constraint is really about enforcing a candidate key, a field that could satisfactorily be used as a key, but we've so happened to have chosen a different field as the actual primary key. In that context, given that we would want someone to be able to change their email address, I don't think "unique" email addresses make sense.

So many things change over time and so many seemingly "unique" fields turn out to not be unique in the real world that, for the most part, I've given up on the unique constraint. Full names are neither unique, nor are they final (people get married and change their name). Not even Social Security Numbers are necessarily unique, for a variety of reasons (http://www.computerworld.com/s/article/300161/Not_So_Unique).

That's what partial unique indexes allow you to put in the database. take this example which would work in PostgreSQL:

    CREATE UNIQUE INDEX profile_username_valid_idx_u
    ON profile(username)
    WHERE invalidated_at IS NULL;

The problem with enforcing this in app-space is it doesn't get around the isolation problem. In both cases, you are assuming that the same profile won't get concurrently updated twice by two different transactions. Since only the database can referee this issue, you must enforce the uniqueness there. I.e. this check must happen on transaction commit and your application has no way of knowing if it did.

Reply on news.ycombinator.com