Locking postgresql table

Question

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.

Why do you want to lock a table for a longer time than the script? You are probably doing something wrong. — LtWorf, Jun 14 '13 at 08:28

Talvalin · Accepted Answer · 2013-06-14T08:21:12.953

5

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;

edited Jun 14 '13 at 08:21

answered Jun 14 '13 at 08:15

Talvalin

7,789
2
30
40

1

If the suggested script works, then please mark this as the accepted answer and upvote when possible. :) – Talvalin Jun 14 '13 at 15:13

score 0 · Answer 2 · answered Jan 05 '23 at 13:20

You can use pg_sleep(), pg_sleep_for() and pg_sleep_until() to lock a table for a specified time as shown below according to Delaying Execution:

BEGIN;
LOCK TABLE person;
SELECT pg_sleep(10);
COMMIT;

BEGIN;
LOCK TABLE person;
SELECT pg_sleep_for('20 seconds');
COMMIT;

BEGIN;
LOCK TABLE person;
SELECT pg_sleep_for('3 minutes');
COMMIT;

BEGIN;
LOCK TABLE person;
SELECT pg_sleep_until('today 21:45');
COMMIT;

BEGIN;
LOCK TABLE person;
SELECT pg_sleep_until('tomorrow 03:00');
COMMIT;

Locking postgresql table

2 Answers2