How to make data changes inside trigger function visible to the top level statement ?

Hello,

I am doing table partitioning, all is ok except that after executing 'insert' sql statement I can't get affected rows, it always be 0. After searching on the documents, I found that row changes inside trigger function is not visible to the top level statement.

Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and affected rows testing is also used widely. pgAdmin tests the affected rows too, thus when I type a new row and click save button, it seems failed but actually succeed.

How can I make the row changes inside the trigger function visible to the top level statement?

Thank all in advance!
>From pgsql-admin-owner(at)postgresql(dot)org Mon Aug 6 10:15:26 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
by mail.postgresql.org (Postfix) with ESMTP id E42E7182A954
for <pgsql-admin(at)postgresql(dot)org>; Mon, 6 Aug 2012 10:15:23 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
by magus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <ringerc(at)ringerc(dot)id(dot)au>)
id 1SyN9S-00017J-6o
for pgsql-admin(at)postgresql(dot)org; Mon, 06 Aug 2012 13:15:22 +0000
Received: by pbbrr13 with SMTP id rr13so2579387pbb.19
for <pgsql-admin(at)postgresql(dot)org>; Mon, 06 Aug 2012 06:15:03 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=google.com; s 120113;
h=message-id:date:from:user-agent:mime-version:to:cc:subject
:references:in-reply-to:content-type:content-transfer-encoding
:x-gm-message-state;
bh=jP3WOoiPnIQEOCY/yTpn0Ae58oD1QxDKMk1GrgN2hTw=;
b=LAvYW1lc6AcGwm0lCqBP9hHnvFW8fK1iz2on/naewm0xvi3dfMU3hYticnK0XtfbAg
3ZIdEs/pKDtJmsJS22NFyb4uXu0obLcp407fAFzGI8AhryRWxWQ0ApQd92XZyRntxHXj
vZHixShBo/Ybd07Bb3XGrErrV8VB6a3iozQBpthZC9EQQIqPBl87vX7GPjnh+BdDn19c
IBWloqAtZ3OX5W1tLHhzCK05Ve7nJPMzz+Mh1wPc9ys6ZZnw+e39HRDJgUWZlyPMYnwA
4lW70YMHj/14E6+mYhW8NW7arbvOYGlnwubUjj0eJncR2dyekaIYX01NzDs9liVqFoSP
TN9w=
Received: by 10.68.129.168 with SMTP id nx8mr18875735pbb.112.1344258902961;
Mon, 06 Aug 2012 06:15:02 -0700 (PDT)
Received: from ayaki.localdomain (58-7-214-101.dyn.iinet.net.au. [58.7.214.101])
by mx.google.com with ESMTPS id ng8sm8958575pbc.13.2012.08.06.06.15.00
(version=SSLv3 cipher=OTHER);
Mon, 06 Aug 2012 06:15:02 -0700 (PDT)
Message-ID: <501FC351(dot)9000507(at)ringerc(dot)id(dot)au>
Date: Mon, 06 Aug 2012 21:14:57 +0800
From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Haifeng Liu <liuhaifeng(at)live(dot)com>
CC: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: How to make data changes inside trigger function visible
to the top level statement ?
References: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0(at)phx(dot)gbl>
In-Reply-To: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0(at)phx(dot)gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: ALoCoQkdLK5ITcdJN3Olw0KzkfqXL1ngXxIGdlSmkgXYTSjAmrmNWEyvABabkRaLs9nAzgjmlViE
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/39
X-Sequence-Number: 39860

On 08/06/2012 06:50 PM, Haifeng Liu wrote:
> Hello,
>
> I am doing table partitioning, all is ok except that after executing 'insert' sql statement I can't get affected rows, it always be 0. After searching on the documents, I found that row changes inside trigger function is not visible to the top level statement.
>
> Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and affected rows testing is also used widely. pgAdmin tests the affected rows too, thus when I type a new row and click save button, it seems failed but actually succeed.
>
> How can I make the row changes inside the trigger function visible to the top level statement?
You can't. If you need an affected row count, you need to insert
directly into the target partition, thus bypassing the trigger.

It's one of those things on the "wouldn't it be nice to fix" list in
PostgreSQL's partitioning support.

--
Craig Ringer