postgres: How to generically make a column immutable?

Question

Here's the problem.

create table customer (
  customer_id int generated by default as identity (start with 100) primary key
);
create table cart (
  cart_id int generated by default as identity (start with 100) primary key
);

I want to protect customer_id and cart_id from updating generically once they are inserted. How?

---

UPD: While I was writing the question I found the answer to my original question. Here it is:

create table cart (
  cart_id int generated by default as identity (start with 100) primary key,
  name text not null,
  at timestamp with time zone
);

create or replace function table_update_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_update_guard
before update of cart_id, name on cart for each row
-- NOTE: the WHEN clause below is optional
when (
     old.cart_id is distinct from new.cart_id
  or old.name    is distinct from new.name
)
execute function table_update_guard('cart_id, name');

> insert into cart (cart_id, name) values (0, 'prado');
INSERT 0 1
> update cart set cart_id = -1 where cart_id = 0;
ERROR:  trigger cart_update_guard: updating is prohibited for cart_id, name
CONTEXT:  PL/pgSQL function table_update_guard() line 3 at RAISE
> update cart set name = 'nasa' where cart_id = 0;
ERROR:  trigger cart_update_guard: updating is prohibited for cart_id, name
CONTEXT:  PL/pgSQL function table_update_guard() line 3 at RAISE
> update cart set at = now() where cart_id = 0;
UPDATE 1

The WHEN clause was suggested by Belayer in his answer. The full explanation is in my research. Additionally I examined the approach with playing with privileges. NOTE: Some people say that triggers like here are performance killers. They are wrong. How do you think postgres implements constraints internally? — Using implicit triggers like defined here.

Answer 1

TL;DR

What did I try? Revoking UPDATE privilege doesn't work.

# \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

Okay, let's put a guard on it.

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();

Now let's give them some work.

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

Right, I didn't change the value. What about this:

> 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

Yeah, here it goes. Good, let's protect cart_id as well. I don't want to copy–paste trigger functions, so I let's try to generalize it:

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');

As you might notice I pass the column name to the trigger function and generate an expression and put the result of that expression into equal which then test.

> 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

Hmmm... He's right, what the dangling old.cart_id = new.cart_id? What if I write

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

Right, right... What if I write

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

Aha, «relation "old" does not exist»...

Well, here's the last resort:

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');

I just make it trigger on any attempt to update cart_id. Let's check:

> 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

Well, finally I answered my original question at this point. But another question is still arisen: How to apply the same algorithm encoded in a function to columns given in args to that function?

Answer 2

If I understand correctly you want to prevent any user from modifying the the table id once it is established and to have a generic function produce the exception, while still allowing other updates. You can accomplish this by modifying the trigger rather than the function. Specify the WHEN predicate on the trigger itself. For the cart table then:

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');

The for the customer table the trigger becomes:

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');

The trigger function itself does not change. (demo here)