From: Haifeng Liu <liuhaifeng@live.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: How to make data changes inside trigger function visible to the top level statement ?
Date: Mon, 6 Aug 2012 18:50:47 +0800
Message-ID: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl> (raw)
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@postgresql.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@postgresql.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@ringerc.id.au>)
id 1SyN9S-00017J-6o
for pgsql-admin@postgresql.org; Mon, 06 Aug 2012 13:15:22 +0000
Received: by pbbrr13 with SMTP id rr13so2579387pbb.19
for <pgsql-admin@postgresql.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.9000507@ringerc.id.au>
Date: Mon, 06 Aug 2012 21:14:57 +0800
From: Craig Ringer <ringerc@ringerc.id.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@live.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: How to make data changes inside trigger function visible
to the top level statement ?
References: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl>
In-Reply-To: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: liuhaifeng@live.com
Subject: Re: How to make data changes inside trigger function visible to the top level statement ?
In-Reply-To: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl>
References: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl>
* 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