postgres - trigger before transaction commit

Question

I was wondering if it is indirectly possible to have a trigger executed just before the transaction is about to commit? In this trigger, I will do consistency checks and rollback the transaction if required.

For example, I have three tables:

users (id, name)
groups (id, name)
user_in_group (user_id, group_id)

I would like to create a trigger which verifies that a user is always part of a group. No orphan users are allowed. Each time an insert into users occurs, this trigger will verify that a correspondering insert into user_in_group also occured. If not, the transaction will not commit.

This cannot be done using a simple row- or statement- based trigger, since the above scenario requires two separate statements.

The other way around, when a delete from user_in_group happens, can be easily done by a row-based trigger.

Answer 1

Did you look into the CREATE CONSTRAINT TRIGGER, with the DEFERRABLE (INITIALLY DEFERRED) option?

Answer 2

Wouldn't the correct method really be to establish constraints into the database? That seems exactly what constraints are for. Add a foreign key constraint and a not null and it would seem you should be in business.

Now revised for symmetrical constraints:

drop table foousers cascade;
drop table foogroups cascade;
drop table foousergrps cascade;
create table foousers (id int primary key, name text);
create table foogroups  (id int primary key, name text);
create table foousergrps (user_id int unique references foousers not null, group_id int unique references foogroups not null);
alter table foogroups add foreign key (id) references foousergrps (group_id) deferrable initially deferred;
alter table foousers add foreign key (id) references foousergrps (user_id) deferrable initially deferred;
begin;
insert into foousers values (0, 'root');
insert into foousers values (1, 'daemon');
insert into foogroups values (0, 'wheel');
insert into foogroups values (1, 'daemon');
insert into foousergrps values (0,0);
insert into foousergrps values (1,1);
commit;

Forbidden:

insert into foousers values (2, 'bad');
insert into foousergrps values (2,2);

Example of (non-deferrable, boo) check function:

create table foousergrps (user_id int unique references foousers not null, group_id int not null);
create function fooorphangroupcheck(int) returns boolean as $$
declare
  gid alias for $1;
begin
  perform 1 from foousergrps where group_id = gid limit 1;
  if NOT FOUND then return false;
  end if;
  return true;
end;
$$
LANGUAGE 'plpgsql';
alter table foogroups add check (fooorphangroupcheck(id));

Answer 3

Lookind at the doc, there seems to be no such trigger option... so one way to achieve "no orphan users" rule would be to not allow direct insert into users and user_in_group tables. Instead create a view (which combines these tables, ie user_id, user_name, group_id) with a update rule which inserts data into right tables.

Or only allow inserting new users via stored procedure which takes all required data as inpud and thus doesn't allow users withoud group.

BTW, why do you have separate table for user and group relationship? Why not add group_id field into users table with FK / NOT NULL constraint?

Answer 4

From the docs . . .

Triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement.

Answer 5

You can use the sql WITH operator, like this:

WITH insert_user AS (
  INSERT INTO users(name) VALUES ('bla-bla-user') RETURNING id
)
INSERT INTO user_in_group(user_id, group_id) 
  SELECT id, 999 FROM insert_user UNION
  SELECT id, 888 FROM insert_user;

SELECT groups (id, name) user_in_group (user_id, group_id)