Postgres: как вообще сделать столбец неизменным?

TL;DR

Что я пробовал? Отзыв UPDATE привилегии не работает.

# \c danissimo danissimo
You are now connected to database "danissimo" as user "danissimo".

> revoke update (customer_id) on customer from danissimo;
REVOKE
> insert into customer (customer_id) values (0);
INSERT 0 1
> update customer set customer_id = 0 where customer_id = 0;
UPDATE 1
> update customer set customer_id = -1 where customer_id = 0;
UPDATE 1

Ладно, поставим охрану.

create or replace function customer_id_guard() returns trigger
language plpgsql as $body$
begin
  if old.customer_id != new.customer_id then
    raise exception
      'trigger %: updating is prohibited for %',
      tg_name, 'customer_id' using
      errcode = 'restrict_violation';
  end if;
  return new;
end;
$body$;

create or replace trigger customer_id_guard
after update on customer for each row
execute function customer_id_guard();

Теперь давайте дадим им немного работы.

> update customer set customer_id = -1 where customer_id = -1;
UPDATE 1

Правильно, я не менял значение. Как насчет этого:

> update customer set customer_id = 0 where customer_id = -1;
ERROR:  trigger customer_id_guard: updating is prohibited for customer_id
CONTEXT:  PL/pgSQL function customer_id_guard() line 4 at RAISE

Да, вот оно. Хорошо, давайте тоже защитим cart_id. Я не хочу копировать триггерные функции, поэтому попытаюсь обобщить:

create or replace function generated_id_guard() returns trigger
language plpgsql as $body$
declare
  id_col_name text := tg_argv[0];
  equal boolean;
begin
  execute format('old.%1$I = new.%1$I', id_col_name) into equal;
  if not equal then
    raise exception
      'trigger %: updating is prohibited for %',
      tg_name, id_col_name using
      errcode = 'restrict_violation';
  end if;
  return new;
end;
$body$;

create or replace trigger cart_id_guard
after update on cart for each row
execute function generated_id_guard('cart_id');

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

> insert into cart (cart_id) values (0);
INSERT 0 1
> update cart set cart_id = 0 where cart_id = 0;
ERROR:  syntax error at or near "old"
LINE 1: old.cart_id = new.cart_id
        ^
QUERY:  old.cart_id = new.cart_id
CONTEXT:  PL/pgSQL function generated_id_guard() line 6 at EXECUTE

Хммм... Он прав, что за болтающийся old.cart_id = new.cart_id? Что, если я напишу

execute format('select old.%1$I = new.%1$I', id_col_name) into equal;

> update cart set cart_id = 0 where cart_id = 0;
ERROR:  missing FROM-clause entry for table "old"
LINE 1: select old.cart_id = new.cart_id
               ^
QUERY:  select old.cart_id = new.cart_id
CONTEXT:  PL/pgSQL function generated_id_guard() line 6 at EXECUTE

Правильно, правильно ... Что, если я напишу

declare
  id_old int;
  id_new int;
begin
  execute format('select %I from old', id_col_name) into id_old;
  execute format('select %I from new', id_col_name) into id_new;
  if id_old != id_new then

> update cart set cart_id = 0 where cart_id = 0;
ERROR:  relation "old" does not exist
LINE 1: select cart_id from old
                            ^
QUERY:  select cart_id from old
CONTEXT:  PL/pgSQL function generated_id_guard() line 7 at EXECUTE

Ага, «отношения «старого» не существует»...

Ну и последнее средство:

drop table cart;
create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  at timestamp with time zone
);
insert into cart (cart_id) values (0);

create or replace function surrogate_id_guard() returns trigger
language plpgsql immutable parallel safe cost 1 as $body$
begin
  raise exception
    'trigger %: updating is prohibited for %',
    tg_name, tg_argv[0] using
    errcode = 'restrict_violation';
  return null;
end;
$body$;

create or replace trigger cart_id_guard
before update of cart_id on cart for each row
execute function surrogate_id_guard('cart_id');

Я просто заставляю его срабатывать при любой попытке обновления cart_id. Давай проверим:

> update cart set cart_id = 0 where cart_id = 0;
ERROR:  trigger cart_id_guard: updating is prohibited for cart_id
CONTEXT:  PL/pgSQL function surrogate_id_guard() line 3 at RAISE
> update cart set at = now() where cart_id = 0;
UPDATE 1

Ну, наконец, я ответил на свой первоначальный вопрос в этот момент. Но все же возникает другой вопрос: как применить тот же алгоритм, закодированный в функции, к столбцам, заданным в аргументах этой функции?

Если я правильно понимаю, вы хотите запретить любому пользователю изменять идентификатор таблицы после его установки и чтобы общая функция создавала исключение, при этом разрешая другие обновления. Вы можете добиться этого, изменив триггер, а не функцию. Укажите предикат WHEN для самого триггера. Тогда для таблицы cart:

create or replace trigger cart_id_guard
   before update of cart_id 
       on cart for each row
          when (old.cart_id is distinct from new.cart_id)
       execute function surrogate_id_guard('cart_id');

Для таблицы customer триггером становится:

create or replace trigger customer_id_guard
   before update of customer_id 
       on customer for each row
     when (old.customer_id is distinct from new.customer_id)
  execute function surrogate_id_guard('customer_id');

Сама функция триггера не меняется. (демо здесь)

Самая первая попытка в моем предыдущем исследовании заключалась в отзыве привилегий. Как указал Laurenz Albe в своем комментарии, мне пришлось отозвать привилегию на обновление всей таблицы вместо отзыва привилегии на обновление определенного столбца. Вот код:

# \c danissimo danissimo
You are now connected to database "danissimo" as user "danissimo".

create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  at timestamp with time zone default now()
);
insert into cart default values;

revoke update on cart from danissimo;

Могу ли я обновить таблицу сейчас?

> update cart set at = at - interval '1 day';
ERROR:  permission denied for table cart

Хорошо, давайте предоставим право на обновление столбцов, отличных от cart_id:

> grant update (at) on cart to danissimo;
> update cart set at = at - interval '1 day';
UPDATE 1

Все идет нормально. Теперь время идет, и в конце концов danissimo добавляет еще одну колонку item_ids:

alter table cart add column item_ids int[];

Может ли danissimo обновить новую колонку сейчас? Имейте в виду, что у него была отозвана привилегия на обновление всей таблицы, а привилегия на обновление нового столбца не была предоставлена:

> update cart set item_ids = array[1, 3, 7 ,5];
ERROR:  permission denied for table cart

А если я предоставлю ему привилегию?

> grant update (item_ids) on cart to danissimo;
> update cart set item_ids = array[1, 3, 7 ,5];
UPDATE 1

Что все это значит? Я рассматривал два подхода. Один из них — запретить обновления столбца после того, как ему будет присвоено значение. Другой - играть с привилегиями. В наших проектах обычно мы добавляем новые столбцы по мере развития проектов. Если я придерживаюсь привилегий, я должен предоставлять привилегию для обновления нового столбца каждый раз, когда я добавляю новый. С другой стороны, если я защищаю некоторые столбцы с помощью триггера, я просто добавляю новые столбцы и больше не беспокоюсь.

ВЫВОД: Используйте триггеры, как показано выше 👆🏼.