6

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?

  • 1
    Are you always inserting single rows to team_joins or can it be a whole set at once? Is there a table team (or similar) with unique rows per team_id? Aside: Postgres 9.3? Have you considered upgrading? – Erwin Brandstetter Mar 15 '17 at 19:01
  • 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. There is indeed a teams table with an id column that is the primary key. – Rob Johansen Mar 15 '17 at 19:16
10

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:

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 some unexpected behavior there.

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 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$  LANGUAGE plpgsql;

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:

  • I should clarify further that, even though it will be necessary to insert a whole set of players in the future, each set will always be players joining a single team. It will never be necessary for a single transaction to insert multiple players into different teams. I assume this means that the write locking portion of your answer would not be affected. – Rob Johansen Mar 15 '17 at 19:35
  • @AlienBishop: Yup, that's exactly what this means. – Erwin Brandstetter Mar 15 '17 at 19:36
  • I don't think this works. You're not locking the table's rows that don't exist before the INSERT statement. So you can insert extra rows there. And, even during the INSERT ... query I don't think you're guaranteed that nothing touches the table before the INSERT finishes and after the count(*). – Evan Carroll Mar 15 '17 at 19:40
  • 1
    @ErwinBrandstetter is right, I didn't see another table. despite him having it bolded. =( My bad. – Evan Carroll Mar 15 '17 at 20:00
  • 1
    @AlienBishop: You may be interested in the updated function. – Erwin Brandstetter Mar 16 '17 at 12:58
1

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.

0

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?

  • 1
    If I use SERIALIZABLE in this case, do I actually have to replay transactions? Wouldn't a serialization failure indicate that the failed INSERT violated the WHERE clause? – Rob Johansen Mar 15 '17 at 18:51
  • No, what if someone added one row to the table after the count(*). PostgreSQL doesn't know what the count(*) is testing against, just that it's stale in one transaction. – Evan Carroll Mar 15 '17 at 19:00
  • My advice is to avoid SERIALIZABLE like the plague. After you go down that route, everything has to be SERIALIZABLE. Good luck with that. I acknowledge others feel differently about this mode though. – Evan Carroll Mar 15 '17 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.