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 1lAycO-0006Rd-8Q for pgsql-bugs@arkaria.postgresql.org; Sat, 13 Feb 2021 17:22:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lAycN-0001JJ-6v for pgsql-bugs@arkaria.postgresql.org; Sat, 13 Feb 2021 17:22:03 +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 1lAycN-0001JC-0j for pgsql-bugs@lists.postgresql.org; Sat, 13 Feb 2021 17:22:03 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lAycK-0002xg-VS for pgsql-bugs@lists.postgresql.org; Sat, 13 Feb 2021 17:22:02 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 11DHLw5S1661462; Sat, 13 Feb 2021 12:21:58 -0500 From: Tom Lane To: =?UTF-8?B?THVrYSDFvWl0bmlr?= cc: pgsql-bugs@lists.postgresql.org Subject: Re: Unexpected serialization error In-reply-to: References: Comments: In-reply-to =?UTF-8?B?THVrYSDFvWl0bmlr?= message dated "Fri, 12 Feb 2021 19:14:17 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1661460.1613236918.1@sss.pgh.pa.us> Date: Sat, 13 Feb 2021 12:21:58 -0500 Message-ID: <1661461.1613236918@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk =?UTF-8?B?THVrYSDFvWl0bmlr?= writes: > I had no luck over at general slack channel. So I'm beginning to treat this > as a bug. Here's a test case that unexpectedly fails at last insert. I > expect it not to fail because the rows that the two transactions act on are > unrelated to one another. The bug is that you're assuming exact tracking of the SSI serialization rules. It's not done that way, because it'd be prohibitively expensive. (I've not dug into this example in any detail, but I suspect it's acquiring page-level not tuple-level predicate locks, thus the transactions conflict because each tries to update a page the other one already read.) The short answer is that ANY application that's depending on serializable mode MUST be prepared to retry serialization failures. You don't get to skip that just because there theoretically shouldn't be a failure. regards, tom lane