Re: Re: Order of evaluation in triggers for checks on inherited table partitions

Okay, I figured out what is going on. Even though I was running the
update on the master table the trigger was not being applied because
it was actually being ran using the child table where the record to be
updated resided. So the trigger function was being skipped and it was
running as an ordinary update, hence the error. In order to get this
to work I had to add a trigger for each child table as well to call my
update function trigger.

--Kevin Crain

On Tue, May 31, 2011 at 6:40 AM, Kevin Crain <kevin(dot)crain1(at)gmail(dot)com> wrote:
> Can procedural languages be used in rules?  I didn't see any examples
> in the documentation that suggested something like this could be done
> using rules.
>
> --Kevin Crain
>
> On Mon, May 30, 2011 at 2:21 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:
>> On 2011-05-27, Kevin Crain <kevin(dot)crain1(at)gmail(dot)com> wrote:
>>> I am trying to create a trigger on updates to a table that is
>>> partitioned.  The child tables are partitioned by month and include
>>> checks on a timestamp field.
>>
>>> However when I try to update an existing record with a
>>> timestamp that would place it in a child table different from the
>>> child table it is in I get an error due to the check on the child
>>> table it is currently in.  My best guess as to what is happening is
>>> that the trigger is evaluating the check before it evaluates the
>>> trigger function and thus cannot tell that the update to the original
>>> table should never take place.  I have included an example below.  The
>>> error that results is "new row for relation "t_foo_2011_6" violates
>>> check constraint "t_foo_2011_6_f_timestamp_check""
>>
>> the problem is the check is running before the trigger.
>> perhaps you can use a rule instead of a trigger?
>>
>> --
>> ⚂⚃ 100% natural
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>