Lock a row in a trigger

I have two PostgreSQL tables: objects and metadata. Each object has a size property that represents its size in bytes and can be stored in a unique location with storage_id. In metadata, the total_size of all objects of each storage with a given storage_id is maintained. Simplified tables:

CREATE TABLE IF NOT EXISTS objects (
  object_id UUID PRIMARY KEY,
  storage_id UUID NOT NULL,
  size BIGINT NOT NULL,
  FOREIGN KEY (storage_id) REFERENCES metadata(storage_id)
);

CREATE TABLE IF NOT EXISTS metadata (
  storage_id UUID PRIMARY KEY,
  total_size BIGINT DEFAULT 0
);

To maintain the total_size in the metadata table, I have a triggers that whenever an object is inserted or deleted, the total_size gets updated, i.e. here is the trigger for insertion:

CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  UPDATE metadata
  SET total_size = total_size + NEW.size
  WHERE storage_id = NEW.storage_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_insert
AFTER INSERT ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_insert();

The problem is that concurrent insertions/deletions could override the total_size which leads to invalid data. How can I change the trigger so it contains a row-level lock when the SET total_size = total_size + NEW.size gets executed? I was thinking of the FOR UPDATE statement, but that requires a SELECT. Could I use PERFORM ... FOR UPDATE; to lock the row?

Could I use PERFORM ... FOR UPDATE; to lock the row?

You could, but that just duplicates the lock your update already acquires on its own, as immediately pointed out by @Iłya Bursov. You can see it in pg_locks:
_{demo at db<>fiddle}

start transaction;
create table t(v int primary key);
insert into t values(1),(2);
commit and chain;
update t set v=9 where v=1;
select mode
     , relation::regclass
     , pid=pg_backend_pid() as is_me
from pg_locks
join pg_database on pg_locks.database   = pg_database.oid
                and pg_database.datname = current_catalog
where relation::regclass in ('t','t_pkey');

One's on the table, the other on the index enforcing your primary key.

---

As hinted by @Frank Heikens, you might want to swap out the update for an upsert, to also make sure your initial inserts are handled safely, and add a coalesce() to cover any updates and deletes as recommended by @Charlieface:

CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO metadata AS m(storage_id,total_size)
  VALUES(coalesce(NEW.storage_id,OLD.storage_id),coalesce(NEW.size,0))
  ON CONFLICT(storage_id)DO UPDATE
  SET total_size = m.total_size 
                  +coalesce(NEW.size,0) 
                  -coalesce(OLD.size,0);
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_insert
AFTER INSERT OR UPDATE OR DELETE ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_insert();

To leave it as an after trigger, you'll need to change the FK constraint. While you're at it, consider a CHECK constraint to make sure nobody slips in negative-size objects:

size BIGINT NOT NULL CHECK(size>=0),
  FOREIGN KEY (storage_id) REFERENCES metadata(storage_id) DEFERRABLE INITIALLY DEFERRED

Deferrability makes it give the trigger time to add the new storage_id before the constraint gets validated. Without that, the trigger would have to fire before.

_{demo at db<>fiddle}

insert into objects values
 (gen_random_uuid(),gen_random_uuid(),1)
,(gen_random_uuid(),gen_random_uuid(),1)
returning*;

Now metadata got populated on its own, automatically:

table metadata;

Adding an object to existing storage:

insert into objects 
select gen_random_uuid()
     , storage_id
     , 9 as size
from objects
limit 1 --any existing storage will do
returning*;

table metadata;

Updating the size of any one arbitrary object:

update objects 
set size=5
where ctid=(select min(ctid)from objects)
returning*;

table metadata;

delete from objects
where ctid=(select min(ctid)from objects)
returning*;

table metadata;

Optionally, you can expand the trigger to also take care of removing totals for unused storage where total_size dropped to zero.

To handle a truncate event, you also need a separate statement level trigger - even though that deletes all rows of a table, it does not fire on delete triggers.

@Adrian Klaver's view is good thing to consider:

1. Triggers slow things down, especially row-level ones, especially those that have to lock things.
2. If the table isn't very large and/or you don't need these totals very often, it might be a better idea to to tolerate some additional time&effort for the db to run a fresh scan and aggregate at those times, than slow down all CRUD operations on it.
3. If you don't need completely fresh, up-to-date, precise and exact totals but you do need them quick, you can make that a materialized view and cache them periodically.