Postgres的“if not exists”失败是因为序列已存在。

Question

Postgres的“if not exists”失败是因为序列已存在。

6

我正在构建一个应用程序，其中有几个计数器，我正在尝试让它们根据需要动态创建。

举个简单的例子，如果有人在脚本中输入单词，它应该返回此单词之前出现的次数。以下是可能执行的SQL示例，如果他们输入了单词“example”。

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

第一次运行这段代码将返回1，第二次返回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中相对较新，所以也许他们还没有解决所有问题？

编辑：我们考虑以这种方式处理的主要原因是避免过多的锁定。想法是如果两个人同时增加，使用序列意味着两个用户都不必等待对方（除了像这个例子中一样，初始创建该序列）。

- Shadow

2

我从未见过能够动态地进行DDL更改的应用程序。您能否更详细地解释一下您的整体架构？为什么需要动态创建这些序列？ - stdunbar

我可能不会 - 我们只是在考虑处理这种情况的不同选择。采用序列表的方法可能是我们要选择的方法，但在选择最合适的方法之前，我想考虑一些不同的方法。无论我们如何选择，这仍然对我来说似乎是错误的行为... - Shadow

“如果两个人同时进行递增，使用序列意味着两个用户都不必等待另一个人” - 这是错误的假设。 - user330315

@a_horse_with_no_name - 不，我不会重新创建它。IF NOT EXISTS 的想法是如果存在，则不会发生任何事情。此外，我刚刚测试了一下关于用户是否必须互相等待的理论 - 我已经确认即使两个用户都还保持着事务打开状态，他们都能够获得序列号 - 所以这实际上并不是一个错误的假设。 - Shadow

如果你不想“重新创建”它，那么为什么要在用户操作触发的第一次运行CREATE SEQUENCE呢？为什么不在其余表格一起创建它并完成呢？ - user330315

显示剩余2条评论

2个回答

2

编辑：我们考虑以这种方式处理的主要原因是为了避免过多的锁定。想法是，如果两个人同时增加，使用序列意味着没有一个用户需要等待另一个用户（除了在这个例子中，对于该序列的初始创建）。

听起来你正在优化一个可能不存在的问题。当然，如果你有100,000个同时插入行的用户（因为序列通常只在那时使用），那么序列可能会有一些争用的可能性，但实际上，在序列成为障碍之前，还会有其他瓶颈。

我建议你首先证明序列是一个问题。通过适当的数据库设计（动态DDL不是），序列将不会成为瓶颈。

作为参考，DDL在大多数数据库中都不是事务安全的。

- stdunbar

我们不幸地证明这是一个问题。我们的自动化测试定期遇到它，即使在生产中，我们也见过它出现几次。我可以向您保证，如果这不是一个问题，我们就不会研究它。就像这个例子，我所说的并不是插入行，而是在需要时创建增量器。它是用于生成唯一递增的条形码（或其片段），如果您需要真实的用例。此外，这个问题是特定于Postgres的，我们不需要担心它是否通用。如果我们更改数据库，我们将更改方法。 - Shadow

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- redneb · Accepted Answer

序列是数据库架构的一部分。如果您发现自己根据存储在数据库中的数据动态修改架构，则可能正在做错事情。这对于序列尤其如此，因为它们具有特殊属性，例如有关它们与事务相关的行为。具体来说，如果您在事务中间增加序列（借助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 将使用行级锁来实现此行为，因此其他插入操作不会被阻塞。