Hi, This patch series implements the TODO in predicate.c that suggests adding ratio-based predicate lock limits. (Resending with patch attachment, cause previous send was missing the patch file)
The series is split into two logical commits: 1. Core implementation of the GUC variables and predicate lock logic 2. Comprehensive regression tests Patch overview: - Adds max_predicate_locks_per_relation_ratio and max_predicate_locks_per_page_ratio GUCs - When ratios are > 0, calculates lock limits based on relation size estimates - Maintains full backward compatibility (default 0.0 = use existing fixed limits) - Includes complete regression test coverage This addresses the TODO comment in MaxPredicateChildLocks() for more intelligent lock allocation in mixed workloads. Looking forward to your review! ArkadySkv
From 9b6a963d428790c78b54688edfeecd6d53f3d030 Mon Sep 17 00:00:00 2001 From: ArkadySkv <[email protected]> Date: Tue, 28 Oct 2025 12:04:45 +0400 Subject: [PATCH 1/2] Add dynamic predicate lock ratio limits Add max_predicate_locks_per_relation_ratio and max_predicate_locks_per_page_ratio GUCs. These parameters allow setting ratio-based limits for predicate lock promotion, addressing the TODO comment in predicate.c. When set > 0, the lock limits are calculated based on relation size estimates rather than using fixed limits. Maintains backward compatibility with default ratio of 0.0 (disabled). --- src/backend/storage/lmgr/predicate.c | 66 ++++++++++++++++++++++- src/backend/utils/misc/guc.c | 13 ++++- src/backend/utils/misc/guc_parameters.dat | 18 +++++++ src/include/utils/guc.h | 3 ++ 4 files changed, 97 insertions(+), 3 deletions(-) diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c index c1d8511ad17..4080b72af00 100644 --- a/src/backend/storage/lmgr/predicate.c +++ b/src/backend/storage/lmgr/predicate.c @@ -200,6 +200,8 @@ #include "access/parallel.h" #include "access/slru.h" #include "access/transam.h" +#include "access/relscan.h" +#include "access/table.h" #include "access/twophase.h" #include "access/twophase_rmgr.h" #include "access/xact.h" @@ -383,6 +385,10 @@ int max_predicate_locks_per_page; /* in guc_tables.c */ */ static PredXactList PredXact; +/* GUC variables for predicate lock ratios */ +extern double max_predicate_locks_per_relation_ratio; +extern double max_predicate_locks_per_page_ratio; + /* * This provides a pool of RWConflict data elements to use in conflict lists * between transactions. @@ -2268,6 +2274,31 @@ DeleteChildTargetLocks(const PREDICATELOCKTARGETTAG *newtargettag) LWLockRelease(SerializablePredicateListLock); } +/* + * Get number of blocks for a relation, handling cases where + * the relation might not exist or be accessible + */ +static BlockNumber +RelationGetNumberOfBlocks(Oid relid) +{ + Relation rel; + BlockNumber nblocks = 0; + + /* Quick exit for invalid relid */ + if (!OidIsValid(relid)) + return 0; + + /* Try to open the relation */ + rel = relation_open(relid, AccessShareLock); + if (rel == NULL) + return 0; + + nblocks = RelationGetNumberOfBlocks(rel); + relation_close(rel, AccessShareLock); + + return nblocks; +} + /* * Returns the promotion limit for a given predicate lock target. This is the * max number of descendant locks allowed before promoting to the specified @@ -2291,13 +2322,43 @@ MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag) switch (GET_PREDICATELOCKTARGETTAG_TYPE(*tag)) { case PREDLOCKTAG_RELATION: - return max_predicate_locks_per_relation < 0 + { + int base_limit = max_predicate_locks_per_relation < 0 ? (max_predicate_locks_per_xact / (-max_predicate_locks_per_relation)) - 1 : max_predicate_locks_per_relation; + + /* Apply ratio limit if configured */ + if (max_predicate_locks_per_relation_ratio > 0.0) + { + Oid relid = GET_PREDICATELOCKTARGETTAG_RELATION(*tag); + BlockNumber nblocks = RelationGetNumberOfBlocks(relid); + + if (nblocks > 0) + { + int ratio_limit = (int) (nblocks * max_predicate_locks_per_relation_ratio); + return Min(base_limit, ratio_limit); + } + } + + return base_limit; + } case PREDLOCKTAG_PAGE: - return max_predicate_locks_per_page; + { + int base_limit = max_predicate_locks_per_page; + + /* Apply ratio limit if configured */ + if (max_predicate_locks_per_page_ratio > 0.0) + { + /* For pages, use a conservative estimate */ + int estimated_tuples_per_page = 100; /* reasonable default */ + int ratio_limit = (int) (estimated_tuples_per_page * max_predicate_locks_per_page_ratio); + return Min(base_limit, ratio_limit); + } + + return base_limit; + } case PREDLOCKTAG_TUPLE: @@ -2316,6 +2377,7 @@ MaxPredicateChildLocks(const PREDICATELOCKTARGETTAG *tag) /* * For all ancestors of a newly-acquired predicate lock, increment + * * their child count in the parent hash table. If any of them have * more descendants than their promotion threshold, acquire the * coarsest such lock. diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index a82286cc98a..c567b42010a 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -83,6 +83,18 @@ char *GUC_check_errmsg_string; char *GUC_check_errdetail_string; char *GUC_check_errhint_string; +/* + * Ratio-based limits for predicate lock promotion. + * + * These GUCs enable dynamic calculation of predicate lock limits based + * on relation size estimates. A value of 0.0 disables ratio limits, + * falling back to the static max_predicate_locks_per_relation and + * max_predicate_locks_per_page settings. + * + * When enabled, the effective limit is MIN(static_limit, size * ratio). + */ +double max_predicate_locks_per_relation_ratio = 0.0; +double max_predicate_locks_per_page_ratio = 0.0; /* * Unit conversion tables. @@ -195,7 +207,6 @@ static const char *const map_old_guc_names[] = { NULL }; - /* Memory context holding all GUC-related data */ static MemoryContext GUCMemoryContext; diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index d6fc8333850..897e11d8374 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -1546,6 +1546,24 @@ max => 'INT_MAX', }, +{ name => 'max_predicate_locks_per_relation_ratio', type => 'real', context => 'PGC_SUSET', group => 'LOCK_MANAGEMENT', + short_desc => 'Sets the maximum ratio of predicate locks per relation.', + long_desc => 'If greater than 0, limits predicate locks for a relation to this ratio times the estimated page count.', + variable => 'max_predicate_locks_per_relation_ratio', + boot_val => '0.0', + min => '0.0', + max => '1.0', +}, + +{ name => 'max_predicate_locks_per_page_ratio', type => 'real', context => 'PGC_SUSET', group => 'LOCK_MANAGEMENT', + short_desc => 'Sets the maximum ratio of predicate locks per page.', + long_desc => 'If greater than 0, limits predicate locks for a page to this ratio times the estimated tuple count.', + variable => 'max_predicate_locks_per_page_ratio', + boot_val => '0.0', + min => '0.0', + max => '1.0', +}, + { name => 'authentication_timeout', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH', short_desc => 'Sets the maximum allowed time to complete client authentication.', flags => 'GUC_UNIT_S', diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index f21ec37da89..7fffaa0191b 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -304,6 +304,9 @@ extern PGDLLIMPORT double log_statement_sample_rate; extern PGDLLIMPORT double log_xact_sample_rate; extern PGDLLIMPORT char *backtrace_functions; +extern PGDLLIMPORT double max_predicate_locks_per_relation_ratio; +extern PGDLLIMPORT double max_predicate_locks_per_page_ratio; + extern PGDLLIMPORT int temp_file_limit; extern PGDLLIMPORT int num_temp_buffers; -- 2.43.0 From 98f61964c4ef1b951a520af9e89385be7a865f4c Mon Sep 17 00:00:00 2001 From: ArkadySkv <[email protected]> Date: Tue, 28 Oct 2025 12:05:26 +0400 Subject: [PATCH 2/2] Add regression tests for predicate lock ratio GUCs Tests include: - Default value verification - Setting valid values - Boundary value testing - Invalid value rejection - Reset functionality --- .../expected/predicate_lock_ratios.out | 43 +++++++++++++++++++ .../regress/sql/predicate_lock_ratios.sql | 24 +++++++++++ 2 files changed, 67 insertions(+) create mode 100644 src/test/regress/expected/predicate_lock_ratios.out create mode 100644 src/test/regress/sql/predicate_lock_ratios.sql diff --git a/src/test/regress/expected/predicate_lock_ratios.out b/src/test/regress/expected/predicate_lock_ratios.out new file mode 100644 index 00000000000..602a4969b3e --- /dev/null +++ b/src/test/regress/expected/predicate_lock_ratios.out @@ -0,0 +1,43 @@ +-- Test GUCs for predicate lock ratios + +-- Check that new GUC variables exist and have default values +SHOW max_predicate_locks_per_relation_ratio; + max_predicate_locks_per_relation_ratio +---------------------------------------- + 0.0 +(1 row) + +SHOW max_predicate_locks_per_page_ratio; + max_predicate_locks_per_page_ratio +------------------------------------- + 0.0 +(1 row) + +-- Test setting the GUCs to valid values +SET max_predicate_locks_per_relation_ratio = 0.1; +SHOW max_predicate_locks_per_relation_ratio; + max_predicate_locks_per_relation_ratio +---------------------------------------- + 0.1 +(1 row) + +SET max_predicate_locks_per_page_ratio = 0.5; +SHOW max_predicate_locks_per_page_ratio; + max_predicate_locks_per_page_ratio +------------------------------------- + 0.5 +(1 row) + +-- Test boundary values +SET max_predicate_locks_per_relation_ratio = 0.0; -- should disable +SET max_predicate_locks_per_relation_ratio = 1.0; -- max value + +-- Test invalid values (should fail) +SET max_predicate_locks_per_relation_ratio = -0.1; +ERROR: value -0.1 is outside the valid range for parameter "max_predicate_locks_per_relation_ratio" (0.0 .. 1.0) +SET max_predicate_locks_per_page_ratio = 1.1; +ERROR: value 1.1 is outside the valid range for parameter "max_predicate_locks_per_page_ratio" (0.0 .. 1.0) + +-- Reset to defaults +RESET max_predicate_locks_per_relation_ratio; +RESET max_predicate_locks_per_page_ratio; diff --git a/src/test/regress/sql/predicate_lock_ratios.sql b/src/test/regress/sql/predicate_lock_ratios.sql new file mode 100644 index 00000000000..b61c456b03d --- /dev/null +++ b/src/test/regress/sql/predicate_lock_ratios.sql @@ -0,0 +1,24 @@ +-- Test GUCs for predicate lock ratios + +-- Check that new GUC variables exist and have default values +SHOW max_predicate_locks_per_relation_ratio; +SHOW max_predicate_locks_per_page_ratio; + +-- Test setting the GUCs to valid values +SET max_predicate_locks_per_relation_ratio = 0.1; +SHOW max_predicate_locks_per_relation_ratio; + +SET max_predicate_locks_per_page_ratio = 0.5; +SHOW max_predicate_locks_per_page_ratio; + +-- Test boundary values +SET max_predicate_locks_per_relation_ratio = 0.0; -- should disable +SET max_predicate_locks_per_relation_ratio = 1.0; -- max value + +-- Test invalid values (should fail) +SET max_predicate_locks_per_relation_ratio = -0.1; +SET max_predicate_locks_per_page_ratio = 1.1; + +-- Reset to defaults +RESET max_predicate_locks_per_relation_ratio; +RESET max_predicate_locks_per_page_ratio; -- 2.43.0
