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 1rQEtc-00HUji-VA for pgsql-general@arkaria.postgresql.org; Wed, 17 Jan 2024 23:00:33 +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 1rQEtb-00GUPt-Ey for pgsql-general@arkaria.postgresql.org; Wed, 17 Jan 2024 23:00:31 +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 1rQEtb-00GUPl-3k for pgsql-general@lists.postgresql.org; Wed, 17 Jan 2024 23:00:31 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rQEtY-002MlM-8z for pgsql-general@lists.postgresql.org; Wed, 17 Jan 2024 23:00:30 +0000 Received: by mail-ot1-x336.google.com with SMTP id 46e09a7af769-6dc8b280155so4600262a34.0 for ; Wed, 17 Jan 2024 15:00:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705532426; x=1706137226; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=J6IdO16AYMkNQ0TG5eOWkRmrbQq5f+Xd1h8Vb/CV1vc=; b=OGG2frsD5Rz6/dDW2Mhp6c3aHj4Uzwcn14y+rQjWNTi3Gv6/cJRb6u4OF5eyOthXLK 4zAByJf87BMN6lYCAsPyRX1tq5HIdbeD6e3GuBOE0Bd95niDcaifMCYTa/6f6C8YOGS6 XTrLAx/DqvyAK18FNWkLWxGPwnacLrRPQo1UWrP12YVscbM6OmeJqxdWTckr43USDjNz K2rC89NI+2DqNVHkMbRvpmykLmapKtjuirZRp4e0bqESQK0ApSIaS+cnhj9naZtUlnSa 9Onwt1OdT29rb/gXQji1PzsDwmr/Wn/cQIAPmrQJSHDuzpF00NADGlhAvDz8M+mxe5r5 hJVQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705532426; x=1706137226; h=content-transfer-encoding:in-reply-to:from:references:cc: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=J6IdO16AYMkNQ0TG5eOWkRmrbQq5f+Xd1h8Vb/CV1vc=; b=n4O9joachASQkUmdrp4b2hAx7jhH39GDXqTOqejdDmzPAqnoCnQIWYRatzYk2j9XqL 3I7XgWVritFOHffBtzfTJX3LI5fkCyqFnPEyAenfF6VIJ/C3RtJbHCZwkvjyQp7K+YgA kcXs0uIeSGYB7LLqgtniaxpsjnjUR7Et99tritaigt11Nb36Z2pJq1PjgleWHlBMVN9n lSNmNniiWRSQR0qHLOqxuiGWQxEe4I0F0dUFZHhWf/ziAZE42xkcF8qmY4mqfUb3g1MG vO5Ago2LWbQnnUDFIXyWkLPJV/EvyCOqxt/JZQur460ELBgPJ0xuePIF1c1W2zrF8kev VV6g== X-Gm-Message-State: AOJu0Ywtt8z182hL4fbPf4IKcUvkMCtj13n/sB082kXPLwJ5AGdr3CIQ tMd7fCsKrR/EDUqdrCWv3zw7EU3kHzUr X-Google-Smtp-Source: AGHT+IEjlUDL2Z8PS+5LsvP1dordvq3KwmakX3rcAnIZgfVOTMB+m1ZhTCrOxx4dBubohXbt91BJPg== X-Received: by 2002:a05:6870:1593:b0:210:b649:836e with SMTP id j19-20020a056870159300b00210b649836emr455217oab.1.1705532426454; Wed, 17 Jan 2024 15:00:26 -0800 (PST) Received: from [192.168.2.6] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id gu27-20020a056870ab1b00b00206516474f3sm95426oab.38.2024.01.17.15.00.25 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 17 Jan 2024 15:00:26 -0800 (PST) Message-ID: <39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com> Date: Wed, 17 Jan 2024 17:00:23 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Moving to Postgresql database Content-Language: en-US To: Dominique Devienne , Tom Lane Cc: Adrian Klaver , veem v , pgsql-general References: <40a34680-1583-4cff-86cb-2598be16bb80@aklaver.com> <8cbd791a-0688-49fb-a536-3b8c30b5109b@aklaver.com> <1904120.1705442350@sss.pgh.pa.us> From: Jim Nasby In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/17/24 5:31 AM, Dominique Devienne wrote: > Is the pg_statement_rollback technically wrong? Can't what it does be done > better and more efficiently if it was in the core itself? Is it a lot of > code? I took a quick look at that extension, and it's doing pretty much what you'd do if this was baked into Postgres. The performance penaltiy that you'll suffer here is that you're going to assign a new transaction ID for every statement, which can be significantly more expensive than using one XID per BEGIN/COMMIT (depending of course on how many statements you have inside a BEGIN/COMMIT). By the way, you might take a look at Babelfish[1] since it has to solve this issue as well due to some of the error handling modes that T-SQL supports. > Basically implicit-statement-level-rollback is the norm, AFAIK, and > PostgreSQL is the exception here. I'm really curious what other databases you've seen that have this behavior, because the only time I've ever seen it was T-SQL. Way back in Sybase 11 days it was the only behavior you had, but at some point SQL Server (and maybe Sybase) added additional options. Frankly, this paradigm has always seemed completely broken to me. The entire point of having transactions is so you have all-or-nothing behavior: either everything works or the transaction aborts. I realize that automatically rolling a statement back doesn't technically violate ACID, but IMO it definitely violates the spirit of it. While there are certainly *some* legitimate uses for rolling a statement back on error, in 30 years I've seen maybe one scenario where you'd want to roll a statement back on *any* error, and even then it was only on a specific statement - not every statement that might get sent to the server. 1: https://babelfishpg.org/ -- Jim Nasby, Data Architect, Austin TX