sql - Postgres 仅在行数低于限制时才插入行

Question

我希望在 Postgres 10 中有一个 SQL 语句或函数，它允许我仅在已经少于特定数量的行时插入一行。

select count(*) from mytable where mykey = 1
--- do the following only if the above is less than 5
insert into mytable (myvalue, mykey) values ('randomvalue', 1)
--- so there are never more than 5 rows in mytable with mykey = 1

像这样的伪代码是否可以在应用程序中工作(对 postgres 服务器的多次往返调用)？

tx = app.tx("start transaction");
count = tx.query("select count(*) from mytable where mykey = 1")
if (count < 5) {
  tx.query("insert into mytable (myvalue, mykey) values ('randomvalue', 1)")
}
tx.do("commit")

如果它不起作用，我怎么能在 Postgres 端执行此操作，所以我只从应用程序调用一次，例如 select myinsertfunction('randomvalue', 1) ？

无论是上面的多次往返方式，还是对 postgres 的单次调用，最重要的是，这不可能以插入超过 5 行的方式并行运行。例如，事务 1 和事务 2 都检查计数是否为 4(小于最大值 5)，并同时进行，因此它们最终都会插入 1 行，每行导致总共 6 行，而不是最多 5 个。

Answer 1

此问题称为 Phantom Read :

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

尝试

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO mytable (myvalue, mykey) SELECT 'randomvalue', 1 WHERE
    (SELECT COUNT(*) FROM mytable WHERE mykey = 1) < 5;
END;

事务隔离级别将确保事务仅在计数小于 5 时才插入值。