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

On Sat, 31 Oct 2009, Denis Feklushkin wrote:

>
> 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.

If I remember correctly you're allowed to put an after trigger before or
after the constraint check for foreign keys based on the naming of the
trigger as the key is checked in a trigger. IIRC, with a name like
"10_..." it will compare lower so happens before the check and a name
like "krb5" it would come after.

However, I don't think you can currently have both the property that you
will never have a failure to commit after your external action runs and
that your external action can abort the transaction if the external action
fails.