I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table:
Table "public.team_joins"
Column | Type | Modifiers
-----------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('team_joins_id_seq'::regclass)
team_id | integer | not null
Indexes:
"team_joins_pkey" PRIMARY KEY, btree (id)
"team_joins_team_id" btree (team_id)
Foreign-key constraints:
"team_id_refs_teams_id" FOREIGN KEY (team_id) REFERENCES teams(id) DEFERRABLE INITIALLY DEFERRED
So, for example, if a team with id 3 only allows 20 players, and SELECT COUNT(*) FROM team_joins WHERE team_id = 3
is equal to 20, no players should be able to join team 3. What's the best way to handle this and avoid concurrency issues? Should I use a SERIALIZABLE
transaction to insert, or can I just use a WHERE
clause like this in the insert statement?
INSERT INTO team_joins (team_id)
VALUES (3)
WHERE (
SELECT COUNT(*) FROM team_joins WHERE team_id = 3
) < 20;
Or is there a better option that I'm not considering?
Best Answer
Typically, you have a
team
table (or similar) with a uniqueteam_id
column.Your FK constraint indicates as much:
... REFERENCES teams(id)
- so I'll work withteams(id)
.Then, to avoid complications (race conditions or deadlocks) under concurrent write load, it's typically simplest and cheapest to take a write lock on the parent row in
team
and then, in the same transaction, write the child row(s) inteam_joins
(INSERT
/UPDATE
/DELETE
).Example for single row
INSERT
. To process a whole set at once, you need to do more; see below.One might suspect a corner case problem in the
SELECT
. What if there is no row withteam_id = 3
, yet? Wouldn't theWHERE
clause cancel theINSERT
?It wouldn't, because the
HAVING
clause makes this an aggregation over the whole set which always returns exactly one row (which is eliminated if there are 20 or more for the giventeam_id
already) - exactly the behavior you want. The manual:Bold emphasis mine.
The case where no parent row is found is no problem either. Your FK constraint enforces referential integrity anyway. If
team_id
is not in the parent table, the transaction dies with a foreign key violation either way.All possibly competing write operations on
team_joins
have to follow the same protocol.In the
UPDATE
case, if you change theteam_id
, you would lock the source and the target team.Locks are released at the end of the transaction. Detailed explanation in this closely related answer:
In Postgres 9.4 or later, the new, weaker
FOR NO KEY UPDATE
may be preferable. Also does the job, less blocking, potentially cheaper. The manual:Another incentive to consider upgrading ...
Insert multiple players of the same team
Usefully assuming you have a column
player_id integer NOT NULL
. Same locking as above, plus ...Short syntax:
The set-returning function in the
SELECT
list does not comply with standard SQL, but it's perfectly valid in Postgres.Just don't combine multiple set-returning functions in the
SELECT
list before Postgres 10, which finally fixed some unexpected behavior there.Cleaner, more verbose, standard SQL doing the same:
That's all or nothing. Like in a Blackjack game: one too many and the whole
INSERT
is out.Function
To round it off, all of this could conveniently be encapsulated in a
VARIADIC
PL/pgSQL function:About
FOUND
.Call (note the simple syntax with a list of values):
Or, to pass an actual array - note the
VARIADIC
key word again:Related: