Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jEzCJ-0003T5-Qo for pgsql-bugs@arkaria.postgresql.org; Thu, 19 Mar 2020 17:43:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jEzCI-0003im-Fp for pgsql-bugs@arkaria.postgresql.org; Thu, 19 Mar 2020 17:43:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jEzCI-0003id-2H for pgsql-bugs@lists.postgresql.org; Thu, 19 Mar 2020 17:43:10 +0000 Received: from mail-qv1-xf41.google.com ([2607:f8b0:4864:20::f41]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jEzCA-0001WL-Cu for pgsql-bugs@lists.postgresql.org; Thu, 19 Mar 2020 17:43:09 +0000 Received: by mail-qv1-xf41.google.com with SMTP id cz10so1486929qvb.0 for ; Thu, 19 Mar 2020 10:43:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:mime-version:content-disposition :content-transfer-encoding:in-reply-to:user-agent; bh=rxp6iifKlS1uwfuUEvmlIstdH0R+LCm6tLrcXiC0RYc=; b=tPiH7Jt1Cvu2goy3b6S30wRtRikY4iE5gWEj+DgeFdUeJpWYSp5Xnh4i6ZRkTrLh/r Fgfrop8CGgMBdIz0F02CopiNNLaA23B6E7wj7BKNqJAMfy40DD1GjdJqFfPR639jeokm /lQKVt25x4yq+7Haonv4ev7vROkwg7+rrpHW7EvwuZdqxKWbKnnmR50bXBAL579RrRxl R5GQlVl1xSflz9Tt2FDTnK95V8JfYgWMofps2MsizO4fHZvrMiR/PaMOeNhgCZvuF56b qQgfhm52tc5+5Z1+Zfd3iLrcNVhx+hztKP9vQ4ZcbBRGgvziC/vQvilsIjWYXF2uTqgn oxzA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:mime-version :content-disposition:content-transfer-encoding:in-reply-to :user-agent; bh=rxp6iifKlS1uwfuUEvmlIstdH0R+LCm6tLrcXiC0RYc=; b=ICMYH0WCXeXndgmK50RUZE1bp5q2xmCKLkuPgWX4U32OoWvVmiSX1H4S/VFT2DvVp1 C/plS3Rjn2glHjMrxQQzna4U/i55vf11EEVbUb/dAkAdnvAsJx5uVhO7JsWnl0PXBdpn FlIvLc4pfezGaxwIrP9B8h7P17+7Zl4dZ2BJbS6ugp62YZWEtD4025q+ZvcGE3taE0mR Y8r4wg5O4fAeIOmtstwFNsKmnKkxSlWDpRPOEdn5D6S/egC3HS3B+aJ2d/kGlO91IWVZ dzSyEHF1yYW7QIWkXjriKL0WSO3vCfzADk+131W/OXjsfPYmb95WyiCQVrmQdizQYFZn gqtQ== X-Gm-Message-State: ANhLgQ0nG2ft0gyQ1miOC5k7otd8bVKGT4H4ZqRMZFD4rwXsg07kijjQ hNR0UCBRhC//lr1yATBSVr83ifwVWbI= X-Google-Smtp-Source: ADFU+vu89igdYH70H3Vofgb5y/Zfg63q2rwQ73DRnQ5T1feHf7Hz1Gabkp3RazO3qY2EH4NLBUOQAw== X-Received: by 2002:ad4:5429:: with SMTP id g9mr4045128qvt.134.1584639779908; Thu, 19 Mar 2020 10:42:59 -0700 (PDT) Received: from nimloth.alvh.no-ip.org ([201.186.208.119]) by smtp.gmail.com with ESMTPSA id 68sm1960684qkh.75.2020.03.19.10.42.59 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 19 Mar 2020 10:42:59 -0700 (PDT) Received: by nimloth.alvh.no-ip.org (Postfix, from userid 1000) id 58DEC30089E; Thu, 19 Mar 2020 14:42:57 -0300 (-03) Date: Thu, 19 Mar 2020 14:42:57 -0300 From: Alvaro Herrera To: "Raschkowski, Michael" Cc: pgsql-bugs@lists.postgresql.org, Hartmut Bolten , Hannes Just Subject: Re: Trigger does not work after partitioning Message-ID: <20200319174257.GA5010@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <001c01d5fe0d$baddddb0$30999910$@auconet-it.com> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2020-Mar-19, Raschkowski, Michael wrote: > I am using Postgres 12.1 on Windows 10. > > I wrote the script for the partitioning of some tables and have > noticed that some triggers do not work after partitioning. In attached > example, you can see that the table test1 gets a trigger that sets the > field instancenbr to 1 after each insertion. Nevertheless, the field > instancenbr stays to have NULL-Value. This has never worked, regardless of partitioning. AFTER triggers cannot usefully change the affected row; if you want to do that, you need a BEFORE trigger. The "return NEW" line is useless in an AFTER trigger. BEFORE triggers are not supported with partitioned tables (except in the future Postgres 13, where I added that feature yesterday), but you can add the trigger to each partition and it should work correctly. Of course, you'll need to add the trigger manually on each partition you create or attach. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services