postgresql - 如何在 PostgreSQL 中有效地检查序列中已使用和未使用的值

Question

在 PostgreSQL (9.3) 中，我有一个表定义为:

CREATE TABLE charts
( recid serial NOT NULL,
  groupid text NOT NULL,
  chart_number integer NOT NULL,
  "timestamp" timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  donotsee boolean,
  CONSTRAINT pk_charts PRIMARY KEY (recid),
  CONSTRAINT chart_groupid UNIQUE (groupid),
  CONSTRAINT charts_ichart_key UNIQUE (chart_number)
);

CREATE TRIGGER update_modified
  BEFORE UPDATE ON charts
  FOR EACH ROW EXECUTE PROCEDURE update_modified();

我想用如下序列替换 chart_number:

CREATE SEQUENCE charts_chartnumber_seq START 16047;

这样通过trigger或者function，添加一个新的图表记录自动生成一个新的图表编号，按照升序排列。但是，现有的图表记录不能更改其图表编号，多年来，分配的图表编号一直在跳过。因此，在为新图表记录分配新图表编号之前，我需要确保"new"图表编号尚未被使用，并且任何具有图表编号的图表记录都没有分配不同的编号。

如何做到这一点？

Answer 1

考虑不这样做。首先阅读这些相关的答案:

• Gap-less sequence where multiple transactions with multiple tables are involved
• Compacting a sequence in PostgreSQL

如果您仍然坚持填补空白，这里有一个相当有效的解决方案:

1. 为避免在表格的大部分中搜索下一个缺失的 chart_number，创建一个包含所有当前间隙的辅助表格一次:

CREATE TABLE chart_gap AS
SELECT chart_number
FROM   generate_series(1, (SELECT max(chart_number) - 1  -- max is no gap
                           FROM charts)) chart_number
LEFT   JOIN charts c USING (chart_number)
WHERE  c.chart_number IS NULL;

2. 将 charts_chartnumber_seq 设置为当前最大值并将 chart_number 转换为实际的 serial/strong>专栏:

SELECT setval('charts_chartnumber_seq', max(chart_number)) FROM charts;

ALTER TABLE charts
   ALTER COLUMN chart_number SET NOT NULL
 , ALTER COLUMN chart_number SET DEFAULT nextval('charts_chartnumber_seq');

ALTER SEQUENCE charts_chartnumber_seq OWNED BY charts.chart_number; 

详细信息:

• How to reset postgres' primary key sequence when it falls out of sync?
• Safely and cleanly rename tables that use serial primary key columns in Postgres?

3. 当 chart_gap 不为空时，从那里获取下一个 chart_number。要在不让事务等待的情况下解决可能的并发事务竞争条件，请使用建议锁:

WITH sel AS (
   SELECT chart_number, ...  -- other input values
   FROM   chart_gap
   WHERE  pg_try_advisory_xact_lock(chart_number)
   LIMIT  1
   FOR    UPDATE
   )
, ins AS (
   INSERT INTO charts (chart_number, ...) -- other target columns
   TABLE sel 
   RETURNING chart_number
   )
DELETE FROM chart_gap c
USING  ins i
WHERE  i.chart_number = c.chart_number;

或者，Postgres 9.5 或更高版本有方便的FOR UPDATE SKIP LOCKED 使这更简单和更快:

...
   SELECT chart_number, ...  -- other input values
   FROM   chart_gap
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
...

详细解释:

• Postgres UPDATE ... LIMIT 1

检查结果。填写完所有行后，将返回受影响的 0 行。 (您可以使用 IF NOT FOUND THEN ... checkin plpgsql)。然后切换到一个简单的INSERT:

   INSERT INTO charts (...)  -- don't list chart_number
   VALUES (...);  --  don't provide chart_number