16

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.

Improve this question

5 Answers 5

Reset to default
28

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1, and the same deferrable initially deferred for foreign keys, uniques and exclude constraints.
Thanks. I didn't realise that the DEFERRABLE could be set on constraint triggers. This solves my question best I think.
If they have this working, why in the world do they not allow CHECK() constraints to be deferrable? Well, good 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));
Improve this answer

4 Comments

Please read the description again. It's about inserting the user without inserting it into user_in_group. Not about deleting from user_in_group or using invalid references.
@Appelsien S: Please read my answer again. I pointed out immediately after I added the example that my constraint was working the wrong way and now have fixed it so that orphan users are not allowed (while still forbidding bad user_in_group entries—not forbidding those would be much simpler to create tables for since no alter table would be required).
In your revised verison, user_id and group_id in foousergrps are made unique. This is ofcourse not desired: foousergrps is a N-to-N relation. You made it a 1-1 relation.
@Appelsein: I'll have to chalk that up to unexpressed explicit requirements. It answers the question as written. It is very annoying that postgresql requires the unique, but doing so is not fatal. You could create two materialized view tables (meaning updated by triggers on foousergrps) foousergrpsuniqueusers and foousergrpsuniquegroups which contain the unique list of users and groups and have the foreign key reference those shadow tables. A check constraint with a function might work but is not deferrable (meaning you cannot have the reverse foreign key and must add a usergroup first)
1

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?

Improve this answer

7 Comments

View with update rule is cool idea, thanks! About your other suggestion, how could that work? What's the difference with executing the queries outside a procedure?
The idea of SP is to combine both inserts into single command - should it fail at any point (ie you have already inserted into users table but not into user_group) all actions will be rolled back and you won't end up with orphan record.
Ah alright. But by only using the SP, I cannot prevent a user from accessing the table directly?
Well, you could not give user(s) INSERT / UPDATE rights on those tables. Or, again, use update rules to throw away any direct updates, leaving only your special view or SP as means to insert new users.
You can define functions (stored procedures) to run either under the permissions of the invoker or under the permissions of the owner. That's part of PostgreSQL's CREATE FUNCTION syntax.
|
0

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.

Improve this answer

1 Comment

I know. The scenario described in the question cannot be done using these types of triggers. That's why I was looking for an indirect way. Do you happen to know one?
0

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)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.