DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:content-type:content-transfer-encoding;
	b=kmjgJhNPBHxgMlMwyh8wpvrc7JSBZ1x4V3ndDdy5CujF1s/67dzatD4RsWjcKXCni1
	pUE3ChPYRpJy4OfAlw7aeBFS0NmuS3kh8wv3CKZs6k6+ZRpAC9rkbJxxc2ENGXzQv8ss
	tIrVmqFUwL7udOoiA21rbfxnzTeZQX7lhssjo=
MIME-Version: 1.0
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>
From: Kevin Crain <kevin.crain1@gmail.com>
Date: Tue, 31 May 2011 07:46:34 -0700
Message-ID: <BANLkTikoGUX-aUaEH953fPZYy2wydNRC+g@mail.gmail.com>
Subject: Re: Re: Order of evaluation in triggers for checks on inherited
	table partitions
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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? =C2=A0I didn't see any example=
s
> 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. =C2=A0The child tables are partitioned by month and includ=
e
>>> 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. =C2=A0My best guess as to what is happening i=
s
>>> 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. =C2=A0I have included an example below. =
=C2=A0The
>>> 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?
>>
>> --
>> =E2=9A=82=E2=9A=83 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
>>
>