UPSERT on a view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

UPSERT on a view

Steven Roth
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

Reply | Threaded
Open this post in threaded view
|

Re: UPSERT on a view

Melvin Davidson-5


On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <[hidden email]> 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?

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;

Your problem is that
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!
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT on a view

David G Johnston
On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson <[hidden email]> wrote:
On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <[hidden email]> 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...
>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;

Your problem is that
A. TRIGGERS are meant for TABLES, not views
and
B. 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.
 
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT on a view

Melvin Davidson-5


On Tue, Mar 13, 2018 at 10:02 AM, David G. Johnston <[hidden email]> wrote:
On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson <[hidden email]> wrote:
On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <[hidden email]> 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...
>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;

Your problem is that
A. TRIGGERS are meant for TABLES, not views
and
B. 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!
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT on a view

Tom Lane-2
In reply to this post by Melvin Davidson-5
Melvin Davidson <[hidden email]> writes:

> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: UPSERT on a view

Tom Lane-2
I wrote:
>> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth <[hidden email]> 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

Previous Thread Next Thread