Thread

[PATCH v7 16/16] Predicate locks are held per-cluster, not per-database

Karl O. Pinc <kop@karlpinc.com> — 2023-10-03T17:11:31Z

I believe this is still true and nothing has changed since
this email thread:
https://www.postgresql.org/message-id/20170518110712.31caaf3b@slate.meme.com

This is a significant corner case and so should be documented.  It is
also somewhat suprising since the databases within a cluster are
otherwise isolated, at least from the user's perspective.

I mention psql pagination because it is easy to overlook the fact that
it is not just end-user applications that generate transactions.

I have not thought about this in a long time and am not 100% certain
that WAL checkpoints on segments are delayed until the predicate lock
for the respective transaction is released.  I'm pretty sure, but this
should be checked.

I am sure that all concurrent predicate locks must be released
together.  See the 2nd to last sentence of ports12:
https://arxiv.org/pdf/1208.4179.pdf

The index entries might be improved.  The idea is to guide someone
who's got a lot of WAL laying around and is using serializable
transactions.
---
 doc/src/sgml/mvcc.sgml | 28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)

diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index f8f83d463d..b021a2d2de 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -829,6 +829,34 @@ ERROR:  could not serialize access due to read/write dependencies among transact
        query execution time.
       </para>
      </listitem>
+     <listitem>
+
+       <indexterm>
+         <primary>WAL</primary>
+         <secondary>Serializeable Snapshot Isolation and
+           WAL checkpoint delay</secondary>
+       </indexterm>
+       
+       <indexterm>
+         <primary>Serializable Snapshot Isolation</primary>
+         <secondary>WAL checkpoint delay</secondary>
+       </indexterm>
+
+       <para>
+         Predicate locks are held per-cluster, not per database.
+         This means that serializeable transactions in one database can have
+         effects in another.
+         Long running serializeable transactions, as might occur accidentally
+         when
+         <link linkend="app-psql-meta-command-pset-pager">pagination</link>
+         halts <link linkend="app-psql">psql</link> output, can have
+         significant inter-database effects.
+         These include exhausting available predicate locks and
+         cluster-wide <link linkend="ports12">WAL checkpoint delay</link>.
+         When making use of serializeable transactions consider having
+         separate clusters for production and non-production use.
+       </para>
+     </listitem>
     </itemizedlist>
    </para>
 
-- 
2.30.2


--MP_/VSGM3xNEmY7iJyL2wuWRCjV--