Ошибка команда on conflict do update не может менять строку повторно

We can find the error message from the source code, which we can simply understand why we got ON CONFLICT DO UPDATE command cannot affect row a second time.

In the source code of PostgreSQL at src/backend/executor/nodeModifyTable.c and the function of ExecOnConflictUpdate(), we can find this comment:

This can occur when a just inserted tuple is updated again in the same command. E.g. because multiple rows with the same conflicting key values are inserted.
This is somewhat similar to the ExecUpdate() TM_SelfModified case. We do not want to proceed because it would lead to the same row being updated a second time in some unspecified order, and in contrast to plain UPDATEs there’s no historical behavior to break.

As the comment said, we can not update the row which we are inserting in INSERT ... ON CONFLICT, just like:

postgres=#  CREATE TABLE t (id int primary key, name varchar);

postgres=#  INSERT INTO t VALUES (1, 'smart'), (1, 'keyerror') 
postgres=#  ON CONFLICT (id) DO UPDATE SET name = 'Buuuuuz';
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Remember, the executor of postgresql is a volcano model, so it will process the data we insert one by one. When we process to (1, ‘smart’), since the table is empty, we can insert normally. When we get to (1, ‘keyerror’), there is a conflict with the (1, ‘smart’) we just inserted, so the update logic is executed, which results in updating our own inserted data, which PostgreSQL doesn’t allow us to do.

Similarly, we cannot update the same row of data twice:

postgres=# DROP TABLE IF EXISTS t;
postgres=# CREATE TABLE t (id int primary key, name varchar);

postgres=# INSERT INTO t VALUES (1, 'keyerror'), (1, 'buuuuz') 
postgres=# ON CONFLICT (id) DO UPDATE SET name = 'Buuuuuuuuuz';
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple referenced tables.

I have the following type that I am passing to the function:

CREATE TYPE symbol_record AS
(
    name                       character varying(32),
    exchange                   character varying(64),

    base_name                  character varying(16),
    base_precision             smallint,
    base_commission_precision  smallint,
    
    quote_name                 character varying(16),
    quote_precision            smallint,
    quote_commission_precision smallint
);

the following relevant CTE’s:

-- select all the distinct base_name and quote_name
typ_asset AS (SELECT v.name, a.id
    FROM (
        SELECT DISTINCT (base_name) name FROM val
        UNION
        SELECT DISTINCT (quote_name) name FROM val
    ) v
    LEFT JOIN asset a USING (name)
),

and

ins_asset AS (INSERT INTO asset AS a (name, created_at)
    SELECT name, now()
    FROM typ_asset
    WHERE id IS NULL
    ON CONFLICT (name) DO UPDATE
        SET name = EXCLUDED.name
            WHERE a.name <> EXCLUDED.name
    RETURNING a.name, a.id
),

are working correctly.

However, I am unsure how to return the matching ‘precision’ and ‘commission_precision’ columns for each of the base/quote for consumption by the calling insert/select in ins_asset.

I tried:

typ_asset AS (SELECT v.name, v."precision", v.commission_precision, a.id
    FROM (
        SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
        UNION
        SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val
    ) v
    LEFT JOIN asset a USING (name)
),

and adding the columns accordingly to the ins_asset CTE, but am getting the error:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

I am currently using Postgres 14, and the tables and full function can be seen at:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=bfcb7f7a9a9fcbd8a1b65ec033af8ac2

I am sure it has something to do with the inserting additional data into the asset table (typ_asset / ins_asset) ctes, as it works fine without passing the additional columns (or I am passing them back incorrectly)

And further, yes I am fully aware that I am using a reserved keyword (precision). As this is the business language/terminology I have kept it for now (quoting as necessary), but it will also be fixed.

What is the correct way to return the ‘precision’ and ‘commission_precision’ columns, and upsert them into the asset table? The ‘name’ column in the asset table is unique, and I have tried to set commission_precision instead of name in case the unique column was the cause of the error. that also failed with the same error.

edit
(1) per requested by ypercubeᵀᴹ, the full working fiddle without the precision and commission_precision values in the asset table:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f7ad113c6c812c1348bbc85eef24661

(2) I’m sorry, yes you are correct, I have been trying many different things to fix the problem and DISTINCT ON was one thing I tried (because I don’t fully understand the language), apparently, among several other changes. I have corrected the non working dbfiddle, and that actually fixed the problem.

I have a table from which I want to UPSERT into another, when try to launch the query, I get the «cannot affect row a second time» error. So I tried to see if I have some duplicate on my first table regarding the field with the UNIQUE constraint, and I have none. I must be missing something, but since I cannot figure out what (and my query is a bit complex because it is including some JOIN), here is the query, the field with the UNIQUE constraint is «identifiant_immeuble» :

