Обсуждение: Small doubt on update a partition when some rows need to move among partition

Поиск
Список
Период
Сортировка

Small doubt on update a partition when some rows need to move among partition

От
"movead.li@highgo.ca"
Дата:
Hello,

I am trying to handle the limit that we can't do a tuple move caused by BEFORE TRIGGER,
during which I get two doubt points:

The first issue:
In ExecBRUpdateTriggers() or ExecBRInsertTriggers() function why we need to check the
result slot after every trigger call. If we should check the result slot after all triggers are
called.

For example, we have a table t1(i int, j int), and a BEFORE INSERT TRIGGER on t1 make i - 1, 
and another BEFORE INSERT TRIGGER on t1 make i + 1. If the first trigger causes a partition
move, then the insert query will be interrupted. However, it will not change partition after
all triggers are called.

The second issue:
I read the code for partition move caused by an update, it deletes tuple in an old partition
and inserts a new tuple in a partition. But during the insert, it triggers the trigger on the new
partition, so the result value may be changed again, I want to know if it's intended way? In
my mind, if an insert produced by partition move should not trigger before trigger again.


I make an initial patch as my thought, sorry if I missing some of the historical discussion.


Regards,
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
EMAIL: mailto:movead(dot)li(at)highgo(dot)ca
Вложения

Re: Small doubt on update a partition when some rows need to move among partition

От
Ashutosh Bapat
Дата:
On Thu, Aug 20, 2020 at 5:22 PM movead.li@highgo.ca <movead.li@highgo.ca> wrote:
>
> Hello,
>
> I am trying to handle the limit that we can't do a tuple move caused by BEFORE TRIGGER,
> during which I get two doubt points:
>
> The first issue:
> In ExecBRUpdateTriggers() or ExecBRInsertTriggers() function why we need to check the
> result slot after every trigger call. If we should check the result slot after all triggers are
> called.
>
> For example, we have a table t1(i int, j int), and a BEFORE INSERT TRIGGER on t1 make i - 1,
> and another BEFORE INSERT TRIGGER on t1 make i + 1. If the first trigger causes a partition
> move, then the insert query will be interrupted. However, it will not change partition after
> all triggers are called.

This was discussed at
https://www.postgresql.org/message-id/20200318210213.GA9781@alvherre.pgsql.

-- 
Best Wishes,
Ashutosh Bapat