如何在PostgreSQL中有效检查序列中已使用和未使用的值 [英] How to check a sequence efficiently for used and unused values in PostgreSQL

问题描述

在PostgreSQL（9.3）中，我有一个表定义为：

In PostgreSQL (9.3) I have a table defined as:

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：

I would like to replace the chart_number with a sequence like:

CREATE SEQUENCE charts_chartnumber_seq START 16047;

因此，通过触发器或功能，添加新图表记录会自动以升序生成新图表编号。但是，任何现有图表记录都无法更改其图表编号，并且多年来，已分配的图表编号已被跳过。因此，在将新图表编号分配给新图表记录之前，我需要确保尚未使用新图表编号，并且任何具有图表编号的图表记录都不会分配其他编号。

So that by trigger or function, adding a new chart record automatically generates a new chart number in ascending order. However, no existing chart record can have its chart number changed and over the years there have been skips in the assigned chart numbers. Hence, before assigning a new chart number to a new chart record, I need to be sure that the "new" chart number has not yet been used and any chart record with a chart number is not assigned a different number.

这怎么办？

推荐答案

考虑不这样做。请首先阅读以下相关答案：

Consider not doing it. Read these related answers first:

• 无间隙序列，其中涉及具有多个表的多个事务


• < a href = https://dba.stackexchange.com/a/111824/3684>在PostgreSQL中压缩序列

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

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

If you still insist on filling in gaps, here is a rather efficient solution:

1。。下一个缺少的 chart_number 的表，创建一个具有所有当前差距的帮助器表， 一次 ：

1. To avoid searching large parts of the table for the next missing chart_number, create a helper table with all current gaps once:

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 列：

2. Set charts_chartnumber_seq to the current maximum and convert chart_number to an actual serial column:

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; 

详细信息：

• 当postgres的主键序列不同步时如何重置？


• 安全，干净地重命名在Postgres中使用串行主键列的表？

• 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 不为空时，获取下一个图表编号。
要解决 具有并发交易的可能竞争条件 ，而无需等待交易，请使用咨询锁：

3. While chart_gap is not empty fetch the next chart_number from there. To resolve possible race conditions with concurrent transactions, without making transactions wait, use advisory locks:

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 或更高版本具有方便的用于锁定更新跳过，以使其更简单，更快捷：

Alternatively, Postgres 9.5 or later has the handy FOR UPDATE SKIP LOCKED to make this simpler and faster:

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

详细说明：

• Postgres更新...限制1

• Postgres UPDATE ... LIMIT 1

检查结果。填满所有行后，将返回受影响的0行。 （如果未找到，则可以使用来登录plpgsql ... ）。然后切换到简单的 INSERT ：

Check the result. Once all rows are filled in, this returns 0 rows affected. (you could check in plpgsql with IF NOT FOUND THEN ...). Then switch to a simple INSERT:

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

这篇关于如何在PostgreSQL中有效检查序列中已使用和未使用的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！