Re: What order of steps of the postgres when you change information in the table?

2009/10/30 Denis Feklushkin <denis(dot)feklushkin(at)gmail(dot)com>:
>
> Problem:
> It is necessary to synchronize the "users" table with an
> external storage of passwords (krb5)
>
> I made a trigger:
>
> CREATE TRIGGER "10_krb5"
>  AFTER INSERT OR UPDATE OR DELETE
>  ON users
>  FOR EACH ROW
>  EXECUTE PROCEDURE user2krb5_python();
>
> Everything works, except that when you delete a row from table
> "users" foreign keys checking occurs after this trigger. And in case
> of any problems with the referencing record is an exception,
> rollback is occured (this is ok), but the trigger user2krb5_python() was
> executed and the user from the external storage removed.
>
> Checking of foreign keys occurs after the "AFTER-trigger" is ok? Check
> of the primary key, unique, and other constraints occurs in the very
> beginning, I checked.

The common solution here is to put a record into a table and issue a
notify to a listening process to take that table and process it.
Since a notify only fires if the transaction completes without error,
and the entry in the table won't exist unless the transaction
completes, the external process won't be kicked off unless the things
are complete.