PostgreSQL约束，可在提交时进行检查，而不是更早 [英] PostgreSQL constraint, that gets checked on commit and not earlier

问题描述

是否可以在PostgreSQL中创建创建唯一索引或其他类型的约束的方法，该约束或约束将在事务COMMIT上检查而不是在一毫秒之前进行？

Is it possible to create to create a unique index or other kind of constraint in PostgreSQL, that would be checked on transaction COMMIT and not a millisecond earlier?

我需要一对（record_id，顺序）的索引，因此我确保在给定的record_id内，只有一个且不超过一个记录具有相同的顺序。问题出在哪里？嗯，问题出在我使用的网络框架处理商品的重新排序方式上。看起来，当一个项目被移动时，当它的排序顺序改变时，框架用新的排序值写入新项目，然后不久它又更新了另一个，从而造成了一种临时情况，其中有多个记录具有相同的订购价值。重新排序所有内容后，所有记录都将更新，并且在事务COMMIT上，所有内容都应该会恢复正常。

I need an index for a pair of (record_id, ordering), so I make sure that inside a given record_id only one and no more than one records has the same ordering. Where's the problem? Well, the problem is in the way the web framework I'm using handles re-ordering the items. It looks like, when an item was moved, when its sort ordering was changed, the framework writes the new item with the new ordering value, then shortly after that it updates another one, thus creating a temporary situation where more than one record has the same ordering value. After re-ordering everything all the records gets updated and on transaction COMMIT everything should be fine again.

如果重要的话，我正在使用PostgreSQL 10。

I'm using PostgreSQL 10 if that matters.

推荐答案

使用设置约束命令：

设置约束 >

设置约束-设置当前交易的约束检查时间

SET CONSTRAINTS — set constraint check timing for the current transaction

简介

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

说明

SET CONSTRAINTS设置当前事务中的约束检查行为。立即约束在每个语句的末尾检查
。在提交事务之前，不会检查
个DEFERRED约束。每个约束都有其自己的
即时或延迟模式。

SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

创建后，约束将具有以下三个特征之一：
可初始延迟，可延迟最初立即，或不可延误
。第三类始终为IMMEDIATE，不受SET CONSTRAINTS命令的
影响。前两个类以指定的模式开始每个
事务，但是可以通过SET CONSTRAINTS更改事务中
的行为。

Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. The third class is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.

SET CONSTRAINTS带有约束名称列表的操作仅会更改
的模式（必须全部推迟）。每个约束
名称都可以是模式限定的。如果未指定架构名称，则使用当前架构搜索路径
查找第一个匹配名称。 SET
CONSTRAINTS ALL更改所有可延缓约束的模式。

SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints (which must all be deferrable). Each constraint name can be schema-qualified. The current schema search path is used to find the first matching name if no schema name is specified. SET CONSTRAINTS ALL changes the mode of all deferrable constraints.

当SET CONSTRAINTS将约束模式从DEFERRED更改为
IMMEDIATE时，新模式将追溯生效：在SET
CONSTRAINTS命令的执行过程中，将检查在
事务结束时将检查的所有未完成的
数据修改。如果违反了任何此类约束，则SET
CONSTRAINTS会失败（并且不会更改约束模式）。因此，SET
CONSTRAINTS可以用于强制检查约束是否发生在交易中
的特定点。

When SET CONSTRAINTS changes the mode of a constraint from DEFERRED to IMMEDIATE, the new mode takes effect retroactively: any outstanding data modifications that would have been checked at the end of the transaction are instead checked during the execution of the SET CONSTRAINTS command. If any such constraint is violated, the SET CONSTRAINTS fails (and does not change the constraint mode). Thus, SET CONSTRAINTS can be used to force checking of constraints to occur at a specific point in a transaction.

当前，只有UNIQUE，主键，引用（外键）和
EXCLUDE约束受此设置影响。当插入一行或修改
时（不在语句末尾），总是立即检查NOT NULL和CHECK
约束。还立即检查了尚未声明为DEFERRABLE的唯一性和排除性
约束。
。

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

触发称为约束触发器的触发器
也受此设置控制吗？它们会在应检查
关联约束的同时触发。

The firing of triggers that are declared as "constraint triggers" is also controlled by this setting — they fire at the same time that the associated constraint should be checked.

< hr>

您还可以在（例如）创建表

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

约束可以是：

• 已延期|最初立即


• 可延迟|不可延迟

这篇关于PostgreSQL约束，可在提交时进行检查，而不是更早的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！