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

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.


(sorry for my English)

2009/10/30 Denis Feklushkin <denis.feklushkin@gmail.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.

On Fri, 30 Oct 2009 23:29:49 -0600
Scott Marlowe <scott.marlowe@gmail.com> wrote:

> 2009/10/30 Denis Feklushkin <denis.feklushkin@gmail.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.

If an error occurs in an external process? Transaction will not
be interrupted because it will be finished.

It seems to me, checking of the foreign key after all
triggers is meaningless

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.

Stephan Szabo wrote:

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

Yeah. That's what two-phase commit is for - but I don't see how you
could use it in a trigger.

--
Craig Ringer