From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Raschkowski, Michael <michael.raschkowski@auconet-it.com>
Cc: pgsql-bugs@lists.postgresql.org, Hartmut Bolten <hartmut.bolten@auconet-it.com>
Cc: Hannes Just <hannes.just@auconet-it.com>
Subject: Re: Trigger does not work after partitioning
Date: Thu, 19 Mar 2020 14:42:57 -0300
Message-ID: <20200319174257.GA5010@alvherre.pgsql> (raw)
In-Reply-To: <001c01d5fe0d$baddddb0$30999910$@auconet-it.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@2ndquadrant.com, michael.raschkowski@auconet-it.com, hartmut.bolten@auconet-it.com, hannes.just@auconet-it.com
Subject: Re: Trigger does not work after partitioning
In-Reply-To: <20200319174257.GA5010@alvherre.pgsql>
* 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