在 postgresql 中触发 ROLLBACK 事件 [英] ROLLBACK event triggers in postgresql

问题描述

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

I know it may sound odd but is there any way I can call my trigger on ROLLBACK event in a table? I was going through postgresql triggers documentation, there are events only for CREATE, UPDATE, DELETE and INSERT on table.

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

My requirement is on transaction ROLLBACK my trigger will select last_id from a table and reset table sequence with value = last_id + 1; in short I want to preserve sequence values on rollback.

任何形式的想法和反馈将不胜感激！

Any kind of ideas and feed back will be appreciated guys!

推荐答案

您不能为此使用序列.您需要一个序列化点，所有 插入必须通过该点 - 否则无法保证无间隙"属性.您还需要确保永远不会从该表中删除任何行.

You can't use a sequence for this. You need a single serialization point through which all inserts have to go - otherwise the "gapless" attribute can not be guaranteed. You also need to make sure that no rows will ever be deleted from that table.

序列化还意味着只有一个事务可以向该表中插入行 - 所有其他插入都必须等到前一个"插入已提交或回滚.

The serialization also means that only a single transaction can insert rows into that table - all other inserts have to wait until the "previous" insert has been committed or rolled back.

如何实现这一点的一种模式是拥有一个存储序列"数字的表格.让我们假设我们需要这个发票号码，因为法律原因必须是无间隙的.

One pattern how this can be implemented is to have a table where the the "sequence" numbers are stored. Let's assume we need this for invoice numbers which have to be gapless for legal reasons.

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

So we first create the table to hold the "current value":

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');

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

Now we need a function that will generate the next number but that guarantees that no two transactions can obtain the next number at the same time.

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 序列的行现在被锁定并且没有其他事务可以更新该值.如果调用事务回滚，则对序列计数器的更新也是如此.如果它被提交，新值将被持久化.

The function will increment the counter and return the incremented value as a result. Due to the update the row for the sequence is now locked and no other transaction can update that value. If the calling transaction is rolled back, so is the update to the sequence counter. If it is committed, the new value is persisted.

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

To ensure that every transaction uses the function, a trigger should be created.

创建有问题的表:

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

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

Now create the trigger function and the trigger:

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();

现在，如果一笔交易这样做:

Now if one transaction does this:

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

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

The new number is generated. A second transaction then needs to wait until the first insert is committed or rolled back.

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

As I said: this solution is not scalable. Not at all. It will slow down your application massively if there are a lot of inserts into that table. But you can't have both: a scalable and correct implementation of a gapless sequence.

我也很确定存在上述代码未涵盖的边缘情况.所以很可能你仍然会出现差距.

I'm also pretty sure that there are edge case that are not covered by the above code. So it's pretty likely that you can still wind up with gaps.

这篇关于在 postgresql 中触发 ROLLBACK 事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！