PostgreSQL中的ROLLBACK事件触发器 [英] ROLLBACK event triggers in postgresql

问题描述

我知道这听起来很奇怪，但是有什么方法可以在表的ROLLBACK事件上调用触发器？我正在查看postgresql触发器文档，桌上只有CREATE，UPDATE，DELETE和INSERT事件。一个表和重置表序列，其值= last_id + 1;简而言之，我想保留回滚时的序列值。

任何种类的想法和反馈都将受到赞赏！

解决方案

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

序列化还意味着只有一个事务可以在该表中插入行-所有其他插入都必须等待，直到上一个插入已提交或回滚为止。

如何实现此目的的一种模式是有一个表，其中存储序列号。假设我们需要发票号，但由于法律原因，发票号必须完整。

因此，我们首先创建表以保存当前值：

 创建表slow_sequence 
（
 seq_name varchar（100）非空主键，
 current_value整数非空默认值0 
）; 
 
-为发票
创建一个序列，将其插入slow_sequence值（发票）中； 
  

现在，我们需要一个函数，该函数将生成下一个数字，但要保证没有两个事务可以获取下一个号码在同一时间。

 创建或替换函数next_number（p_seq_name text）
返回整数
作为
 $$ 
 update slow_sequence 
 set current_value = current_value + 1 
其中seq_name = p_seq_name 
返回current_value; 
 $$ 
语言sql; 
  

该函数将增加计数器并返回增加后的值。由于 update 的原因，序列的行现已锁定，并且其他任何事务都无法更新该值。如果调用事务回滚，则对序列计数器的更新也将回滚。如果已提交，则新值将保留。

为确保每个事务使用该功能，应创建一个触发器。

创建相关表格：

 创建表格发票
（
 invoice_number整数不为空主键，
 customer_id整数不为空，
到期日期不为
）； 
  

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

 创建或替换函数f_invoice_trigger（）
返回触发器
，因为
 $$ 
以
开头-数字为无条件赋值，这样就不会
了-通过提供特定的数字
 new.invoice_number：= next_number（'invoice'）;可以防止这种情况。 
返回新值； 
结尾； 
 $$ 
语言plpgsql; 
 
创建触发器invoice_trigger 
，然后在每行插入到发票
上
执行过程f_invoice_trigger（）; 
  

现在，如果一项交易这样做：

 插入发票（customer_id，到期日期）
值（42，日期 2015-12-01）； 
  

将生成新数字。然后，第二事务需要等待，直到第一次插入被提交或回滚为止。

---

正如我所说：此解决方案不可扩展。一点也不。如果在该表中有很多插入，它将大大减慢您的应用程序的速度。但是你不能同时拥有：可伸缩的和正确实现无间隙序列。

我也很确定有优势上述代码未涵盖的情况。因此，您很有可能仍然存在差距。

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.

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;

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 the table in question:

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屋！