Re: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.

Поиск
Список
Период
Сортировка
От Haribabu Kommi
Тема Re: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.
Дата
Msg-id CAJrrPGdfcr3wr42g7SKN1DvuEyPfxBgX06DiyntBcgsK=E5r_Q@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.  (maxim.boguk@gmail.com)
Ответы Re: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.  (Dilip Kumar <dilipbalaut@gmail.com>)
Список pgsql-bugs


On Mon, Oct 3, 2016 at 10:53 PM, <maxim.boguk@gmail.com> wrote:
The following bug has been logged on the website:

Bug reference:      14350
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.5.4
Operating system:   Linux
Description:

During developing the database structure migration with maximum
compatibility for an outside application code (a lot of views with instead
of triggers to transparently restructure underlying data), I found that one
critical (for me) feature missing.

I expected that I should be possible to COPY directly into a VIEW with
INSTEAD OF INSERT trigger on it.
But reality bite me again.

Test case:

create table ttt(id serial, name text);
create view ttt_v AS select ''::text AS str;
CREATE FUNCTION tf_ttt() RETURNS trigger AS $tf_ttt$
BEGIN
INSERT INTO ttt (name) VALUES (NEW.str);
RETURN NULL;
END;
$tf_ttt$ LANGUAGE plpgsql;
CREATE TRIGGER t_ttt_v INSTEAD OF INSERT ON ttt_v FOR EACH ROW EXECUTE
PROCEDURE tf_ttt();
COPY ttt_v FROM stdin;
Some string
Another string
\.
^C

ERROR:  cannot copy to view "ttt_v"

Unfortunately application use COPY to batch load in lot places.
Is this a bug? Missing feature? Work as designed?

PS: if it had been already discussed - sorry, I tried to search mail list
archive but found nothing relevant.

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.

Regards,
Hari Babu
Fujitsu Australia
Вложения

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Huan Ruan
Дата:
Сообщение: Re: BUG #14319: Logical decoding dropping statements in subtransactions
Следующее
От: "Zuk, Kacper"
Дата:
Сообщение: Re: BUG #14329: libpq doesn't send complete client certificate chain on first SSL connection