Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Denis Feklushkin <denis(dot)feklushkin(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: What order of steps of the postgres when you change information in the table?
Date: 2009-10-31 18:31:52
Message-ID: 20091031112738.M58829@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

Responses

pgsql-general by date

Next:From: Sam MasonDate: 2009-10-31 19:07:00
Subject: Re: Absolute value of intervals
Previous:From: Jeff DavisDate: 2009-10-31 16:39:26
Subject: Re: When was a Function Added?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group