CODEWITHSUNDEEP
postgresqlplpgsql
inf3rno
inf3rno

Reputation: 26129

pgsql select for update multiple tables

I have the following plpgsql function:

CREATE FUNCTION user_delete(
  IN id INT4
)
  RETURNS VOID
AS
  $BODY$
  BEGIN
    DELETE FROM user_role WHERE user_id = id;
    DELETE FROM user_permission WHERE user_id = id;
    DELETE FROM permission_cache WHERE user_id = id;
    DELETE FROM access WHERE user_id = id;
    DELETE FROM "user" WHERE user_id = id;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

Is it possible to lock rows related to used_id in all tables with a single select for update query?

Upvotes: 1

Views: 1671

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656586

You wouldn't normally need to use explicit locks unless you have a race condition and heavy concurrent load in your application. It's generally best to streamline write access, so that all processes access tables and rows in the same order to avoid deadlocks. The DELETE commands collects locks just as fast as SELECT FOR UPDATE does.

I don't think it is possible to do that with a single SELECT FOR UPDATE.
Just lock rows in all tables sequentially:

PERFORM 1 FROM user_role x WHERE x.user_id = id FOR UPDATE;
PERFORM 1 FROM user_permission x WHERE x.user_id = id FOR UPDATE;
...

Locks are collected "as you go" and released at the end of the transaction. Since a function is inside a transaction automatically, all locks remain at least until the end of the function.

  • Inside a plpgsql function use PERFORM instead of SELECT, since you don't actually want any rows returned.
  • Beware of name conflicts. I used table-qualified column names in my example.

Upvotes: 1

Related Questions