postgres-xc-developers

[Postgres-xc-developers] COPY and BR triggers

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

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.

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

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

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).

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.

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

[Ashutosh B. <ash...@en...>]

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?


>
> 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

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

[鈴木 幸市 <ko...@in...>]

I'm afraid it's an XC restriction.

Regards;
---
Koichi Suzuki



On 2013/05/17, at 13:06, 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?
>  
> 
> 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
> ------------------------------------------------------------------------------
> 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

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

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

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
>

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

[Ashutosh B. <ash...@en...>]

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

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

[鈴木 幸市 <ko...@in...>]

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

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

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
>
>
>

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

[鈴木 幸市 <ko...@in...>]

I understood.

Maybe we must revise COPY manual about the restriction from user's point of view.

Regards;
---
Koichi Suzuki



On 2013/05/17, at 14:44, Amit Khandekar <ami...@en...> wrote:

> 
> 
> 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
> 
>