From: Kevin Crain <kevin.crain1@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: Order of evaluation in triggers for checks on inherited table partitions
Date: Tue, 31 May 2011 07:46:34 -0700
Message-ID: <BANLkTikoGUX-aUaEH953fPZYy2wydNRC+g@mail.gmail.com> (raw)
In-Reply-To: <BANLkTi=bzsS1+BDjYmCJ_+U68HgNMipXmw@mail.gmail.com>
References: <BANLkTi=ZH6_MCYE=F=pevpK1S72jCDGLVA@mail.gmail.com>
<irvnif$o8n$4@reversiblemaps.ath.cx>
<BANLkTi=bzsS1+BDjYmCJ_+U68HgNMipXmw@mail.gmail.com>
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.crain1@gmail.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@xnet.co.nz> wrote:
>> On 2011-05-27, Kevin Crain <kevin.crain1@gmail.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kevin.crain1@gmail.com
Subject: Re: Re: Order of evaluation in triggers for checks on inherited table partitions
In-Reply-To: <BANLkTikoGUX-aUaEH953fPZYy2wydNRC+g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox