Re: [HACKERS] [PATCH] Add GUCs for predicate lock promotion thresholds - Mailing list pgsql-hackers

Kevin Grittner <kgrittn@gmail.com> writes:

> It occurred to me that it would make sense to allow these settings
> to be attached to a database or table (though *not* a role).  I'll
> look at that when I get back if you don't get to it first.

Attached is a draft patch (no docs or tests) on top of the previous one,
adding reloptions for the per-relation and per-page thresholds.  That
made me realise that the corresponding GUCs don't need to be PGC_SIGHUP,
but could be PGC_SUSET or PGC_USERSET.  I'll adjust the original patch
if you agree.  I have not got around around to adding per-database
versions of the setting, but could have a stab at that.


-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.               - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.                      - Calle Dybedahl

From e2ae108ca8212790604ef7e54f278e41ca460ffb Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Wed, 25 Jan 2017 00:13:53 +0000
Subject: [PATCH 2/2] Add max_pred_locks_per_{relation,page} reloptions

---
 src/backend/access/common/reloptions.c | 24 +++++++++++++++++++++-
 src/backend/storage/lmgr/predicate.c   | 37 +++++++++++++++++++++++-----------
 src/bin/psql/tab-complete.c            |  2 ++
 src/include/utils/rel.h                | 21 +++++++++++++++++++
 4 files changed, 71 insertions(+), 13 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 42b4ea410f..ab8977a218 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -284,6 +284,24 @@ static relopt_int intRelOpts[] =
         },
         -1, 0, 1024
     },
+    {
+        {
+            "max_pred_locks_per_relation",
+            "Maximum number of pages or rows that can be predicate-locked before locking the whole relation.",
+            RELOPT_KIND_HEAP,
+            AccessExclusiveLock,
+        },
+        -1, 0, INT_MAX
+    },
+    {
+        {
+            "max_pred_locks_per_page",
+            "Maximum number of rows on a single page that can be predicate-locked before locking the whole page.",
+            RELOPT_KIND_HEAP,
+            AccessExclusiveLock,
+        },
+        -1, 0, INT_MAX
+    },
 
     /* list terminator */
     {{NULL}}
@@ -1310,7 +1328,11 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
         {"user_catalog_table", RELOPT_TYPE_BOOL,
         offsetof(StdRdOptions, user_catalog_table)},
         {"parallel_workers", RELOPT_TYPE_INT,
-        offsetof(StdRdOptions, parallel_workers)}
+        offsetof(StdRdOptions, parallel_workers)},
+        {"max_pred_locks_per_relation", RELOPT_TYPE_INT,
+        offsetof(StdRdOptions, max_predicate_locks_per_relation)},
+        {"max_pred_locks_per_page", RELOPT_TYPE_INT,
+        offsetof(StdRdOptions, max_predicate_locks_per_page)}
     };
 
     options = parseRelOptions(reloptions, validate, kind, &numoptions);
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index ac927f2c3a..9b767d7e04 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -443,8 +443,9 @@ static void RestoreScratchTarget(bool lockheld);
 static void RemoveTargetIfNoLongerUsed(PREDICATELOCKTARGET *target,
                            uint32 targettaghash);
 static void DeleteChildTargetLocks(const PREDICATELOCKTARGETTAG *newtargettag);
-static int    MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag);
-static bool CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag);
+static int    MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag, Relation relation);
+static bool CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag,
+                                                Relation relation);
 static void DecrementParentLocks(const PREDICATELOCKTARGETTAG *targettag);
 static void CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag,
                     uint32 targettaghash,
@@ -453,7 +454,8 @@ static void DeleteLockTarget(PREDICATELOCKTARGET *target, uint32 targettaghash);
 static bool TransferPredicateLocksToNewTarget(PREDICATELOCKTARGETTAG oldtargettag,
                                   PREDICATELOCKTARGETTAG newtargettag,
                                   bool removeOld);
-static void PredicateLockAcquire(const PREDICATELOCKTARGETTAG *targettag);
+static void PredicateLockAcquire(const PREDICATELOCKTARGETTAG *targettag,
+                                 Relation relation);
 static void DropAllPredicateLocksFromTable(Relation relation,
                                bool transfer);
 static void SetNewSxactGlobalXmin(void);
@@ -2138,17 +2140,27 @@ DeleteChildTargetLocks(const PREDICATELOCKTARGETTAG *newtargettag)
  * entirely arbitrarily (and without benchmarking).
  */
 static int
-MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag)
+MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag, Relation relation)
 {
     switch (GET_PREDICATELOCKTARGETTAG_TYPE(*tag))
     {
         case PREDLOCKTAG_RELATION:
+        {
+            int rel_max_pred_locks = RelationGetMaxPredicateLocksPerRelation(relation, -1);
+            if (rel_max_pred_locks != -1)
+                return rel_max_pred_locks;
             return max_predicate_locks_per_relation < 0
                 ? max_predicate_locks_per_xact / -max_predicate_locks_per_relation
                 : max_predicate_locks_per_relation;
+        }
 
         case PREDLOCKTAG_PAGE:
+        {
+            int rel_max_pred_locks_per_page = RelationGetMaxPredicateLocksPerPage(relation, -1);
+            if (rel_max_pred_locks_per_page != -1)
+                return rel_max_pred_locks_per_page;
             return max_predicate_locks_per_page;
+        }
 
         case PREDLOCKTAG_TUPLE:
 
@@ -2174,7 +2186,8 @@ MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag)
  * Returns true if a parent lock was acquired and false otherwise.
  */
 static bool
-CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag)
+CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag,
+                                    Relation relation)
 {
     PREDICATELOCKTARGETTAG targettag,
                 nexttag,
@@ -2204,7 +2217,7 @@ CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag)
             parentlock->childLocks++;
 
         if (parentlock->childLocks >
-            MaxPredicateChildLocks(&targettag))
+            MaxPredicateChildLocks(&targettag, relation))
         {
             /*
              * We should promote to this parent lock. Continue to check its
@@ -2220,7 +2233,7 @@ CheckAndPromotePredicateLockRequest(const PREDICATELOCKTARGETTAG *reqtag)
     if (promote)
     {
         /* acquire coarsest ancestor eligible for promotion */
-        PredicateLockAcquire(&promotiontag);
+        PredicateLockAcquire(&promotiontag, relation);
         return true;
     }
     else
@@ -2362,7 +2375,7 @@ CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag,
  * any finer-grained locks covered by the new one.
  */
 static void
-PredicateLockAcquire(const PREDICATELOCKTARGETTAG *targettag)
+PredicateLockAcquire(const PREDICATELOCKTARGETTAG *targettag, Relation relation)
 {
     uint32        targettaghash;
     bool        found;
@@ -2395,7 +2408,7 @@ PredicateLockAcquire(const PREDICATELOCKTARGETTAG *targettag)
      * coarser granularity, or whether there are finer-granularity locks to
      * clean up.
      */
-    if (CheckAndPromotePredicateLockRequest(targettag))
+    if (CheckAndPromotePredicateLockRequest(targettag, relation))
     {
         /*
          * Lock request was promoted to a coarser-granularity lock, and that
@@ -2431,7 +2444,7 @@ PredicateLockRelation(Relation relation, Snapshot snapshot)
     SET_PREDICATELOCKTARGETTAG_RELATION(tag,
                                         relation->rd_node.dbNode,
                                         relation->rd_id);
-    PredicateLockAcquire(&tag);
+    PredicateLockAcquire(&tag, relation);
 }
 
 /*
@@ -2455,7 +2468,7 @@ PredicateLockPage(Relation relation, BlockNumber blkno, Snapshot snapshot)
                                     relation->rd_node.dbNode,
                                     relation->rd_id,
                                     blkno);
-    PredicateLockAcquire(&tag);
+    PredicateLockAcquire(&tag, relation);
 }
 
 /*
@@ -2518,7 +2531,7 @@ PredicateLockTuple(Relation relation, HeapTuple tuple, Snapshot snapshot)
                                      relation->rd_id,
                                      ItemPointerGetBlockNumber(tid),
                                      ItemPointerGetOffsetNumber(tid));
-    PredicateLockAcquire(&tag);
+    PredicateLockAcquire(&tag, relation);
 }
 
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ddad71a10f..ca75de7df6 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1919,6 +1919,8 @@ psql_completion(const char *text, int start, int end)
             "autovacuum_vacuum_threshold",
             "fillfactor",
             "parallel_workers",
+            "max_pred_locks_per_relation",
+            "max_pred_locks_per_page",
             "log_autovacuum_min_duration",
             "toast.autovacuum_enabled",
             "toast.autovacuum_freeze_max_age",
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index a617a7cf56..0e5722f6ed 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -277,6 +277,8 @@ typedef struct StdRdOptions
     bool        user_catalog_table;        /* use as an additional catalog
                                          * relation */
     int            parallel_workers;        /* max number of parallel workers */
+    int            max_predicate_locks_per_relation; /* max number of predicate locks */
+    int            max_predicate_locks_per_page; /* max number of predicate locks per page */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR            10
@@ -325,6 +327,25 @@ typedef struct StdRdOptions
      ((StdRdOptions *) (relation)->rd_options)->parallel_workers : (defaultpw))
 
 
+/*
+ * RelationGetMaxPredicateLocksPerRelation
+ *        Returns the relation's max_predicate_locks_per_relation reloption setting.
+ *        Note multiple eval of argument!
+ */
+#define RelationGetMaxPredicateLocksPerRelation(relation, defaultmpl) \
+    ((relation)->rd_options ? \
+     ((StdRdOptions *) (relation)->rd_options)->max_predicate_locks_per_relation : (defaultmpl))
+
+/*
+ * RelationGetMaxPredicateLocksPerPage
+ *        Returns the relation's max_predicate_locks_per_page reloption setting.
+ *        Note multiple eval of argument!
+ */
+#define RelationGetMaxPredicateLocksPerPage(relation, defaultmplpp) \
+    ((relation)->rd_options ? \
+     ((StdRdOptions *) (relation)->rd_options)->max_predicate_locks_per_page : (defaultmplpp))
+
+
 /*
  * ViewOptions
  *        Contents of rd_options for views
-- 
2.11.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers