PostgreSQL - CONFLICT UPDATE with a view with a subset of columns

Question

The current version is 9.5.3. Of course, an update is planned.

I have a PostgreSQL database whose schema precedes the table at the row level at the row level (i.e. CREATE POLICY ...). Row level security has been implemented using views. Security is performed in the view by selecting only rows that have their own name that matches CURRENT_USER.

I am trying to create an upsert request using this kind of. The problem arises when I try to name conflict_target.

The problem with use ON CONFLICT UPDATE ...comes from the name of which restriction has been violated.

Game example

CREATE TABLE foo (id serial, num int, word text, data text, ownername varchar(64));

For each user, the combinations wordand nummust be unique.

CREATE UNIQUE INDEX foo_num_word_owner_idx ON foo (num, word, ownername);

, . . security_barrier v 9.5. , ownername.

CREATE VIEW foo_user WITH (security_barrier = True) AS
    SELECT id, num, word, data FROM foo 
    WHERE foo.ownername = CURRENT_USER;    

:

CREATE OR REPLACE FUNCTION trf_set_owner() RETURNS trigger AS
$$
BEGIN
    IF (TG_OP = 'INSERT') THEN
    NEW.ownername = CURRENT_USER::varchar(64);   
    END IF;
    IF (TG_OP = 'UPDATE') THEN
        NEW.ownername = CURRENT_USER::varchar(64);
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER foo_row_owner
    BEFORE INSERT OR UPDATE ON foo FOR EACH ROW
     EXECUTE PROCEDURE trf_set_owner();

, ownername ; .

:

INSERT INTO foo_user (num, word, data) VALUES (1, 'asdf', 'cat'), (2, 'qwer', 'dog');


SELECT * FROM foo;
-- normally, this would give an error related to privileges,
-- because we don't allow users to query the underlying table.
-- bypassed here for demo purposes.

 id | num | word | data | ownername
----+-----+------+------+-----------
  1 |   1 | asdf | cat  | admin
  2 |   2 | qwer | dog  | admin
(2 rows)


SELECT * FROM foo_user;

 id | num | word | data
----+-----+------+------
  1 |   1 | asdf | cat
  2 |   2 | qwer | dog
(2 rows)

.

, num word . , num word ( ).

ON CONFLICT INSERT, UPSERT-ish. .

:

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog');
ERROR:  duplicate key value violates unique constraint "foo_num_word_owner_idx"
DETAIL:  Key (num, word, ownername)=(2, qwer, admin) already exists.

. .

ON CONFLICT,

:

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog')
    ON CONFLICT DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 2:     ON CONFLICT DO UPDATE
            ^
HINT:  For example, ON CONFLICT (column_name).

, , . , . :

ON CONFLICT,

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog')
    ON CONFLICT (num, word, ownername) DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

ERROR:  column "ownername" does not exist
LINE 2:     ON CONFLICT (num, word, ownername) DO UPDATE

True. . ownername , , num word.

ON CONFLICT,

, :

ALTER TABLE foo 
    ADD CONSTRAINT foo_num_word_owner_crt UNIQUE 
    USING INDEX foo_num_word_owner_idx;

NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index 
"foo_num_word_owner_idx" to "foo_num_word_owner_crt"

, :

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog')
    ON CONFLICT ON CONSTRAINT foo_num_word_owner_crt DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

ERROR:  constraint "foo_num_word_owner_crt" for table "foo_user" does not exist

, : , .

. ON CONFLICT, ? ?

( ), , ( API-breaker, ).

.

Answer 1

, ON CONFLICT INSTEAD OF, :

CREATE OR REPLACE FUNCTION trf_set_num_word() RETURNS trigger AS $$
BEGIN
    -- Check if (num, word, ownername) exists by trying an UPDATE
    UPDATE foo SET data = 'frog'
    WHERE num = NEW.num AND word = NEW.word AND ownername = CURRENT_USER::varchar(64);   
    IF FOUND THEN
        RETURN NULL; -- If so, don't INSERT/UPDATE
    END IF;
    RETURN NEW; -- If not, do the INSERT
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER foo_user_num_word
    INSTEAD OF INSERT OR UPDATE ON foo_user FOR EACH ROW
    EXECUTE PROCEDURE trf_set_num_word();