Обсуждение: Re: [BUGS] BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.

On Tue, Oct 4, 2016 at 1:07 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> I think currently there is no handling of INSTEAD of triggers in the copy
> functionality.
>
> It didn't seem difficult to the support the same, until unless there are any
> problems for complext queries, so after adding the INSTEAD of triggers
> check and calling the ExecIRInsertTriggers function, the Copy is also
> working for the view.
>
> Attached is a POC patch of the same. I didn't checked all the possible
> scenarios.

We support insert into view in below 2 cases..

1. INSTEAD OF INSERT trigger
2. or an unconditional ON INSERT DO INSTEAD rule

In your patch we are supporting first one in COPY command, Will it not
be good to support second one also in COPY command ?

On Tue, Nov 1, 2016 at 12:40 PM, Haribabu Kommi
<kommi.haribabu@gmail.com> wrote:
> COPY command is treated as an UTILITY command, During the query
> processing, the rules are not applied on the COPY command, and in the
> execution of COPY command, it just inserts the data into the heap and
> indexes with direct calls.
>
> I feel supporting the COPY command for the case-2 is little bit complex
> and may not be that much worth.

I agree it will be complex..
>
> Attached is the updated patch with doc changes.

Now since we are adding support for INSTEAD OF TRIGGER in COPY FROM
command, It will be good that we provide a HINT to user if INSTEAD of
trigger does not exist, like we do in case of insert ?

INSERT case:
postgres=# insert into ttt_v values(7);
2016-11-01 14:31:39.845 IST [72343] ERROR:  cannot insert into view "ttt_v"
2016-11-01 14:31:39.845 IST [72343] DETAIL:  Views that do not select
from a single table or view are not automatically updatable.
2016-11-01 14:31:39.845 IST [72343] HINT:  To enable inserting into
the view, provide an INSTEAD OF INSERT trigger or an unconditional ON
INSERT DO INSTEAD rule.

COPY case:
postgres=# COPY ttt_v FROM stdin;
2016-11-01 14:31:52.235 IST [72343] ERROR:  cannot copy to view "ttt_v"
2016-11-01 14:31:52.235 IST [72343] STATEMENT:  COPY ttt_v FROM stdin;

On Thu, Nov 3, 2016 at 9:57 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> Thanks for your suggestion. Yes, I agree that adding a hint is good.
> Updated patch is attached with addition of hint message.
>
> 2016-11-03 14:56:28.685 AEDT [7822] ERROR:  cannot copy to view "ttt_v"
> 2016-11-03 14:56:28.685 AEDT [7822] HINT:  To enable copy to view, provide
> an INSTEAD OF INSERT trigger
> 2016-11-03 14:56:28.685 AEDT [7822] STATEMENT:  COPY ttt_v FROM stdin;

Okay, Patch in general looks fine to me. One cosmetic comments, IMHO
in PG we follow operator at end of the line, so move '&&' to end of
the previous line.

+ if (cstate->rel->rd_rel->relkind != RELKIND_RELATION
+ && (!cstate->rel->trigdesc ||
+ !cstate->rel->trigdesc->trig_insert_instead_row))

Meanwhile I will test it and give the feedback.

Haribabu Kommi <kommi.haribabu@gmail.com> writes:
> [ copy_to_view_3.patch ]

Pushed with cosmetic adjustments.