Thread: UPSERT on a view
Why does the following code raise an error?
CREATE TABLE ttest (x integer);
CREATE VIEW vtest AS SELECT x FROM ttest;
CREATE FUNCTION vtest_insert() RETURNS trigger LANGUAGE plpgsql AS $$
RAISE 'foo' USING ERRCODE='unique_violation';
END $$;
CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest
FOR EACH ROW EXECUTE PROCEDURE vtest_insert();
INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
This code raises the error 'foo', even though the insert says DO NOTHING and the error type is unique_violation. Why?
More generally: how can one write trigger functions for a view (that is not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE will work with the correct semantics? What can one do in the INSERT trigger that will cause PostgreSQL to execute the caller-supplied UPDATE clause?
Thanks,
Steve
On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@rothskeller.net> wrote:
Why does the following code raise an error?CREATE TABLE ttest (x integer);CREATE VIEW vtest AS SELECT x FROM ttest;CREATE FUNCTION vtest_insert() RETURNS trigger LANGUAGE plpgsql AS $$RAISE 'foo' USING ERRCODE='unique_violation';END $$;CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtestFOR EACH ROW EXECUTE PROCEDURE vtest_insert();INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;This code raises the error 'foo', even though the insert says DO NOTHING and the error type is unique_violation. Why?More generally: how can one write trigger functions for a view (that is not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE will work with the correct semantics? What can one do in the INSERT trigger that will cause PostgreSQL to execute the caller-supplied UPDATE clause?Thanks,Steve
>CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest
> FOR EACH ROW EXECUTE PROCEDURE vtest_insert();
>INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
A. TRIGGERS are meant for TABLES, not views
and
B. You CANNOT insert into a VIEW.
https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name [ FROM referenced_table_name ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments )
Please also note that it is very helpful if you specify PostgreSQL version and O/S when submitting to this list.
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@rothskeller.net> wrote:This code raises the error 'foo', even though the insert says DO NOTHING and the error type is unique_violation. Why?
More generally: how can one write trigger functions for a view (that is not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE will work with the correct semantics? What can one do in the INSERT trigger that will cause PostgreSQL to execute the caller-supplied UPDATE clause?
Sorry, not sure...
Your problem is that>CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest> FOR EACH ROW EXECUTE PROCEDURE vtest_insert();>INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
A. TRIGGERS are meant for TABLES, not viewsandB. You CANNOT insert into a VIEW.
Your knowledge is this area is out-of-date...you should read the page you linked to again.
I'll admit "table_name" probably could be labelled "relation_name"...though that is then too broad.
David J.
On Tue, Mar 13, 2018 at 10:02 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@rothskeller.net> wrote:This code raises the error 'foo', even though the insert says DO NOTHING and the error type is unique_violation. Why?More generally: how can one write trigger functions for a view (that is not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE will work with the correct semantics? What can one do in the INSERT trigger that will cause PostgreSQL to execute the caller-supplied UPDATE clause?Sorry, not sure...Your problem is that>CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest> FOR EACH ROW EXECUTE PROCEDURE vtest_insert();>INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
A. TRIGGERS are meant for TABLES, not viewsandB. You CANNOT insert into a VIEW.Your knowledge is this area is out-of-date...you should read the page you linked to again.I'll admit "table_name" probably could be labelled "relation_name"...though that is then too broad.David J.
>Your knowledge is this area is out-of-date...you should read the page you linked to again.
Yes, you can create a TRIGGER on view, but you CANNOT INSERT INTO A VIEW,
IE:
CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest
FOR EACH ROW EXECUTE PROCEDURE vtest_insert();
INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
Which is the cause of the Error.!
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Melvin Davidson <melvin6925@gmail.com> writes:
> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@rothskeller.net> wrote:
>> Why does the following code raise an error?
>>
>> CREATE TABLE ttest (x integer);
>> CREATE VIEW vtest AS SELECT x FROM ttest;
>> CREATE FUNCTION vtest_insert() RETURNS trigger LANGUAGE plpgsql AS $$
>> RAISE 'foo' USING ERRCODE='unique_violation';
>> END $$;
>> CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON vtest
>> FOR EACH ROW EXECUTE PROCEDURE vtest_insert();
>> INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
>>
>> This code raises the error 'foo', even though the insert says DO NOTHING
>> and the error type is unique_violation. Why?
> *Your problem is that A. TRIGGERS are meant for TABLES, not views*
> *and *
> *B. You CANNOT insert into a VIEW.*
No, instead-of triggers are supposed to do exactly that. The OP's problem
is that he imagines that ON CONFLICT works by catching arbitrary errors and
checking to see if they are labeled unique_violation. Doesn't work like
that; if it did, it'd be the wrong thing, because we would not know
whether or how to roll back any other side effects the trigger had had.
>> More generally: how can one write trigger functions for a view (that is
>> not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE
>> will work with the correct semantics?
Don't think it's possible unfortunately. The trigger would have to take
care of the situation, but it doesn't have any way to know that the
calling query has an ON CONFLICT rule.
regards, tom lane
I wrote:
>> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <steve@rothskeller.net> wrote:
>>> More generally: how can one write trigger functions for a view (that is
>>> not automatically updatable), such that INSERT ... ON CONFLICT DO UPDATE
>>> will work with the correct semantics?
> Don't think it's possible unfortunately. The trigger would have to take
> care of the situation, but it doesn't have any way to know that the
> calling query has an ON CONFLICT rule.
BTW, I don't suppose it solves your problem, but ON CONFLICT does work
with auto-updatable views:
regression=# CREATE TABLE ttest (x integer primary key);
CREATE TABLE
regression=# CREATE VIEW vtest AS SELECT x FROM ttest;
CREATE VIEW
regression=# INSERT INTO vtest VALUES (1);
INSERT 0 1
regression=# INSERT INTO vtest VALUES (1);
ERROR: duplicate key value violates unique constraint "ttest_pkey"
DETAIL: Key (x)=(1) already exists.
regression=# INSERT INTO vtest VALUES (1) ON CONFLICT DO NOTHING;
INSERT 0 0
The query rewriter knows how to translate the ON CONFLICT to apply
to the underlying table along with the rest of the command.
regards, tom lane