CODEWITHSUNDEEP
postgresql
Mike Summers
Mike Summers

Reputation: 2239

Postgresql "reverse" foreign key "constraint"?

Two tables:

Table A
id bigint;    -- Table A primary key

Table B
id   bigint;  -- Table B primary key
a_id bigint;  -- Table A foreign key

On Table A insert I want to ensure that Table B has entries for Table A and rollsback if it does not.

In PostgreSQL is the best/only way to do this on an AFTER INSERT trigger?

Does this sort of relationship have a name?

Thanks.

Upvotes: 2

Views: 2389

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324751

What you appear to want is a one-to-many mandatory relationship, i.e. for the m:n relationship the n side may not be zero.

Use a trigger

There are a couple of ways to do this, but the main way is to define triggers on both A and B:

  • An AFTER INSERT OR UPDATE trigger on A that checks to ensure that a B row with B.a_id = NEW.id exists;

  • An AFTER UPDATE OR DELETE trigger on B that checks to ensure that (on DELETE or if the UPDATE changes the a_id) there is at least one other remaining B with the same a_id, so that the constraint is not violated.

It's fine for them to be AFTER triggers, and in fact necessary in order to allow certain kinds of row swaps to occur. They should be created as constraint triggers.

Needs DEFERRABLE

It should be clear that the above won't work exactly as written, because there's presumably a FK constraint on B.a_id to enforce the existence of the corresponding A.id. So you can't INSERT a B until there's an A, and you can't insert an A until there's a B. So what do you do?

You make the triggers and/or the foreign key constraint deferrable.

(You can instead use a writeable CTE to insert both A and B in the same statement, but ... ugh. Lets not.)

What about the race condition?

Imagine you have an A with two Bs. Two concurrent transactions each go to delete from B.

Each sees that there's another B to satisfy the constraint. So each allows the DELETE to proceed. Whoops. Both commit, and now you have zero Bs for that A. What do you do?

Options:

  • SERIALIZABLE isolation. In this case one or both transactions will fail with a serialization failure. The app must be prepared to trap the error and retry or inform the user that they can't delete the row because it's the only one left. A well written app is always ready to retry queries that fail due to transient deadlocks, server restarts, connectivity issues, anyway, so this is often the best option. Just remember that the whole transaction must be redone.

  • Have each SELECT 1 FROM A WHERE A.id = 'the-B.a_id' FOR UPDATE to take a row lock on the A they're modifying. Thus one will always wait for the other to commit or roll back. This works well. You can generally put the SELECT ... FOR UPDATE that locks the row of A inside the trigger on B, but only if you never also take FOR SHARE or FOR UPDATE locks on other rows from A, otherwise you're likely to get deadlocks. In which case you have to handle deadlock aborts with transaction retries. We use a FOR UPDATE lock so that two concurrent DELETEs from B are serialized.

For similar reasons your trigger on A should take a FOR SHARE lock on the rows of B that it finds when doing an AFTER INSERT OR UPDATE existence-check to make sure B rows exist. Otherwise a concurrent transaction could DELETE those rows or UPDATE them with a different B.a_id. A FOR SHARE lock is sufficient here because you don't have to prevent concurrent inserts/updates on that A row, the lock automatically taken on that A row does so already.

Test. Lots.

You need to do lots of race condition testing.

The isolation tester tool in PostgreSQL's source code tree can be useful for this, as can simply writing programs that try to do all sorts of combinations of changes at the same time using different processes/threads, then sanity check the results at the end.

Is there a simpler way?

You can instead add a b_id column to A, and make it a NOT NULL FOREIGN KEY reference to B marked DEFERRABLE INITIALLY IMMEDIATE. When you want to change the A/B relationship you SET CONSTRAINTS DEFERRED and make the changes.

This basically labels one B as the "main" one for that A, ensuring that one already exists.

Upvotes: 4

Related Questions