Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 46A0F182A953 for ; Mon, 6 Aug 2012 07:51:22 -0300 (ADT) Received: from blu0-omc1-s2.blu0.hotmail.com ([65.55.116.13]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SyKu6-0006qs-5Q for pgsql-admin@postgresql.org; Mon, 06 Aug 2012 10:51:21 +0000 Received: from BLU0-SMTP165 ([65.55.116.8]) by blu0-omc1-s2.blu0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Mon, 6 Aug 2012 03:51:04 -0700 X-Originating-IP: [110.53.94.86] X-EIP: [OhgFmrVH3Cup1gtrp4IkiwYgwGV69vqS] X-Originating-Email: [liuhaifeng@live.com] Message-ID: Received: from [192.168.1.6] ([110.53.94.86]) by BLU0-SMTP165.blu0.hotmail.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.4675); Mon, 6 Aug 2012 03:51:03 -0700 From: Haifeng Liu Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: How to make data changes inside trigger function visible to the top level statement ? Date: Mon, 6 Aug 2012 18:50:47 +0800 To: "pgsql-admin@postgresql.org" MIME-Version: 1.0 (Mac OS X Mail 6.0 \(1485\)) X-Mailer: Apple Mail (2.1485) X-OriginalArrivalTime: 06 Aug 2012 10:51:03.0949 (UTC) FILETIME=[5FF693D0:01CD73C1] X-Pg-Spam-Score: 0.0 (/) X-Archive-Number: 201208/38 X-Sequence-Number: 39859 Hello, I am doing table partitioning, all is ok except that after executing 'inser= t' 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 corre= ct partition is the recommend way, and affected rows testing is also used w= idely. 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 t= op 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 ; 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 ) 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 ; 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=20120113; 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 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0 MIME-Version: 1.0 To: Haifeng Liu CC: "pgsql-admin@postgresql.org" Subject: Re: How to make data changes inside trigger function visible to the top level statement ? References: In-Reply-To: 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