[POC]Enable tuple change partition caused by BEFORE TRIGGER

From: "movead(dot)li(at)highgo(dot)ca" <movead(dot)li(at)highgo(dot)ca>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: "Ashutosh Bapat" <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, "Amit Langote" <amitlangote09(at)gmail(dot)com>, "Alvaro Herrera" <alvherre(at)2ndquadrant(dot)com>, "Ahsan Hadi" <ahsan(dot)hadi(at)gmail(dot)com>
Subject: [POC]Enable tuple change partition caused by BEFORE TRIGGER
Date: 2020-08-21 07:57:42
Message-ID: 20200821155540390217109@highgo.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello hackers,

Currently, if BEFORE TRIGGER causes a partition change, it reports an error 'moving row
to another partition during a BEFORE FOR EACH ROW trigger is not supported' and fails
to execute. I want to try to address this limitation and have made an initial patch to get
feedback from other hackers.

The implemented approach is similar to when a change partition caused by an UPDATE
statement. If it's a BEFORE INSERT TRIGGER then we just need to insert the row produced
by a trigger to the new partition, and if it's a BEFORE UPDATE TRIGGER we need to delete
the old tuple and insert the row produced by the trigger to the new partition.

In current BEFORE TRIGGER implementation, it reports an error once a trigger result out
of current partition, but I think it should check it after finish all triggers call, and you can
see the discussion in [1][2]. In the attached patch I have changed this rule, I check the
partition constraint only once after all BEFORE TRIGGERS have been called. If you do not
agree with this way, I can change the implementation.

And another point is that when inserting to new partition caused by BEFORE TRIGGER,
then it will not trigger the BEFORE TRIGGER on a new partition. I think it's the right way,
what's more, I think the UPDATE approach cause partition change should not trigger the
BEFORE TRIGGERS too, you can see discussed on [1].

[1]https://www.postgresql.org/message-id/2020082017164661079648%40highgo.ca
[2]https://www.postgresql.org/message-id/20200318210213.GA9781@alvherre.pgsql

Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca

Attachment Content-Type Size
enable_partition_change_by_BEFORE_TRIGGER.patch application/octet-stream 21.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Yugo NAGATA 2020-08-21 08:23:20 Re: Implementing Incremental View Maintenance
Previous Message Peter Smith 2020-08-21 07:43:54 Re: proposal - function string_to_table