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.