with upd(a,b,c,d,e,f,g,h,i,j,k) as(
select id_parcelle, batimentimmeuble,etatimmeuble,nb_loc_hab_ma,nb_loc_hab_ap,nb_loc_pro, dossier.id_dossier, adresse.id_adresse, zapms.geom, 1, batimentimmeuble2
from public.zapms
left join geo_pays_gex.dossier on dossier.designation_siea=zapms.id_dossier
left join geo_pays_gex.adresse on adresse.id_voie=(select id_voie from geo_pays_gex.voie where (voie.designation=zapms.nom_voie or voie.nom_quartier=zapms.nom_quartier) and voie.rivoli=lpad(zapms.rivoli,4,'0'))
and adresse.num_voie=zapms.num_voie
and adresse.insee=zapms.insee_commune::integer
)
insert into geo_pays_gex.bal2(identifiant_immeuble, batimentimmeuble, id_etat_addr, nb_loc_hab_ma, nb_loc_hab_ap, nb_loc_pro, id_dossier, id_adresse, geom, raccordement, batimentimmeuble2)
select a,b,c,d,e,f,g,h,i,j,k from upd
on conflict (identifiant_immeuble) do update
set batimentimmeuble=excluded.batimentimmeuble, id_etat_addr=excluded.id_etat_addr, nb_loc_hab_ma=excluded.nb_loc_hab_ma, nb_loc_hab_ap=excluded.nb_loc_hab_ap, nb_loc_pro=excluded.nb_loc_pro, 
id_dossier=excluded.id_dossier, id_adresse=excluded.id_adresse,geom=excluded.geom, raccordement=1, batimentimmeuble2=excluded.batimentimmeuble2
;

As you can see, I use several intermediary tables in this query : one to store the street’s names (voie), one related to this one storing the adresses (adresse, basically numbers related through a foreign key to the street’s names table), and another storing some other datas related to the projects’ names (dossier).

I don’t know what other information I could give to help find an answer, I guess it is better I do not share the actual content of my tables since it may touch some privacy regulations or such.

Thanks for your attention.

EDIT : I found a workaround by deleting the entries present in the zapms table from the bal2 table, as such
delete from geo_pays_gex.bal2 where bal2.identifiant_immeuble in (select id_parcelle from zapms);
it is not entirely satisfying though, since I would have prefered to keep track of the data creator and the date of creation, as much as the fact that the data has been modified (I have some fields to store this information) and here I simply erase all this history… And I have another table with the primary key of the bal2 table as a foreign key. I am still in the DB creation so I can afford to truncate this table, but in production it wouldn’t be possible since I would lose some datas.

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple referenced tables.

I have the following type that I am passing to the function:

CREATE TYPE symbol_record AS
(
    name                       character varying(32),
    exchange                   character varying(64),

    base_name                  character varying(16),
    base_precision             smallint,
    base_commission_precision  smallint,
    
    quote_name                 character varying(16),
    quote_precision            smallint,
    quote_commission_precision smallint
);

the following relevant CTE’s:

-- select all the distinct base_name and quote_name
typ_asset AS (SELECT v.name, a.id
    FROM (
        SELECT DISTINCT (base_name) name FROM val
        UNION
        SELECT DISTINCT (quote_name) name FROM val
    ) v
    LEFT JOIN asset a USING (name)
),

and

ins_asset AS (INSERT INTO asset AS a (name, created_at)
    SELECT name, now()
    FROM typ_asset
    WHERE id IS NULL
    ON CONFLICT (name) DO UPDATE
        SET name = EXCLUDED.name
            WHERE a.name <> EXCLUDED.name
    RETURNING a.name, a.id
),

are working correctly.

However, I am unsure how to return the matching ‘precision’ and ‘commission_precision’ columns for each of the base/quote for consumption by the calling insert/select in ins_asset.

I tried:

typ_asset AS (SELECT v.name, v."precision", v.commission_precision, a.id
    FROM (
        SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
        UNION
        SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val
    ) v
    LEFT JOIN asset a USING (name)
),

and adding the columns accordingly to the ins_asset CTE, but am getting the error:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

I am currently using Postgres 14, and the tables and full function can be seen at:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=bfcb7f7a9a9fcbd8a1b65ec033af8ac2

I am sure it has something to do with the inserting additional data into the asset table (typ_asset / ins_asset) ctes, as it works fine without passing the additional columns (or I am passing them back incorrectly)

