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
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