pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trigger does not work after partitioning
2+ messages / 2 participants
[nested] [flat]

* Trigger does not work after partitioning
@ 2020-03-19 16:44  Raschkowski, Michael <michael.raschkowski@auconet-it.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Raschkowski, Michael @ 2020-03-19 16:44 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Hartmut Bolten <hartmut.bolten@auconet-it.com>; Hannes Just <hannes.just@auconet-it.com>

Hello,

 

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.

 

Is it a bug or I have not taken something into account?

 

Thanks in advance!  

 


Viele Grüße / Kind regards,

Dr. Michael Raschkowski 
Software Architect

Auconet GmbH - ein Unternehmen der Beta Systems Gruppe
An den Treptowers 1, 12435 Berlin
Phone: +49 30 254 690-356
Fax: +49 30 254 690-199
Email:  <mailto:michael.raschkowski@auconet-it.com>
michael.raschkowski@auconet-it.com
 <http://www.auconet-it.com/; www.auconet-it.com


Mandatory Information for business emails according to German trade laws /
Pflichtangaben für geschäftliche E-mails gemäß Handelsgesetzbuch:

Auconet GmbH - ein Unternehmen der Beta Systems Gruppe
An den Treptowers 1
12435 Berlin
Germany
Phone: +49-(0)30-254 690-0
Fax: +49-(0))30-254 690-199
 <mailto:info@auconet-it.com> info@auconet-it.com
 <http://www.auconet-it.com/; www.auconet-it.com

Management / Geschäftsführer: Hartmut Bolten, Sebastian Zang
Legal form / Rechtsform: GmbH | Registered office / Sitz: Berlin
Commercial register / Handelsregister: Amtsgericht Charlottenburg HRB 141
887 B
VAT-ID / Ust-ID-Nr.: DE316422231 | Tax No. / St.-Nr.: 30/036/75431
Bank / Bankverbindung: Commerzbank AG, BIC: COBADEFF, IBAN: DE25 1004 0000
0230 9391 00

 

 

Attachments:

  error_part_trigger.sql (application/octet-stream, 928B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Trigger does not work after partitioning
@ 2020-03-19 17:42  Alvaro Herrera <alvherre@2ndquadrant.com>
  parent: Raschkowski, Michael <michael.raschkowski@auconet-it.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Alvaro Herrera @ 2020-03-19 17:42 UTC (permalink / raw)
  To: Raschkowski, Michael <michael.raschkowski@auconet-it.com>; +Cc: pgsql-bugs@lists.postgresql.org, Hartmut Bolten <hartmut.bolten@auconet-it.com>; Hannes Just <hannes.just@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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-03-19 17:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-19 16:44 Trigger does not work after partitioning Raschkowski, Michael <michael.raschkowski@auconet-it.com>
2020-03-19 17:42 ` Alvaro Herrera <alvherre@2ndquadrant.com>

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