pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



Message-ID: <BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl>
Permalink:  ../BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl/
Also on:    postgresql.org/message-id/BLU0-SMTP1653FF4F215A5C2BE7B8F44B9CF0@phx.gbl

 · 

reply

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