I would like to lock a table: INSERT should be allowed, but UPDATE should be disabled.

I am using a current PostgreSQL version.

I read Advisory Locks and explicit locking. Maybe I am blind, but I could not find a suitable solution for my goal.

Here are details to my use case:

There is a table which is roughly like logging: Several processes add new rows to the table at the same time.

Once in a hour a script processes the data which was added.

Now I want to be sure that this script has a fixed working set. No modifications should be done to the rows which the script is working on. But it is ok if new rows get added while the scripts run. These rows get processes with the next call of the script.

  • There is no table lock mode which does this. What is the other task (the one that takes out this intended lock mode) doing while it holds it the putative lock?
    – jjanes
    Nov 12, 2020 at 20:12
  • 1
    A brute force approach would be to do a select * from the_table for update
    – user1822
    Nov 12, 2020 at 20:53
  • add an instead of trigger for updates, then either throw an error, silently rollback or just do nothing. Nov 12, 2020 at 21:40
  • You probably need to specify more closely, what you want to achieve exactly ... Nov 12, 2020 at 23:06
  • A textbook example of an X-Y problem
    – mustaccio
    Nov 13, 2020 at 12:55

2 Answers 2


Sure, you can use row-level locks to write-lock all rows currently in the table with:


Or similar.

But that prevents all writes, including DELETE, not only UPDATE - for the duration of the lock. And there is the remaining question about rows inserted after that: shouldn't those be protected from UPDATE, too?

The way you phrased it ("INSERT should be allowed, but UPDATE should be disabled."), it sounds like a more general matter of privilege management with GRANT and REVOKE, rather than locking.

Something like:

GRANT INSERT ON TABLE public.tbl TO public;
REVOKE UPDATE ON TABLE public.tbl FROM public;


GRANT INSERT ON TABLE public.tbl TO specific_role;
REVOKE UPDATE ON TABLE public.tbl FROM specific_role;

The actual commands depend on who has been GRANTed privileges in the first place, and who shall be prevented to update now.
Don't forget the special case of the table owner. The manual on GRANT:

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of their own privileges for safety.)

While locks only last for the duration of a transaction, the effect is permanent - until changed explicitly. Also, locks are expensive. GRANT / REVOKE is almost cost-free.

You need the necessary privileges. Either be the owner of the table, be member in the role owning the table, be a superuser, or you have been granted the same privilege WITH GRANT OPTION.


You could use pg_try_advisory_lock(). See Advisory Locks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.