CODEWITHSUNDEEP
sqlpostgresqltriggerstransactions
Vish021
Vish021

Reputation: 399

ROLLBACK event triggers in postgresql

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!

Upvotes: 0

Views: 1903

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 3

Related Questions