问题:Postgres 'if not exists' 失败,因为序列存在

我正在构建的应用程序中有几个计数器,因为我试图让它们根据需要由应用程序动态创建。

举个简单的例子,如果有人在脚本中键入一个单词,它应该返回该单词之前输入的次数。这是一个 sql 示例,如果他们键入单词 example,则可以执行该示例。

CREATE SEQUENCE IF NOT EXISTS example START WITH 1;
SELECT nextval('example')

这将在第一次运行时返回1,第二次返回2,以此类推。

问题是当 2 个人同时单击按钮时。首先,请注意,我的应用程序中发生的不仅仅是这些语句,因此它们重叠的可能性比这一切都发生的情况要大得多。

1> BEGIN;
2> BEGIN;
1> CREATE SEQUENCE IF NOT EXISTS example START WITH 1;
2> CREATE SEQUENCE IF NOT EXISTS example START WITH 1; -- is blocked by previous statement
1> SELECT nextval('example')  -- returns 1 to user.
1> COMMIT;  -- unblocks second connection
2> ERROR:  duplicate key value violates unique constraint 
   "pg_type_typname_nsp_index"
   DETAIL:  Key (typname, typnamespace)=(example, 109649) already exists. 

我的印象是,通过使用“IF NOT EXISTS”,如果该语句确实存在,则它应该只是一个无操作,但它似乎有这种竞争条件,而事实并非如此。我说竞争条件是因为如果这两个没有同时执行,它会按预期工作。

我注意到IF NOT EXISTS对 postgres 来说是相当新的,所以也许他们还没有解决所有的问题?

EDIT:我们考虑以这种方式做事的主要原因是避免过度锁定。想法是,如果两个人同时递增,使用一个序列将意味着两个用户都不必等待另一个(除了,如本例中,为该序列的初始创建)

解答

序列是数据库模式的一部分。如果您发现自己根据存储在数据库中的数据动态地修改模式,那么您可能做错了什么。对于具有特殊属性的序列尤其如此,例如关于他们在交易方面的行为。具体来说,如果您在事务中间增加一个序列(在nextval的帮助下)然后回滚该事务,则该序列的值将不会回滚。所以最有可能的是,这种行为是您不希望您的数据出现的。在您的示例中,假设用户尝试添加单词。这导致相应的序列被递增。现在假设事务由于某种原因(例如计算机崩溃)没有完成并且它被回滚。你最终会得到这个词没有被添加到数据库中,但序列被增加了。

对于您提到的特定示例,有一个简单的解决方案;创建一个普通表来存储所有“序列”。类似的事情会做到这一点:

CREATE TABLE word_frequency (
    word text NOT NULL UNIQUE,
    frequency integer NOT NULL
);

现在我知道这只是一个示例,但如果这种方法不适用于您的实际用例,请告诉我们,我们可以根据您的需要进行调整。

编辑:这是上述解决方案的工作方式。如果添加了一个新单词,请运行以下查询(仅在 postgres 9.5+ 中使用“UPSERT”语法):

INSERT INTO word_frequency(word,frequency)
VALUES ('foo',1)
ON CONFLICT (word)
DO UPDATE
SET frequency = word_frequency.frequency + excluded.frequency
RETURNING frequency;

这个查询将在word_frequency中插入一个频率为 1 的新词,或者如果该词已经存在,它会将现有频率增加 1。如果两个事务同时尝试这样做会发生什么?考虑以下场景:

client 1          client 2
--------          --------
BEGIN
                  BEGIN
UPSERT ('foo',1)
                  UPSERT ('foo',1) <====
COMMIT
                  COMMIT

将发生的情况是,一旦客户端 2 尝试增加 foo 的频率(用上面的箭头标记),该操作就会阻塞,因为该行已被不同的事务修改。当客户端 1 提交时,客户端 2 将被解除阻塞并继续,不会出现任何错误。这正是我们希望它工作的方式。另请注意,postgresql 将使用行级锁定来实现此行为,因此不会阻止其他插入。