Re: [Postgres-xc-developers] Shippability of statement triggers

[Amit K. <ami...@en...>]

On 10 May 2013 17:59, Michael Paquier <mic...@gm...> wrote:

>
>
>
> On Fri, May 10, 2013 at 2:59 PM, Ashutosh Bapat <
> ash...@en...> wrote:
>
>> Can you comment on this, since you are the one who implemented statement
>> triggers?
>>
> About the shippability of triggers, here is some food for brain:
> http://michael.otacoo.com/postgresql-2/triggers-in-a-cluster-database/
>
> In short, a trigger can be fired on a remote node only if:
> - the query that triggered it is FQSed
> - the fired procedure is immutable. There might be cases where a trigger
> with a stable procedure could be shippable but this would be dangerous...
> There are already some APIs I implemented that you can use if they are not
> used already (trigger.c or something in the shippability APIs of the
> optimizer I don't recall precisely).
>
> Just knowing that 99% of triggers do not use immutable functions is enough
> to shoot all the triggers on Coordinators, you will be bad performance for
> row triggers but if you don't do that data consistency is badly endangered.
> However for correctness you should open the open to immutable triggers
> being shippable.
>

Actually I don't want to go into general shippability of statements in
trigger context; that is another issue having a bigger scope. My point for
this mail thread is for this specific case: What to do about statement
triggers, whether we should indeed run *statement* triggers on datanode on
the basis that the trigger function is safe to be run on datanode. Here is
the key point I made :

"For a non-FQS'ed DML, a DML is executed on datanode for each row to be
processed. So if a user updates 10 rows with a non-shippable query, the
coordinator will execute a parameterized remote update query on datanode
for each of the 10 ctids found using the quals. And if we execute shippable
statement triggers on datanode, the statement trigger will be executed 10
times on datanode, which is not expected from the user. That's the reason
we should fire statement triggers always on coordinator regardless of
anything"

Let me know if you have any comments on this specific point.

 --
> Michael
>