Rollback Event Triggers in Postgresql

ROLLBACK event triggers in postgresql

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.

Trigger order of execution

From the docs:

Statement-level BEFORE triggers naturally fire before the statement starts to do anything, while statement-level AFTER triggers fire at the very end of the statement.

http://www.postgresql.org/docs/9.4/interactive/trigger-definition.html

In other words, a before statement trigger will be run just before the statement executes, and an after statement trigger will be run just after the statement finishes executing.

Statement level triggers can't be constraint triggers, and only constraint triggers are deferrable, so there's no way to postpone the trigger until the end of the transaction.

If more than one trigger needs to run at a given time, they are run
in the alphabetical order of their name.

Creating multiple event triggers inside one trigger postgres

Yes, you can do that.

If you write your trigger in PL/pgSQL, you will have the special variable TG_OP defined. The values are INSERT, UPDATE, DELETE or TRUNCATE, based on what operation caused the trigger to run.

See the documentation for details.

AFTER INSERT trigger in separate transaction?

All PostgreSQL triggers execute in the same transaction as the transaction that has triggered them.

Edit: You can also use LISTEN + NOTIFY to send a message from your trigger to a code that executes outside of the transaction. In that case, the message will only be delivered at the point of a successful commit. Errors in listeners will not roll back the triggering transaction.

Perform action before exception call in Postgres trigger function

You can trap errors / catch exceptions.

In the EXCEPTION block you can do anything else, like INSERT into another table. Afterwards you could re-raise the exception to propagate out, but that would roll back the whole transaction including the INSERT to the log table (unless the exception is wrapped and caught in an outer function).

You could:

• use tricks like a dblink call to emulate an autonomous transaction, which isn't undone, when the wrapping transaction is rolled back. Related:

  • How to use (install) dblink in PostgreSQL?
  • dblink can't update a table on the same database in an after UPDATE trigger
  • Does Postgres support nested or autonomous transactions?

• RAISE a NOTICE or WARNING additionally, which also isn't undone by ROOLBACK.

• RAISE a different EXCEPTION with your own text.

Alternatively, you can just cancel the row that triggered the trigger function and not raise an exception. Everything else in the transaction goes through normally.

Assuming this is a trigger ON UPDATE and you have another table with identical structure to write failed INSERTs to:

CREATE OR REPLACE FUNCTION emp_stamp()
  RETURNS trigger AS
$func$
BEGIN
    -- Check that empname and salary are given
    IF NEW.empname IS NULL THEN
         RAISE EXCEPTION 'empname cannot be null';
    END IF;

    IF ...

    RETURN NEW;    -- regular end

EXCEPTION WHEN others THEN  -- or be more specific
    INSERT INTO log_tbl VALUES (NEW.*); -- identical table structure
    RETURN NULL;   -- cancel row
END
$func$ LANGUAGE plpgsql;

Note that NEW contains the state of the row right before the exception occurred, including previous successful statements in the same function.

Trigger:

CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

---

Related Topics