Re: Moving to Postgresql database

On Tue, Jan 16, 2024 at 10:59 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
> > But sure, I take your point, you can emulate statement-level (implicit)
> > rollback via an explicit SAVEPOINT, and ROLLBACK to the savepoint
> instead.
>
> > But my point remains, that something like what that extension does should
> > be an option of PostgreSQL itself, not an extension. --DD
>
> > PS: I'd also be happy to hear why it's not, or won't be, on technical
> terms.
>
> The reason it's not going to happen is that the community (or at least
> the more senior developers) still remembers what happened the last
> time we tried it.
>
> We did implement server-side auto-rollback years ago in PG 7.3,
> and it was enough of a disaster that we took it out again in 7.4.
>

Thanks Tom. That's insightful, and obviously something I didn't know.

> The problem is that now you have a switch somewhere (whether a GUC
> or something else, still a switch) that fundamentally changes the
> transactional semantics seen by applications. Run an application
> in the wrong mode and you have a broken app. Worse, there is an
> awful lot of client-side code that now has to cope with both
> behaviors. We thought that would be okay ... well, it wasn't.
> It was a mess. It would be a bigger mess now if we were to try it
> again, because there would be even more broken client code.
>

OK. That speaks against making it the default for sure.
But what if the client-code explicitly opts-in to that mode/switch?

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?

Basically implicit-statement-level-rollback is the norm, AFAIK, and
PostgreSQL is the exception here.
This creates frictions for ports to PostrgeSQL, and cross-RDBMBS apps in
general.
Thus if it was at least possible to opt-in to it, that would be a great
advance IMHO.

Client backend processes are per-user-per-DB. Would such a switch be
applied to the DB?
DBs are typically tailored to specific applications, this something like
this would work.
Thus all backends accessing a DB that opted-in to
statement-implicit-rollback would use it (by default)?
Or could that be decided on a per-client-backend basis?

I know the discussion will probably stop here. It's unlikely to happen, I
get that.
I think that's a pity, especially since there's a proof of concept, which I
assume if technically valid.

Thanks, --DD