And further, yes I am fully aware that I am using a reserved keyword (precision). As this is the business language/terminology I have kept it for now (quoting as necessary), but it will also be fixed.

What is the correct way to return the ‘precision’ and ‘commission_precision’ columns, and upsert them into the asset table? The ‘name’ column in the asset table is unique, and I have tried to set commission_precision instead of name in case the unique column was the cause of the error. that also failed with the same error.

edit
(1) per requested by ypercubeᵀᴹ, the full working fiddle without the precision and commission_precision values in the asset table:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f7ad113c6c812c1348bbc85eef24661

(2) I’m sorry, yes you are correct, I have been trying many different things to fix the problem and DISTINCT ON was one thing I tried (because I don’t fully understand the language), apparently, among several other changes. I have corrected the non working dbfiddle, and that actually fixed the problem.

Часть 2. TABLESAMPLE
Часть 3. GROUPING SETS, CUBE, ROLLUP
В 4 квартале 2015 года ожидается релиз PostgreSQL 9.5. Как всегда, новая версия

кроме новых багов

приносит новые фичи и «плюшки». В данной статье будут рассмотрены две из них, а именно INSERT… ON CONFLICT DO NOTHING/UPDATE и Row-level security. Уже вышла вторая альфа-версия, поэтому самые нетерпеливые могут её установить и попробовать новый функционал.
Скачать можно тут

• INSERT… ON CONFLICT DO NOTHING/UPDATE
• Row-level security

INSERT… ON CONFLICT DO NOTHING/UPDATE

Он же в просторечии UPSERT. Позволяет в случае возникновения конфликта при вставке произвести обновление полей или же проигнорировать ошибку.

То, что раньше предлагалось реализовывать с помощью хранимой функции, теперь будет доступно из коробки. В выражении INSERT можно использовать условие ON CONFLICT DO NOTHING/UPDATE. При этом в выражении указывается отдельно conflict_target (по какому полю/условию будет рассматриваться конфликт) и conflict_action (что делать, когда конфликт произошел: DO NOTHING или DO UPDATE SET).

Полный синтаксис выражения INSERT будет такой:

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

Для нас самое интересное начинается после ON CONFLICT.

Давайте посмотрим на примерах. Создадим таблицу, в которой будут лежать учетные данные неких персон:

CREATE TABLE account
(
  id bigserial,
  name varchar,
  surname varchar,
  address varchar,
  PRIMARY KEY (id),
  CONSTRAINT unique_person UNIQUE (name, surname, address)
);
Query returned successfully with no result in 31 ms.

Выполним запрос на вставку

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO NOTHING;
Query returned successfully: one row affected, 12 ms execution time.

SELECT * FROM ACCOUNT;

Здесь conflict_target — это (id), а conflict_action — DO NOTHING.
Если попытаться выполнить этот запрос второй раз, то вставки не произойдет, при этом и не выдаст никакого сообщения об ошибке:

Query returned successfully: 0 rows affected, 12 ms execution time.

Если бы мы не указали ON CONFLICT (id) DO NOTHING, то получили бы ошибку:

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль');

********** Error **********

ERROR: duplicate key value violates unique constraint "account_pkey"
SQL state: 23505
Detail: Key (id)=(1) already exists.

Такое же поведение (как и у ON CONFLICT (id) DO NOTHING) будет у запроса:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (name, surname, address) DO NOTHING;

Query returned successfully: 0 rows affected, 12 ms execution time.

В нем мы уже берем значение id по умолчанию (из последовательности), но указываем другой conflict_target — по трем полям, на которые наложено ограничение уникальности.

Как упоминалось выше, также можно указать conflict_target с помощью конструкции ON CONSTRAINT, указав непосредственно имя ограничения:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT ON CONSTRAINT unique_person DO NOTHING;
Query returned successfully: 0 rows affected, 11 ms execution time.

Особенно полезно это в случае, если у вас есть исключающее ограничение (exclusion constraint), к которому вы можете обратиться только по имени, а не по набору колонок, как в случае с ограничением уникальности.

Если у вас построен частичный уникальный индекс, то это также можно указать в условии. Пусть в нашей таблице уникальными сочетания фамилия+адрес будут только у людей с именем Вася:

ALTER TABLE account DROP CONSTRAINT unique_person; 
CREATE UNIQUE INDEX unique_vasya ON account (surname, address) WHERE name='Вася';

Тогда мы можем написать такой запрос:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (surname, address) WHERE name='Вася' DO NOTHING;
Query returned successfully: 0 rows affected, 12 ms execution time.

