Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsUnc-008HFc-0r for pgsql-general@arkaria.postgresql.org; Mon, 16 Oct 2023 21:06:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qsUnZ-004KJ6-L9 for pgsql-general@arkaria.postgresql.org; Mon, 16 Oct 2023 21:06:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsUnZ-004KIi-Ab for pgsql-general@lists.postgresql.org; Mon, 16 Oct 2023 21:06:50 +0000 Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qsUnW-0013ul-Cj for pgsql-general@lists.postgresql.org; Mon, 16 Oct 2023 21:06:49 +0000 Received: by mail-yb1-xb2a.google.com with SMTP id 3f1490d57ef6-d9beb863816so1567590276.1 for ; Mon, 16 Oct 2023 14:06:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697490405; x=1698095205; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=aNthf1j40LLmDHN9aVFAiG6OyD65mM6ibAi1axfvcts=; b=gX+02ItA4tD1J67mSzaZJ8uK0zjK8v5zX/mgDFKLtsiGuFIAmLqs8NaH8hJsGE8D8A YUUxoiVg8R9LsyvR5cUF6usg8Tgoo21zvA4GKa9WtVRfsYolZgYFQ6Qb5/Wjav5zFJiT wo3KDBroFKkZav6FX6QQfqpiHjAAxBaaLPHKaoCSIh2u6N55A+McyAV5iwFayGsxUv9u cvoxPnzMjuwbLiJC1FAh8UeXBwDajVmlgmcuCuNLIP3f53IEWdoHrelBWulLmxjP1Tt0 xi2Z3egXKtA16ZhUhtpnri5M1dq5Cx0DKcIRRiDnBU6b9k2z6t4wfqVlB2D9tAdnabxV muPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697490405; x=1698095205; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=aNthf1j40LLmDHN9aVFAiG6OyD65mM6ibAi1axfvcts=; b=bQ7i5xoBLmDv2oapFaEK1j7vHDsndxdlhySczwLch8aRbHP6NEgKSVy2tw1AUuqZ15 2J+C0qh/xuoo3sw6UbwAaLfgZGxr8VHbTD8lPe0Xo2LCyjWHDRwIEQNZQftuCK84Xgkz MemlHDuNvHVnYHi2xZbhQgwh11uh/qNDkckbWUA7rC8E9K411I5V3uaa21iY8Uqx4Ubl AgtP9GARNIjXIgwTlvnSXTQjr3SGLNTtvLNA/9Ua3F4Yy6AKAiMPVUewSxpMGTfRXSk7 qcvCujcLT7ey3j3lHKQCxzWfAKkX0auB8PlUe/rr6wcpPFz1oxDUSS75aM+lvBQopJjs oSYw== X-Gm-Message-State: AOJu0YzTPxpJ+iQoK8b0zTaCszZndcmxPm6rsvJgppEJE/cKPq0pLcZ5 8yIxb3q9vUAtOuEJpFVUxrRvCuokz9s= X-Google-Smtp-Source: AGHT+IGBtEvCr9CQ9b24GBOQ+E1mqGS48kmHTpX6jdLTLBM8Eq4qjuAL1WXtc2EUAYxWE3vvgbQ1mQ== X-Received: by 2002:a25:d50f:0:b0:d9b:90d4:75ec with SMTP id r15-20020a25d50f000000b00d9b90d475ecmr205147ybe.18.1697490405312; Mon, 16 Oct 2023 14:06:45 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:40a2:fd1b:5726:298c? ([2600:1700:bd2c:b810:40a2:fd1b:5726:298c]) by smtp.googlemail.com with ESMTPSA id 81-20020a250154000000b00d7b8a1074d4sm2943275ybb.57.2023.10.16.14.06.44 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Oct 2023 14:06:44 -0700 (PDT) Message-ID: <5f7399b5-a3c8-48bb-9b8f-ce7dd63ae8c1@gmail.com> Date: Mon, 16 Oct 2023 16:06:44 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Understanding max_locks_per_transaction Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <2438977.1697481141@sss.pgh.pa.us> From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10/16/23 14:31, Craig McIlwee wrote: > That's what we've already done for the short term solution.  It is > somewhat in conflict with your statement regarding the number of lockable > objects not holding still for long, though.  As time goes on and our > scheduled jobs automatically create new monthly partitions, or as our > schema evolves, we may eventually hit the limits again.  That's why we'd > like to create some formula that can estimate the > max_locks_per_transaction value we should configure (with the previously > mentioned multiplier for safety / future proofing).  An alternative would > be to precreate all partitions we anticipate needing so we don't get > surprises down the line, but then we incur extra planning cost for tables > that will stay empty for months or even years. Just set max_locks_per_transaction to Something Big, and go on to other business.  16384 worked for us, after slowly inching up towards 1000. Tom's response let me not worry about setting "so big", and we haven't had any problems since. -- Born in Arizona, moved to Babylonia.