Thread: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.
BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.
From
maxim.boguk@gmail.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM1MApMb2dnZWQgYnk6ICAg ICAgICAgIE1ha3N5bSBCb2d1awpFbWFpbCBhZGRyZXNzOiAgICAgIG1heGlt LmJvZ3VrQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS40Ck9w ZXJhdGluZyBzeXN0ZW06ICAgTGludXgKRGVzY3JpcHRpb246ICAgICAgICAK CkR1cmluZyBkZXZlbG9waW5nIHRoZSBkYXRhYmFzZSBzdHJ1Y3R1cmUgbWln cmF0aW9uIHdpdGggbWF4aW11bQpjb21wYXRpYmlsaXR5IGZvciBhbiBvdXRz aWRlIGFwcGxpY2F0aW9uIGNvZGUgKGEgbG90IG9mIHZpZXdzIHdpdGggaW5z dGVhZApvZiB0cmlnZ2VycyB0byB0cmFuc3BhcmVudGx5IHJlc3RydWN0dXJl IHVuZGVybHlpbmcgZGF0YSksIEkgZm91bmQgdGhhdCBvbmUKY3JpdGljYWwg KGZvciBtZSkgZmVhdHVyZSBtaXNzaW5nLg0KDQpJIGV4cGVjdGVkIHRoYXQg SSBzaG91bGQgYmUgcG9zc2libGUgdG8gQ09QWSBkaXJlY3RseSBpbnRvIGEg VklFVyB3aXRoCklOU1RFQUQgT0YgSU5TRVJUIHRyaWdnZXIgb24gaXQuDQpC dXQgcmVhbGl0eSBiaXRlIG1lIGFnYWluLg0KDQpUZXN0IGNhc2U6DQoNCmNy ZWF0ZSB0YWJsZSB0dHQoaWQgc2VyaWFsLCBuYW1lIHRleHQpOw0KY3JlYXRl IHZpZXcgdHR0X3YgQVMgc2VsZWN0ICcnOjp0ZXh0IEFTIHN0cjsNCkNSRUFU RSBGVU5DVElPTiB0Zl90dHQoKSBSRVRVUk5TIHRyaWdnZXIgQVMgJHRmX3R0 dCQNCkJFR0lODQpJTlNFUlQgSU5UTyB0dHQgKG5hbWUpIFZBTFVFUyAoTkVX LnN0cik7DQpSRVRVUk4gTlVMTDsNCkVORDsNCiR0Zl90dHQkIExBTkdVQUdF IHBscGdzcWw7DQpDUkVBVEUgVFJJR0dFUiB0X3R0dF92IElOU1RFQUQgT0Yg SU5TRVJUIE9OIHR0dF92IEZPUiBFQUNIIFJPVyBFWEVDVVRFClBST0NFRFVS RSB0Zl90dHQoKTsNCkNPUFkgdHR0X3YgRlJPTSBzdGRpbjsNClNvbWUgc3Ry aW5nDQpBbm90aGVyIHN0cmluZw0KXC4NCl5DDQoNCkVSUk9SOiAgY2Fubm90 IGNvcHkgdG8gdmlldyAidHR0X3YiDQoNClVuZm9ydHVuYXRlbHkgYXBwbGlj YXRpb24gdXNlIENPUFkgdG8gYmF0Y2ggbG9hZCBpbiBsb3QgcGxhY2VzLg0K SXMgdGhpcyBhIGJ1Zz8gTWlzc2luZyBmZWF0dXJlPyBXb3JrIGFzIGRlc2ln bmVkPw0KDQpQUzogaWYgaXQgaGFkIGJlZW4gYWxyZWFkeSBkaXNjdXNzZWQg LSBzb3JyeSwgSSB0cmllZCB0byBzZWFyY2ggbWFpbCBsaXN0CmFyY2hpdmUg YnV0IGZvdW5kIG5vdGhpbmcgcmVsZXZhbnQuDQoNCk1ha3N5bQ0KDQoKCg==
Re: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.
From
Haribabu Kommi
Date:
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
Attachment
Re: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.
From
Dilip Kumar
Date:
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 ? -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com