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 1pPLEa-0004Yk-JP for pgsql-general@arkaria.postgresql.org; Tue, 07 Feb 2023 10:29:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPLEZ-0001pK-ES for pgsql-general@arkaria.postgresql.org; Tue, 07 Feb 2023 10:29:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPLEZ-0001pA-1L for pgsql-general@lists.postgresql.org; Tue, 07 Feb 2023 10:29:55 +0000 Received: from mail-wr1-x42a.google.com ([2a00:1450:4864:20::42a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pPLEW-0000st-RD for pgsql-general@lists.postgresql.org; Tue, 07 Feb 2023 10:29:54 +0000 Received: by mail-wr1-x42a.google.com with SMTP id m14so13019050wrg.13 for ; Tue, 07 Feb 2023 02:29:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=rAO4YIMVR0IEuBUbs1FLYd2G3p2LxUzQfCEFFIxOiRI=; b=bkNSx8c3sXLrYwJxxPgmLqiL5SfYJDYN8M+nxpD4fk8WhmZJpn86rt9/tDCda4yO6E hA0bsMR6LFbjvUlTvekTMIOEM9kpeBty/vCXVEYwsdJb5zfBwRL0rLo3nn5KtcfzeNm+ +rOWTCCXHb3XrtwiAbeb9Lxijrb60TUsEDB/ffDZstqAN9M5eqKoz+9QL7NiJTZvgaok I71CS8vLblGdc1XP2MdwbDGFiae9dUD1BM2NWQ+leg8HxedTI3mfE4mC8whfsMx8O/F1 HMX+7S9eWYRKPB0rcePngMesIyXOIXYvUJfz+b0rXEVhEI+hu8cvL5Lu/cIO+9kcTFe5 zBCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=rAO4YIMVR0IEuBUbs1FLYd2G3p2LxUzQfCEFFIxOiRI=; b=fE/GzcqpV+VkYMND0tKr0FXs2KhiiThDqgrmXl5tC5msS2w3l9kDRdwUEzl8BB3Q30 UpNfYZwUz8yfeitWuTvvlBGTgB4ox0Qz1vN1tsc7kWi+Zf5zFA4in66yLPDpNw5t3KFC sQK1EvT1AbeaXs5x0UIVpz8HRuMz0GzU0pbt4jjzVInMFplaW3X91Ksf36gX6GD2BLch wNoytPagK6nGpDSghwCXjWKHXEVef4JKf/jlz9CSMcu5KNLqW3NWupM+cwc+YzNF64Ra Z/cLOl59Vq7jSMOV+SvM5t7p3A670DiCeBZxT7MKWv9moC+/ZoxEElApkt/aW2FXyWEy eeDw== X-Gm-Message-State: AO0yUKVxbBH3hVchi0X7iQsbmakyvUv0QeOen29PRJEryRM1ELYQJ63/ SEJvZ/H1F4zXdq2qr3BMN3IPiW0Z0jM0ZJkVNeY= X-Google-Smtp-Source: AK7set9V4rKxBV+CdauLkT86426/wI4MxWpAqycxkk2m5auCfJgUhPFGmFrrJ56IB3X4f83I1yy25A== X-Received: by 2002:adf:ee4c:0:b0:2c3:e03e:8345 with SMTP id w12-20020adfee4c000000b002c3e03e8345mr2284125wro.66.1675765791457; Tue, 07 Feb 2023 02:29:51 -0800 (PST) Received: from localhost.localdomain ([88.116.133.170]) by smtp.gmail.com with ESMTPSA id fc13-20020a05600c524d00b003db01178b62sm20633406wmb.40.2023.02.07.02.29.50 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 07 Feb 2023 02:29:51 -0800 (PST) Message-ID: Subject: Re: Too coarse predicate locks granularity for B+ tree indexes From: Laurenz Albe To: Rinat Shigapov , pgsql-general@lists.postgresql.org Date: Tue, 07 Feb 2023 11:29:50 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 2023-02-07 at 16:23 +0600, Rinat Shigapov wrote: > I have a concurrent testsuite that runs 14 test cases. Each test case ope= rates > on a disjoint set of records, doesn't retry transactions and is run under > 'serializable' isolation level. The test data is small and likely fits wi= thin > a single tuple page. >=20 > When I finished the test suite I was surprised=C2=A0that PostgreSQL 14.5 = returns > serialization failure on every test suite run. This is no question for the hackers list; redirecting to general. That behavior sounds perfectly normal to me: if everything is in a single page, PostgreSQL probably won't use an index scan. With a sequential scan, the predicate lock will be on the whole table. So you should expect serialization failures. This is well documented. Perhaps you should use a more realistic test case with a reasonable amount of data. Yours, Laurenz Albe