sql - postgresql 中的 ROLLBACK 事件触发器

Question

我知道这听起来可能很奇怪，但是有什么方法可以在表中的 ROLLBACK 事件上调用我的触发器吗？我正在浏览 postgresql 触发器文档，只有表上的 CREATE、UPDATE、DELETE 和 INSERT 事件。

我的要求是在事务 ROLLBACK 上，我的触发器将从表中选择 last_id 并使用 value = last_id + 1 重置表序列；简而言之，我想在回滚时保留序列值。

任何类型的想法和反馈都将不胜感激!

Answer 1

你不能为此使用序列。您需要一个单一的序列化点，所有 插入都必须通过它 - 否则无法保证“无缝”属性。您还需要确保不会从该表中删除任何行。

序列化还意味着只有一个事务可以将行插入该表 - 所有其他插入必须等到“先前”插入已提交或回滚。

如何实现这一点的一种模式是拥有一个存储“序列”数字的表。假设我们需要这个用于出于法律原因必须无缝的发票号。

所以我们首先创建表来保存“当前值”:

create table slow_sequence 
(
  seq_name        varchar(100) not null primary key,
  current_value   integer not null default 0
);

-- create a "sequence" for invoices
insert into slow_sequence values ('invoice');

现在我们需要一个函数来生成下一个数字，但要保证没有两个交易可以同时获得下一个数字。

create or replace function next_number(p_seq_name text)
  returns integer
as
$$
  update slow_sequence
     set current_value = current_value + 1
  where seq_name = p_seq_name
  returning current_value;
$$
language sql;

该函数将递增计数器并返回递增的值作为结果。由于 update，序列的行现在被锁定，没有其他事务可以更新该值。如果调用事务被回滚，那么序列计数器的更新也会被回滚。如果已提交，则会保留新值。

为确保每个 事务都使用该函数，应创建一个触发器。

创建有问题的表:

create table invoice 
(
  invoice_number integer not null primary key, 
  customer_id    integer not null,
  due_date       date not null
);

现在创建触发器函数和触发器:

create or replace function f_invoice_trigger()
  returns trigger
as
$$
begin
  -- the number is assigned unconditionally so that this can't 
  -- be prevented by supplying a specific number
  new.invoice_number := next_number('invoice');
  return new;
end;
$$
language plpgsql;

create trigger invoice_trigger
  before insert on invoice
  for each row
  execute procedure f_invoice_trigger();

现在如果一个交易这样做:

insert into invoice (customer_id, due_date) 
values (42, date '2015-12-01');

新号码生成。然后，第二个事务需要等待第一个插入被提交或回滚。

---

正如我所说:此解决方案不可扩展。一点也不。如果该表中有大量插入，它将大大降低您的应用程序的速度。但您不能同时拥有:可扩展的和无缝序列的正确实现。

我也很确定上面的代码没有涵盖一些边缘情况。因此，您很可能仍会出现差距。