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