Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oyiOD-0006rB-DN for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 23:45:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oyiOC-0004pf-7v for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 23:45:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oyiOB-0004pW-Rg for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 23:45:47 +0000 Received: from mail-qt1-x82b.google.com ([2607:f8b0:4864:20::82b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oyiO9-00068l-9Q for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 23:45:47 +0000 Received: by mail-qt1-x82b.google.com with SMTP id l2so3337444qtq.11 for ; Fri, 25 Nov 2022 15:45:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:from:content-language:to:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=Bu4wSKBm+Nbfh+bcFTEbEcnOnoDseRcG3JNc8ceqxd8=; b=gsIqByZwUUiJ9R8OcwspfGyFJRsR+4nusuvY5vf6gcxiM2QQU0lSiC+CpDx32SYK/P f6VmNWUMUA9mxyyutv2l+JL+q7113B5hQIDYAdyDXtm9cekwomWjU1Kc3OzJhhE64eo+ ndZ79mACsfxEtho0BhB9sPC3S3oiuvqXyAwx7JeUMELJaBlwh+9xWVpxrJygiFfQI5Ce 05w1qKTlyKCsn6qacxUWYbYyVH3T1LWtQ9nXgvYLmXQhHqbQlJL0CCz/ing7a6XdPHrT lvpunceqNkXv58Dhyq9s1NFthVB4O2c2v46P+HXAvIFkrw71Hzur0PbAF85wWPUZamVI Xe7Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=subject:from:content-language:to:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Bu4wSKBm+Nbfh+bcFTEbEcnOnoDseRcG3JNc8ceqxd8=; b=ArtR1WNOxl5nr6N5ignNVqGHRJx3FJhkNDYVlApCzMveWa9iuYbZTHvMuVj1PCYtvV CLxMmj/ysNheQLEnkbDWDuG1e2Gg34isJUPH9X3NHttuZA/XaLf+HnrrC8gaCBNtRGSq ixrveoF+j1Pz+70gOrwDuVhIKISqmnA3TI3kG6de4er0selk9F6Qv3umSDrfQUK6n4gi 8xpBZJO2Jfp3JmxvtBsjS9B0ArRMAMHpGeeTa7ggwQm/PZsUvB7VF1X8n04diCMtg+iZ 5AVGjOgktPUgXehFtit91RhK/V3EA+JSM8wjJTUMeV++sqK/BjipsS28WZRq7O0xlCmC 5hdQ== X-Gm-Message-State: ANoB5pmltt3AHOqaJMUUmnDh79pMZ9dwXWsRP7MqetIQMbnErlQgLoFJ tkXsjw/km8RQUheSGqDtW4ob2LGH27A= X-Google-Smtp-Source: AA0mqf61ikr8v19O1lf+6PqgVgABPyfM7lXnzAMW1Q/syr3yDigcRhkOEFkE1ZP9EnAzHJ5Sh3pfAw== X-Received: by 2002:ac8:7497:0:b0:3a5:e592:37c8 with SMTP id v23-20020ac87497000000b003a5e59237c8mr37201844qtq.609.1669419943393; Fri, 25 Nov 2022 15:45:43 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id m21-20020a05620a24d500b006ee8874f5fasm3654342qkn.53.2022.11.25.15.45.42 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 25 Nov 2022 15:45:43 -0800 (PST) Content-Type: multipart/alternative; boundary="------------DxjtgkFovbb4u9sq9e0p4g0H" Message-ID: Date: Fri, 25 Nov 2022 17:45:42 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 To: pgsql-general@lists.postgresql.org Content-Language: en-US From: Ron Subject: Practical maximum max_locks_per_transaction? List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------DxjtgkFovbb4u9sq9e0p4g0H Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit v9.6.24 (being retired next year) https://www.postgresql.org/docs/9.6/runtime-config-locks.html "The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent table with many children." How "raised" is too raised?  I just had to increase max_locks_per_transaction from 640 to 1024 on an instance with many child tables and against which is run many large reports.  Another instance has zero children, but had to increase the value to 1280 to prevent reports from failing. -- Angular momentum makes the world go 'round. --------------DxjtgkFovbb4u9sq9e0p4g0H Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit v9.6.24 (being retired next year)

https://www.postgresql.org/docs/9.6/runtime-config-locks.html

"The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent table with many children."

How "raised" is too raised?  I just had to increase max_locks_per_transaction from 640 to 1024 on an instance with many child tables and against which is run many large reports.  Another instance has zero children, but had to increase the value to 1280 to prevent reports from failing.

--
Angular momentum makes the world go 'round.
--------------DxjtgkFovbb4u9sq9e0p4g0H--