Ну и, наконец, если вы хотите, чтобы DO NOTHING срабатывал при любом конфликте уникальности/исключения при вставке, то это можно записать следующим образом:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT DO NOTHING;
Query returned successfully: 0 rows affected, 12 ms execution time.

Стоит заметить, что задать несколько conflict_action невозможно, поэтому если указан один из них, а сработает другой, то будет ошибка при вставке:

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO NOTHING;

********** Error **********

ERROR: duplicate key value violates unique constraint "unique_person"
SQL state: 23505
Detail: Key (name, surname, address)=(Вася, Пупкин, Москва, Кремль) already exists.

Перейдем к возможностям DO UPDATE SET.

Для DO UPDATE SET в отличие от DO NOTHING указание conflict_action обязательно.

Конструкция DO UPDATE SET обновляет поля, которые в ней указаны. Значения этих полей могут быть заданы явно, заданы по умолчанию, получены из подзапроса или браться из специального выражения EXCLUDED, из которого можно взять данные, которые изначально были предложены для вставки.

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Петя', 'Петров', 'Москва, Кремль')
ON CONFLICT (id)
DO UPDATE SET
name='Петя',
surname='Петров';
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT;

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Петя', 'Петров', 'Москва, Кремль')
ON CONFLICT (id)
DO UPDATE SET
name=EXCLUDED.name || ' (бывший ' || a.name || ')',
surname=EXCLUDED.surname || ' (бывший ' || a.surname || ')';
Query returned successfully: one row affected, 13 ms execution time.

SELECT * FROM ACCOUNT;

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO UPDATE SET
name=DEFAULT,
surname=DEFAULT;
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT;

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO UPDATE SET
name=(SELECT some_field FROM other_table LIMIT 1);

Также может быть использовано условие WHERE. Например, мы хотим, чтобы поле name не обновлялось, если в поле address в строке таблицы уже содержится текст «Кремль», в противном же случае — обнловлялось:

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Кремль')
ON CONFLICT (id) DO UPDATE SET
name=EXCLUDED.name
WHERE a.name not like '%Кремль%';

Query returned successfully: 0 rows affected, 12 ms execution time.

А если хотим, чтобы поле name не обновлялось, если в поле address во вставляемых данных содержится текст «Кремль», в противном же случае — обнловлялось:

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Вася', 'Пупкин', 'Москва, Красная площадь')
ON CONFLICT (id) DO UPDATE SET
name=EXCLUDED.name
WHERE EXCLUDED.name not like '%Кремль%';
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT

ROW LEVEL SECURITY

Row-level security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.

Данная функциональность может быть интересна тем, кто использует базы с большим числом пользователей.

Работает это следующим образом: описываются правила для конкретной таблицы, согласно которым ограничивается доступ к конкретным строкам при выполнении определнных команд, с помощью выражения CREATE POLICY. Каждое правило содержит некое логическое выражение, которое должно быть истинным, чтобы строка была видна в запросе. Затем правила активируются с помощью выражения ALTER TABLE… ENABLE ROW LEVEL SECURITY. Затем при попытке доступа, например при запросе SELECT, проверяется, имеет ли пользователь право на доступ к конкретной строке и если нет, то они ему не показываются. Суперпользователь по умолчанию может видеть все строки, так как у него по умолчанию выставлен флаг BYPASSRLS, который означает, что для данной роли проверки осуществляться не будут.

Синтаксис выражения CREATE POLICY такой:

CREATE POLICY name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

Правила создаются для конкретных таблиц, поэтому в БД может быть несколько правил с одним и тем же именем для различных таблиц.
После выражения FOR указывается, для каких именно запросов применяется правило, по умолчанию — ALL, то есть для всех запросов.

После TO — для каких ролей, по умолчанию — PUBLIC, то есть для всех ролей.

Далее, в выражении USING указывается булевское выражение, которое должно быть true, чтобы конкретная строка была видна пользователю в запросах, которые используют уже имеющиеся данные (SELECT, UPDATE, DELETE). Если булевское выражение вернуло null или false, то строка видна не будет.

В выражении WITH CHECK указывается булевское выражение, которое должно быть true, чтобы запрос, добавляющий или изменяющий данные (INSERT или UPDATE), прошел успешно. В случае, если булевское выражение вернет null или false, то будет ошибка. Выражение WITH CHECK выполняется после триггеров BEFORE (если они присутствуют) и до любых других проверок. Поэтому, если триггер BEFORE модифицирует строку таким образом, что условие не вернет true, будет ошибка. Для успешного выполнения UPDATE необходимо, чтобы оба условия вернули true, в том числе, если в запросе INSERT… ON CONFILCT DO UPDATE произойдет конфликт и запрос попытается модифицировать данные. Если выражение WITH CHECK опущено, вместо него будет подставляться условие из выражения USING.
В условиях нельзя использовать аггрегирующие или оконные функции.

