❇️ 🙏 🅿️ PostgreSQL反模式：唯一ID 👩🏾‍🔬 ☣️ 🔻

通常，开发人员需要为PostgreSQL表中的记录生成一些唯一的标识符-在插入记录和读取记录时都如此。

柜台桌

似乎-哪个更容易？我们在其中设置了一个单独的盘子-一个带有计数器的条目。我们需要得到一个新的标识-从那里读到写一个新的价值-做到这一点UPDATE......

待办事项那样做！因为明天您将不得不解决问题：

参见PostgreSQL反模式时的持久重叠锁：对抗“死”群体UPDATE
访问计数器表数据的速度逐渐降低，

请参见PostgreSQL反模式：在负载下更新大表
...以及需要使用会打扰您的活动事务进行清理的信息，

请参阅DBA：当VACUUM通过时，我们会手动清理表

SEQUENCE对象

对于此类任务，PostgreSQL提供了一个单独的实体- SEQUENCE。它是非事务性的，即不会引起锁定，但是两个“并行”事务肯定会接收不同的值。

要从序列中获取下一个ID，只需使用函数nextval：

SELECT nextval('seq_name'::regclass);

有时您需要一次获取多个ID-例如，通过COPY进行流式录制。这样setval(currval() + N)做根本是错误的！出于简单的原因，在调用“内部”（currval）和“外部”（setval）函数之间，并发事务可能会更改序列的当前值。正确的方法是调用nextval所需的次数：

SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);

串行伪

在“手动”模式下使用序列不是很方便。但是我们的典型任务是确保插入具有新序列ID的新记录！专门为此目的，发明了PostgreSQL serial，它在生成表时将“扩展”为。无需记住链接到该字段的自动生成序列的名称，它具有此功能。可以在您自己的替换中使用相同的函数-例如，如果需要一次为多个表建立公共序列。但是，由于使用该序列是非事务性的，因此如果回退的事务从该序列中接收到标识符，则保存的表记录中的ID序列将是“泄漏的”id integer NOT NULL DEFAULT nextval('tbl_id_seq')

pg_get_serial_sequence(table_name, column_name)DEFAULT

...

GENERATED栏

从PostgreSQL 10开始，可以声明符合SQL：2003标准的标识列（GENERATED AS IDENTITY）。在该变体中，GENERATED BY DEFAULT行为是等价的serial，但GENERATED ALWAYS所有事情都更加有趣：

CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);

INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

是的，要在这样的列中“跨”插入特定值，您将需要做出额外的努力OVERRIDING SYSTEM VALUE：

INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .

请注意，现在表中有两个相同的值id = 1-即GENERATED不会强加其他UNIQUE条件和索引，而仅仅是一个声明以及serial。

一般而言，在现代PostgreSQL版本上，不建议使用serial，而首选替换为GENERATED。也许除了支持使用低于10的PG的跨版本应用程序的情况。

生成的UUID

只要您在一个数据库实例中工作，一切都会很好。但是，当它们中有多个时，就没有足够的方法来同步序列（但是，如果您确实想这样做，这并不能防止您“不适当”地同步它们）。这里可以为其生成值的类型UUID和功能。我通常将其uuid_generate_v4()用作最“随意”的一种。

隐藏的系统字段

表样/ ctid

有时，从表中获取记录时，您需要以某种方式处理特定的“物理”记录，或者找出在使用继承访问“父”表时从哪个特定节中获得特定记录。

在这种情况下，每个记录中存在的隐藏系统字段将帮助我们：

tableoid存储表的oid-id-即tableoid::regclass::text给出特定表节的名称
ctid -记录的“物理”地址格式 (<>,<>)

例如，ctid它可以用于没有主键的带有表的操作，但是可以tableoid用于某些类型的外键的实现。

oid

创建属性表时最多可以声明11个PostgreSQLWITH OIDS：

CREATE TABLE tbl(id serial) WITH OIDS;

此表中的每个条目获得一个额外的隐藏字段oid有一个全球唯一的数据库内的价值-因为它是为组织系统表像pg_class，pg_namespace...

当你插入立即返回到查询结果表产生值的记录：

INSERT INTO tbl(id) VALUES(DEFAULT);

  :   OID 16400   11 .

对于“普通”表查询，此类字段不可见：

SELECT * FROM tbl;

id
--
 1

与其他系统字段一样，必须明确要求它：

SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;

tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1

诚然，该值oid是只有32位，所以它是很容易得到溢出，之后，oid它甚至不会有可能产生的任何表（它需要一个新的！）。因此，从PostgreSQL 12开始，WITH OIDS不再支持它。

“公平”时间clock_timestamp

有时，当查询或过程长时间运行时，您希望将“当前”时间绑定到记录。失败等待着任何尝试使用该函数执行此操作的人now()-它将在整个事务中返回相同的值。

为了获得“现在”的时间，有一个函数clock_timestamp()（和它的另一个兄弟）。这些功能的行为之间的差异可以在一个简单查询的示例中看到：

SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);

              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03

PostgreSQL反模式：唯一ID

柜台桌