Re: [Postgres-xc-developers] COPY and BR triggers

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

On 17 May 2013 10:14, 鈴木 幸市 <ko...@in...> wrote:

> The background of the restriction is apparently PG restriction.   My point
> is does this issue will not happen in PG.
>

In PG, when queries are executed through triggers, those are initiated by
the backend, there is no client in the picture. So there is no need of any
client-server message exchange for executing triggers while the COPY
protocol is in progress.

For XC, the triggers are executed from the client (i.e. coordinator) so
there are client-server messages to be exchanged while the COPY is in
progress.

In XC, we catch such scenario in the coordinator itself ; we block any
messages to be sent to datanode while the COPY is in progress.

I am working on allowing statement triggers to be executed from
coordinator. That should be possible.

>
> Regards;
>  ---
> Koichi Suzuki
>
>
>
> On 2013/05/17, at 13:36, Ashutosh Bapat <ash...@en...>
> wrote:
>
> Ok, in that case, I don't think we have any other way but to convert COPY
> into INSERT between coordinator and datanode when the triggers are not
> shippable. I think this restriction applies only to the row triggers;
> statement triggers should be fine.
>
>
> On Fri, May 17, 2013 at 10:01 AM, Amit Khandekar <
> ami...@en...> wrote:
>
>>
>>
>> On 17 May 2013 09:36, Ashutosh Bapat <ash...@en...>wrote:
>>
>>>
>>>
>>>
>>> On Fri, May 17, 2013 at 9:27 AM, Amit Khandekar <
>>> ami...@en...> wrote:
>>>
>>>>
>>>>
>>>> On 15 May 2013 12:53, Amit Khandekar <ami...@en...>wrote:
>>>>
>>>>> In XC, the way COPY is implemented is that for each record, we read
>>>>> the whole line into memory, and then pass it to the datanode as-is. If
>>>>> there are non-shippable default column expressions, we evaluate the default
>>>>> values , convert them into output form, and append them to the data row.
>>>>>
>>>>> In presence of BR triggers, currently the ExecBRInsertTriggers() do
>>>>> not get called because of the way we skip the whole PG code block; instead
>>>>> we just send the data row as-is, optionally appending default values into
>>>>> the data row.
>>>>>
>>>>> What we need to do is; convert the tuple returned by ExecBRTriggers
>>>>> into text data row, but the text data should be in COPY format. This is
>>>>> because we need to send the data row to the datanode using COPY command, so
>>>>> it requires correct COPY format, such as escape sequences.
>>>>>
>>>>> For this, we need to call the function CopyOneRowTo() that is being
>>>>> used by COPY TO. This will make sure it will emit the data row in the COPY
>>>>> format. But we need to create a temporary CopyState because CopyOneRowTo()
>>>>> needs it. We can derive it from the current CopyState that is already
>>>>> created for COPY FROM. Most of the fields remain the same, except we need
>>>>> to re-assign CopyState->line_buf, and CopyState->rowcontext.
>>>>>
>>>>> This will save us from writing code to make sure the new output data
>>>>> row generated by BR triggers complies with COPY data format.
>>>>>
>>>>> I had already done similar thing for appending default values into the
>>>>> data row. We call functions like CopyAttributeOutCSV(), CopyInt32() to
>>>>> append the values to the data row in COPY format. There, we did not require
>>>>> CopyOneRow() because we did not require the complete row, we needed to
>>>>> append only a subset of columns to the existing data row.
>>>>>
>>>>> Comments/suggestions welcome.
>>>>>
>>>>
>>>> I have hit a dead end in the way I am allowing the BR triggers to
>>>> execute during COPY.
>>>>
>>>> It is not possible to send any non-COPY messages to the backend when
>>>> the client-server protocol is in COPY mode. Which means, it is not possible
>>>> to send any commands to the datanode when connection is in
>>>> DN_CONNECTION_STATE_COPY_IN state. When the trigger function executes an
>>>> SQL query, that query can't be executed because it's not possible to
>>>> exchange any non-copy messages, let alone sending a query to the backend
>>>> (i.e. datanode).
>>>>
>>>
>>> Is this an XC restriction or PG restriction?
>>>
>>
>> The above is a PG restriction.
>>
>> Not accpepting any other client messages during a COPY protocol is a PG
>> backend requirement. Not accepting  trigger queries from coordinator has
>> become an XC restriction as a result of the above PG protocol restriction.
>>
>>
>>>
>>>>
>>>> This naturally happens only for non-shippable triggers. If triggers are
>>>> executed on datanode, then this issue does not arise.
>>>>
>>>> We need to device some other means to support non-shippable triggers
>>>> for COPY. May be we would end up sending INSERT commands on the datanode
>>>> instead of COPY command, if there are non-shippable triggers. Each of the
>>>> data row will be sent as parameters to the insert query. This operation
>>>> would be slow, but possible.
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> AlienVault Unified Security Management (USM) platform delivers complete
>>>> security visibility with the essential security capabilities. Easily and
>>>> efficiently configure, manage, and operate all of your security controls
>>>> from a single console and one unified framework. Download a free trial.
>>>> http://p.sf.net/sfu/alienvault_d2d
>>>> _______________________________________________
>>>> 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
>>>
>>
>>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Postgres Database Company
> ------------------------------------------------------------------------------
> AlienVault Unified Security Management (USM) platform delivers complete
> security visibility with the essential security capabilities. Easily and
> efficiently configure, manage, and operate all of your security controls
> from a single console and one unified framework. Download a free trial.
>
> http://p.sf.net/sfu/alienvault_d2d_______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>
>