Horton

Thread

  1. triggers and inheritance tree

    Jaime Casanova <jaime@2ndquadrant.com> — 2012-03-28T06:21:26Z

    Hi,

i was trying to create triggers that redirect INSERT/UPDATE/DELETE
actions from parent to childs, but found that UPDATE/DELETE doesn't
get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE
aren't even fired.

I haven't tried with AFTER triggers to see if they are fired but i
tried on 8.4 to 9.1 and all of these have the same behaviour

attached is a simple contained test of this

PS: i'm hoping this is just me needed to sleep

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

  2. Re: triggers and inheritance tree

    Jaime Casanova <jaime@2ndquadrant.com> — 2012-03-28T13:16:46Z

    On Wed, Mar 28, 2012 at 1:21 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> Hi,
>
> i was trying to create triggers that redirect INSERT/UPDATE/DELETE
> actions from parent to childs, but found that UPDATE/DELETE doesn't
> get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE
> aren't even fired.
>

and of course, it has nothing to do with the inheritance tree. that
was just a coincidence.

the problem occurs the same with normal tables, but i can't find where
is the problem.
i suspect, though, that is in the comparison in TRIGGER_TYPE_MATCHES

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

  3. Re: triggers and inheritance tree

    Robert Haas <robertmhaas@gmail.com> — 2012-03-28T13:29:22Z

    On Wed, Mar 28, 2012 at 9:16 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> On Wed, Mar 28, 2012 at 1:21 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
>> Hi,
>>
>> i was trying to create triggers that redirect INSERT/UPDATE/DELETE
>> actions from parent to childs, but found that UPDATE/DELETE doesn't
>> get redirected. Actually, the triggers BEFORE UPDATE and BEFORE DELETE
>> aren't even fired.
>>
>
> and of course, it has nothing to do with the inheritance tree. that
> was just a coincidence.
>
> the problem occurs the same with normal tables, but i can't find where
> is the problem.
> i suspect, though, that is in the comparison in TRIGGER_TYPE_MATCHES

I think the problem is that the UPDATE or DELETE can only fire once a
matching row has been identified, so that OLD can be filled in
appropriately.  But in this case, the matching row gets found not in
the parent table, but in one of its child tables.  So any triggers on
the child table would fire, but triggers on the parent table will not.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  4. Re: triggers and inheritance tree

    Jaime Casanova <jaime@2ndquadrant.com> — 2012-03-28T14:46:18Z

    On Wed, Mar 28, 2012 at 8:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> I think the problem is that the UPDATE or DELETE can only fire once a
> matching row has been identified, so that OLD can be filled in
> appropriately.  But in this case, the matching row gets found not in
> the parent table, but in one of its child tables.  So any triggers on
> the child table would fire, but triggers on the parent table will not.
>

ah! and of course that makes a lot of sense...
how embarrasing! :(

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

  5. Re: triggers and inheritance tree

    Robert Haas <robertmhaas@gmail.com> — 2012-03-28T15:03:40Z

    On Wed, Mar 28, 2012 at 10:46 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> On Wed, Mar 28, 2012 at 8:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> I think the problem is that the UPDATE or DELETE can only fire once a
>> matching row has been identified, so that OLD can be filled in
>> appropriately.  But in this case, the matching row gets found not in
>> the parent table, but in one of its child tables.  So any triggers on
>> the child table would fire, but triggers on the parent table will not.
>
> ah! and of course that makes a lot of sense...
> how embarrasing! :(

If it's any consolation, when I initially looked at your example, I
couldn't see what was wrong with it, either.  After I ran it I figured
it out.  :-)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company