Обычно, требуется управлять доступом, исходя из того, какой пользователь БД запрашивает данные, поэтому нам пригодятся функции, возвращающие информацию о системе (System Information Functions).

Перейдем к примерам:

Добавим в таблицу account поле db_user, заполним это поле для уже существующей записи и добавим новые записи:

ALTER TABLE account ADD COLUMN db_user varchar;
Query returned successfully with no result in 16 ms.
UPDATE account SET db_user='pupkin' WHERE surname='Пупкин';
INSERT INTO account (name, surname, address, db_user)
VALUES ('Петр', 'Петров', 'Москва, Красная площадь', 'petrov'), 
('Иван', 'Сидоров', 'Санкт-Петербург, Зимний дворец', 'sidorov');
Query returned successfully: 2 rows affected, 31 ms execution time.

Создадим роли:

CREATE ROLE pupkin WITH LOGIN PASSWORD 'pupkin';
CREATE ROLE petrov WITH LOGIN PASSWORD 'petrov';
Query returned successfully with no result in 31 ms.

Создадим правило и включим RLS на таблице:

CREATE POLICY select_self ON account
FOR SELECT
USING (db_user=current_user);
ALTER TABLE account ENABLE ROW LEVEL SECURITY;
Query returned successfully with no result in 12 ms.

В данном запросе мы создали правило, согласно которому, пользователю в запросе SELECT будут видны только те строки, в которых значение поля db_user совпадает с именем текущего пользователя БД.

Выполним запрос от пользователя postgres:

SELECT * FROM account

Выполним тот же запрос от пользователя pupkin:

Создадим правило, по которому строки с фамилией «Пупкин» может вставлять только пользователь pupkin:

CREATE POLICY insert_update_pupkin ON account
WITH CHECK (surname<>'Пупкин' OR current_user='pupkin')

Попробуем выполнить запрос от пользователя pupkin:

INSERT INTO account (name, surname, address)
VALUES ('Дмитрий', 'Пупкин', 'Киев, Майдан')
Query returned successfully: one row affected, 13 ms execution time.

Проверим:

select * from account;

Оп-па! Мы забыли указать поле db_user и запись, которую мы вставили, мы уже не увидим. Что ж, давайте исправим такую логику с помощью триггера, в котором будем заполнять поле db_user именем текущего пользователя:

CREATE OR REPLACE FUNCTION fill_db_user() RETURNS TRIGGER AS 
$BODY$
BEGIN
  NEW.db_user = current_user;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER fill_db_user BEFORE INSERT ON account
FOR EACH ROW EXECUTE PROCEDURE fill_db_user();

Пробуем снова:

INSERT INTO account (name, surname, address)
VALUES ('Иван', 'Пупкин', 'Киев, Майдан');
select * from account;

Попробуем изменить данные о Иване Пупкине пользователем petrov:

UPDATE account SET db_user='petrov'
WHERE id=21
Query returned successfully: 0 rows affected, 13 ms execution time.

Как видим, данные не изменились, это произошло потому, что условие USING из правила select_self не выполнилось.

Если одному запросу соответствует несколько правил, то они объединяются через OR.

Стоит отметить, что правила применяются только при явным запросам к таблицам и не применяются при проверках, которые осуществляет система (constaints, foreign keys и т.п.). Это означает, что пользователь с помощью запросов, определить, что какое-либо значение существует в БД. Например, если пользователь может осуществлять вставку в таблицу, которая ссылается на другую таблицу, из которой он не может делать SELECT. В таком случае, он может попытаться сделать INSERT в первую таблицу и по результату (произошла вставка или же произошла ошибка при проверке ссылочной целостности) определить, существует ли значение во второй таблице.

Вариантов использования row-level security можно придумать множество:

• одну и ту же базу используют несколько приложений с разным функционалом
• несколько инстансов одного и того же приложения с разными правами
• доступ по ролям или группам пользователей
• и т.д.

В следующих частях я планирую рассмотреть такие новые фичи PostgreSQL 9.5, как:

• Часть 2. TABLESAMPLE
• SKIP LOCKED
• BRIN-индексы
• GROUPING SETS, CUBE, ROLLUP
• Новые функции для JSONB
• IMPORT FOREIGN SCHEMA
• и другие