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

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

On 10 May 2013 16:00, Koichi Suzuki <koi...@gm...> wrote:

> I think this is a reasonable point to start with.
> However, in general, TRIGGER is an expensive action so I believe we should
> provide more sophisiticated optimization of TRIGGER action.   I understand
> this is not an issue of TRIGGER.   We need to look into rewriter, function
> shippability (current shippability is safe but not optimal -- I'd like to
> discuss this shortly), and statement shippability,  I mean partial statment
> shippability.
>
>
That's a long standing issue that we have been discussing as to what are
the various ways in which a user can make trigger functions execute safely
on datanode. Actually the performance impact is particularly critical for
row triggers, not so much for statement triggers since they execute only
once.

I undertand trigger action needs careful and sophisticated design and has
> complicated relationship with other component of query processing.
>

>
> Best;
>
> ----------
> Koichi Suzuki
>
>
> 2013/5/10 Amit Khandekar <ami...@en...>
>
>>
>>
>> On 10 May 2013 11:29, Ashutosh Bapat <ash...@en...>wrote:
>>
>>>
>>> Hi Amit,
>>> This looks fine from correctness perspective and may be ok for this
>>> release. I hope, we can still FQS the query and not ship the triggers.
>>>
>>
>> Yes, that's the idea. We handle statement triggers explicitly on
>> coordinator for FQS. We define a resultRelInfo in RemoteQuery if it is FQS,
>> and then call the BS and AS triggers in ExecInitRemoteQuery() and
>> ExecEndRemoteQuery() if resultRelInfo is set.
>>
>>
>> How would it affect performance?
>>>
>>
>> Since the query would still be FQSed even for non-shippable stmt trigger,
>> this does not impact performance. Stmt trigger would be fired only once.
>>
>> Michael,
>>
>>> Can you comment on this, since you are the one who implemented statement
>>> triggers?
>>>
>>>
>>> On Fri, May 10, 2013 at 10:33 AM, Amit Khandekar <
>>> ami...@en...> wrote:
>>>
>>>> We can consider applying the usual row-trigger rules of trigger
>>>> shippability to statement triggers:
>>>> If the trigger function is shippable, execute the trigger on datanode,
>>>> else on coordinator.
>>>>
>>>> It is not as trivial as it sounds though. 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. Is
>>>> this expected from the user ?
>>>>
>>>> From the user's perspective, the statement is executed once,  so the
>>>> statement trigger should be fired only once. Typical use case is that the
>>>> user queries need to be logged/audited. So we need to prevent firing
>>>> statement triggers on datanode for non-FQS'ed query. But should the user
>>>> define the stmt trigger function as immutable in such a case ? May be not
>>>> in this auditing scenario. But it is not very clear what would a shippable
>>>> statement trigger mean to the user exactly. If the function is really one
>>>> which does not access the database as per the immutable definition, then it
>>>> anyway does not matter how many times it gets executed on datanode for a
>>>> given statement.
>>>>
>>>> I think the solution is to *always* fire statement triggers on
>>>> *coordinator* regardless of shippability or whether it is FQS or non-FQS.
>>>> For FQS query, we need to explicitly fire stmt trigger before/after the
>>>> fqs'ed query node is executed, may be inside the ExecRemoteQuery() function
>>>> itself.
>>>>
>>>> Comments ?
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Learn Graph Databases - Download FREE O'Reilly Book
>>>> "Graph Databases" is the definitive new guide to graph databases and
>>>> their applications. This 200-page book is written by three acclaimed
>>>> leaders in the field. The early access version is available now.
>>>> Download your free book today! http://p.sf.net/sfu/neotech_d2d_may
>>>> _______________________________________________
>>>> Postgres-xc-developers mailing list
>>>> Pos...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>
>>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EntepriseDB Corporation
>>> The Postgres Database Company
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Learn Graph Databases - Download FREE O'Reilly Book
>> "Graph Databases" is the definitive new guide to graph databases and
>> their applications. This 200-page book is written by three acclaimed
>> leaders in the field. The early access version is available now.
>> Download your free book today! http://p.sf.net/sfu/neotech_d2d_may
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>