pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim Nasby <jim.nasby@gmail.com>
To: Dominique Devienne <ddevienne@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Moving to Postgresql database
Date: Wed, 17 Jan 2024 17:00:23 -0600
Message-ID: <39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com> (raw)
In-Reply-To: <CAFCRh-9RYHczWVJnfcb_-iMHkA9L3-tp0s-o9WNV3h4+QmLNaw@mail.gmail.com>
References: <CAB+=1TUyxXeRbmz6oRC+96EjLAnW7Qvqmse4iSNtWgH5i21mQg@mail.gmail.com>
	<CAFCRh-9qkLTCWPssN4ZOvGj3JY3LceMKs2eaizx49b2Cx8019Q@mail.gmail.com>
	<40a34680-1583-4cff-86cb-2598be16bb80@aklaver.com>
	<CAFCRh-9Es2Ahgzetyu-46guwyGwPjaJXHfGpF0AG4zt7KDBjfw@mail.gmail.com>
	<8cbd791a-0688-49fb-a536-3b8c30b5109b@aklaver.com>
	<CAFCRh-_hkHF1oEDRdEJnOXSz=RzXruzdpq3jdxvZ25fWLcqpbA@mail.gmail.com>
	<1904120.1705442350@sss.pgh.pa.us>
	<CAFCRh-9RYHczWVJnfcb_-iMHkA9L3-tp0s-o9WNV3h4+QmLNaw@mail.gmail.com>

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






view thread (18+ messages)  latest in thread

Message-ID: <39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com>
Permalink:  ../39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com/
Also on:    postgresql.org/message-id/39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: jim.nasby@gmail.com, ddevienne@gmail.com, tgl@sss.pgh.pa.us, adrian.klaver@aklaver.com, veema0000@gmail.com
  Subject: Re: Moving to Postgresql database
  In-Reply-To: <39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com>
  References: <CAB+=1TUyxXeRbmz6oRC+96EjLAnW7Qvqmse4iSNtWgH5i21mQg@mail.gmail.com> <CAFCRh-9qkLTCWPssN4ZOvGj3JY3LceMKs2eaizx49b2Cx8019Q@mail.gmail.com> <40a34680-1583-4cff-86cb-2598be16bb80@aklaver.com> <CAFCRh-9Es2Ahgzetyu-46guwyGwPjaJXHfGpF0AG4zt7KDBjfw@mail.gmail.com> <8cbd791a-0688-49fb-a536-3b8c30b5109b@aklaver.com> <CAFCRh-_hkHF1oEDRdEJnOXSz=RzXruzdpq3jdxvZ25fWLcqpbA@mail.gmail.com> <1904120.1705442350@sss.pgh.pa.us> <CAFCRh-9RYHczWVJnfcb_-iMHkA9L3-tp0s-o9WNV3h4+QmLNaw@mail.gmail.com> <39af2eb6-ccf5-401a-9d21-060377db6296@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox