How to perform conditional insert based on row count?

Question

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?

Answer 1

Typically, you have a team table (or similar) with a unique team_id column.
Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(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) in team_joins (INSERT / UPDATE / DELETE).

BEGIN;

SELECT * FROM teams WHERE id = 3 FOR UPDATE;  -- write lock

INSERT INTO team_joins (team_id)
SELECT 3                -- inserting single row
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < 20;

COMMIT;

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 with team_id = 3, yet? Wouldn't the WHERE clause cancel the INSERT?
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 given team_id already) - exactly the behavior you want. The manual:

If a query contains aggregate function calls, but no GROUP BY clause, grouping still occurs: the result is a single group row (or perhaps no rows at all, if the single row is then eliminated by HAVING). The same is true if it contains a HAVING clause, even without any aggregate function calls or GROUP BY clause.

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 the team_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:

• How to atomically replace a subset of table data

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:

Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

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:

INSERT INTO team_joins (team_id, player_id)
SELECT 3, unnest('{5,7,66}'::int[])
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < (21 - 3);  -- 3 being the number of rows to insert

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 related unexpected behavior.}

Cleaner, more verbose, standard SQL doing the same:

INSERT INTO team_joins (team_id, player_id)
SELECT team_id, player_id
FROM  (
   SELECT 3 AS team_id
   FROM   team_joins
   WHERE  team_id = 3
   HAVING count(*) < (21 - 3)
   ) t
CROSS JOIN (
   VALUES (5), (7), (66)
   ) p(player_id);

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:

CREATE OR REPLACE FUNCTION f_add_players(team_id int, VARIADIC player_ids int[])
  RETURNS bool
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT * FROM teams WHERE id = 3 FOR UPDATE;         -- lock team
-- SELECT * FROM teams WHERE id = 3 FOR NO KEY UPDATE;  -- in pg 9.4+

   INSERT INTO team_joins (team_id, player_id)
   SELECT $1, unnest($2)                                -- use $1, not team_id
   FROM   team_joins t
   WHERE  t.team_id = $1                                -- table-qualify to disambiguate
   HAVING count(*) < 21 - array_length($2, 1);
   -- HAVING count(*) < 21 - cardinality($2);           -- in pg 9.4+

   RETURN FOUND;                                        -- true if INSERT
END
$func$;

About FOUND.

Call (note the simple syntax with a list of values):

SELECT f_add_players(3, 5, 7, 66);

Or, to pass an actual array - note the VARIADIC key word again:

SELECT f_add_players(3, VARIADIC '{5,7,66}');

Related:

• How to use an array as argument to a VARIADIC function in PostgreSQL?
• Pass multiple values in single parameter

Answer 2

I am answering your comment

For now I'm always inserting single rows, but in the future it will likely be necessary/desirable to insert a whole set at once

I don't know how you store a player has joined a team or not. So i will call them "newplayer" If you have a lot of "newplayers" waiting for joining a team, I would suggest this kind of query to now how many team you have to create :

SELECT DISTINCT ((ROW_NUMBER() OVER () -1)/20) +1) 
FROM newplayer

It returns a list of number from 1 to needed max. If you have 55 players without team, it will return '1','2','3'. Then you can join on this request to insert your 3 teams at once.

For you team_joins, something like this :

WITH match AS (SELECT ((ROW_NUMBER() OVER () -1)/20) +1) AS teamId, newplayers.id as playerId)
INSERT INTO team_joins (team_id, player_id)
match.teamId, match.playerId
FROM match 

Up to you at change '20' into 'team.limit' and substract count of already inserted joins for each team.

Answer 3

You can easily accomplish this atomically..

INSERT INTO team_joins (team_id)
SELECT team_id
FROM team_joins
GROUP BY team_id
HAVING count(*) < 20;

However, I'm not sure that the best way because of concurrency concerns. Without SERIALIAZABLE it's still possible but extremely unlikely under normal workloads, that the select will complete before the INSERT starts. My favorite way to solve the concurrency problem is not to INSERT or DELETE concurrently unless you have to. Instead, to pre-insert when you add the team and allocate the team members. Without SERIALIZABLE, there are lots of edge cases. With SERIALIZABLE you have to replay transactions -- both of those solutions are more concrete and but far more complex.

As a general rule, it's easy and natural to lock/protect rows and guarantee against modification during a transaction. It's complex to protect tables from INSERTs.

You may find my other answer interesting Strategy for concurrent group bookings?