Locking a table read-only temporarilty
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi all,</p>
<p> I've been looking up locking, and perhaps I'm being dense, but
I'm struggling to figure out how to create a lock to make a table
read-only for a short period of time.</p>
<p> I want to set a table to be read-only, so that any other
clients that try to UPDATE or INSERT will hold until the lock is
released. I've been reading;</p>
<p><a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/10/explicit-locking.html">https://www.postgresql.org/docs/10/explicit-locking.html</a></p>
<p> But I'm not sure which lock would be what I want, and I don't
see how to release the lock when finished. Could someone hit me
with a clue-stick?</p>
<p>Thanks!</p>
<pre class="moz-signature" cols="72">--
Digimer
Papers and Projects: <a class="moz-txt-link-freetext" href="https://alteeve.com/w/">https://alteeve.com/w/</a>
"I am, somehow, less interested in the weight and convolutions of Einstein’s brain than in the near certainty that people of equal talent have lived and died in cotton fields and sweatshops." - Stephen Jay Gould</pre>
</body>
</html>
On Thu, 2022-03-31 at 15:20 -0400, Digimer wrote:
I've been looking up locking, and perhaps I'm being dense, but I'm struggling to figure out
how to create a lock to make a table read-only for a short period of time.
I want to set a table to be read-only, so that any other clients that try to UPDATE
or INSERT will hold until the lock is released. I've been reading;
https://www.postgresql.org/docs/10/explicit-locking.html
But I'm not sure which lock would be what I want, and I don't see how to release
the lock when finished. Could someone hit me with a clue-stick?
You'd have to start a transaction and
LOCK tab IN SHARE MODE;
Commit the transaction to release the lock.
However, it is a bad idea to keep transactions with high locks open for a long time.
Your real problem might have a better answer.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com