databasepostgresqllockingpsqltable-lock

Locking postgresql table

Is it possible to lock a table for a specified time in PostgreSQL database by running psql commands via shell script or sql file?

If we run the LOCK TABLE command, when the script exits the lock will also be gone, so that is not sufficient.

Solution

  • Use pg_sleep for your specified time in conjunction with LOCK TABLE? Something like the script below should lock a table for 60 seconds (note this is untested):

    BEGIN WORK;
LOCK TABLE MyTable IN ACCESS EXCLUSIVE MODE;
SELECT pg_sleep(60);
COMMIT WORK;