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

在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;

以便通过触发或函数，添加新的图表记录自动按升序生成新的图表编号。但是，现有的海图记录不能更改其海图编号，并且多年来一直存在分配海图编号的跳过。因此，在为新图表记录分配新图表编号之前，我需要确保"新"图表编号尚未被使用，并且任何带有图表编号的图表记录都没有分配不同的编号。

如何做到这一点?

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

涉及多个表的多个事务的无间隙序列
压缩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 列:

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;

细节:

如何重置postgresql '不同步时的主键顺序?
安全，干净地重命名表，使用串行主键列在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…限制1

检查结果。一旦所有行都被填满，这将返回0个受影响的行。(你可以用IF NOT FOUND THEN ...检查plpgsql)。然后切换到简单的INSERT:

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

在PostgreSQL中，SEQUENCE确保了您提到的两个要求，即:

没有重复
一旦分配就不能更改

但是由于SEQUENCE的工作方式(参见手册)，它不能确保不跳过。其中，首先想到的两个原因是:

SEQUENCE如何使用insert处理并发块(您也可以添加Cache的概念也使此不可能)
同时，用户触发的delete是一个不可控的方面，SEQUENCE不能自己处理。

在这两种情况下，如果你仍然不想跳过，(如果你真的知道你在做什么)你应该有一个单独的结构来分配id(而不是使用SEQUENCE)。基本上是一个系统，它有一个"可分配"id列表存储在一个表中，该表有一个以FIFO方式弹出id的函数。这应该允许你控制delete等。

但是，这应该尝试，只有当你真的知道你在做什么!人们自己不做序列是有原因的。有一些死角情况(例如并发插入)，而且很可能您对问题情况进行了过度设计，这些情况可能可以用更好/更清晰的方式解决。

序号通常没有意义，所以为什么要担心呢?但是如果你真的想要这个，那么按照下面这个繁琐的步骤来做。注意，不是有效;唯一有效的选择是忘记孔并使用序列。

为了避免在每次插入时扫描charts表，您应该扫描表一次，并将未使用的chart_number值存储在单独的表中:

CREATE TABLE charts_unused_chart_number AS
  SELECT seq.unused
  FROM (SELECT max(chart_number) FROM charts) mx,
       generate_series(1, mx(max)) seq(unused)
  LEFT JOIN charts ON charts.chart_number = seq.unused
  WHERE charts.recid IS NULL;

上面的查询生成一个从1到当前最大chart_number值的连续的数字序列，然后LEFT JOIN将charts表查找到它，并找到没有对应的charts数据的记录，这意味着该序列的值未作为chart_number使用。

接下来创建一个触发器，在charts表上的INSERT上触发。在触发器函数中，从上面步骤中创建的表中选择一个值:

CREATE FUNCTION pick_unused_chart_number() RETURNS trigger AS $$
BEGIN
  -- Get an unused chart number
  SELECT unused INTO NEW.chart_number FROM charts_unused_chart_number LIMIT 1;
  -- If the table is empty, get one from the sequence
  IF NOT FOUND THEN
    NEW.chart_number := next_val(charts_chartnumber_seq);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_charts_cn
BEFORE INSERT ON charts
FOR EACH ROW EXECUTE PROCEDURE pick_unused_chart_number();

容易。但是INSERT可能会因为一些其他触发器终止该过程或任何其他原因而失败。因此，您需要检查以确定chart_number确实被插入:

CREATE FUNCTION verify_chart_number() RETURNS trigger AS $$
BEGIN
  -- If you get here, the INSERT was successful, so delete the chart_number
  -- from the temporary table.
  DELETE FROM charts_unused_chart_number WHERE unused = NEW.chart_number;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_charts_verify
AFTER INSERT ON charts
FOR EACH ROW EXECUTE PROCEDURE verify_chart_number();

在某一点上，包含未使用图表号的表将为空，此时您可以(1)ALTER TABLE charts使用序列而不是chart_number的integer;(2)删除两个触发器;(三)未使用海图号的表;

虽然您想要的是可能的，但它不能仅使用SEQUENCE来完成，它需要表上的排他锁或重试循环才能工作。

你需要:

LOCK thetable IN EXCLUSIVE MODE
通过查询max ID找到第一个空闲ID，然后在generate_series上执行left join以找到第一个空闲条目。
如果有空闲条目，请插入
如果没有空闲条目，则调用nextval返回结果。

性能将非常糟糕，事务将被序列化。没有并发性。此外，除非LOCK是您运行的影响该表的第一件事，否则您将面临导致事务中止的死锁。

您可以通过使用AFTER DELETE .. FOR EACH ROW触发器来减少这种情况，该触发器通过INSERT将条目保存到跟踪空闲id的单列表中来跟踪您删除的条目。然后，您可以在default上的ID赋值函数中，将表中最低的ID SELECT用于该列，从而避免了显式表锁、generate_series上的left join和max调用的需要。事务仍然会在free id表上的锁上序列化。在PostgreSQL中，你甚至可以使用SELECT ... FOR UPDATE SKIP LOCKED来解决这个问题。所以如果你是在9.5上，你实际上可以使这个不可怕，尽管它仍然会很慢。

我强烈建议您直接使用SEQUENCE，而不是重新使用值。

相关内容

最新更新

热门标签：