PostgreSQL Locking – Allow INSERT but Not UPDATE

lockingpostgresql

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.

Best Answer

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

SELECT * FROM tbl FOR UPDATE;

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;

Or:

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.

Related Solutions

Sql-server – How to force the use of row locks

The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this:

CREATE TABLE dbo.LockTest
(
    col1    integer IDENTITY NOT NULL,
    col2    integer NOT NULL,
    col3    integer NOT NULL,

    CONSTRAINT PK_LockTest
        PRIMARY KEY CLUSTERED (col1)
);
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_LockTest
ON dbo.LockTest (col2);
GO
ALTER TABLE dbo.LockTest SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX PK_LockTest ON dbo.LockTest SET (ALLOW_PAGE_LOCKS=OFF);

Using the clustered index as the access method:

-- Error 651:
-- Cannot use the PAGE granularity hint on the table "dbo.LockTest"
-- because locking at the specified granularity is inhibited.
SELECT col1
FROM dbo.LockTest WITH (INDEX(PK_LockTest), PAGLOCK);

Using the nonclustered index:

-- Succeeds: page locks allowed on index UQ_LockTest
SELECT col2 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Succeeds: UQ_LockTest also includes col1
-- NC indexes always include the clustering key
SELECT col1 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Fails: cannot retrieve col3 without touching the clustered index
SELECT col3 FROM dbo.LockTest AS lt WITH (PAGLOCK);

Note that disabling page locking can have unexpected side-effects, such as preventing index reorganization (since this process works at the page level):

-- Error 1943:
-- The index "PK_LockTest" on table "LockTest" cannot
-- be reorganized because page level locking is disabled.
ALTER INDEX PK_LockTest ON dbo.LockTest REORGANIZE;

-- Succeeds (page locking available):
ALTER INDEX UQ_LockTest ON dbo.LockTest REORGANIZE;

MySQL InnoDB MVCC vs Locking – Key Differences Explained

MVCC applies to isolation levels read-committed and repeatable read (default).

You don't need to specify anything for both of these features to work together. Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.

Best Answer

Related Solutions

Sql-server – How to force the use of row locks

MySQL InnoDB MVCC vs Locking – Key Differences Explained

Related Question