Re: BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.

What more can I say ? It's clear that the auto_explain feature, which is a
logging process, can make perfectly fine transactions being rolled back.
That makes no sense, the transaction should be committed before any logging
event is triggered, for me it's a bug in the core of postgres. It has
nothing to do with the plugin used.

----
Postgres opens a transaction containing a query that import data.
Everything works fine until just before the commit (I guess).
Postgres triggers the auto_explain logging function which has nothing to do
with the transaction itself and the integrity of data.
The explain query fails, it is allowed to, that's not a bug of oracle_fdw.
Postgres rollbacks the transaction because of the failure in its own
logging process.

Le mar. 14 mars 2023 à 15:10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > I had this problem when importing data from oracle with oracle_fdw and
> > auto_explain option set on the postgres server : If the query took too
> long,
> > it triggers the auto_explain wich queries things on oracle side where
> there
> > was a permission problem. The error bubble up outside of logging scope
> and
> > make the transaction rollback after having properly transferred dozen
> > million records (that then stood as dead tuples).
>
> > Logging is one thing with its own scope and it should never interfere
> with
> > the behavior of the software. Beside whatever reason triggering the
> error in
> > logging scope, the transaction that had no problem itself should not be
> > rollbacked. It makes no sense.
>
> > Isn't there something to refactor in Postgres ?
>
> This bug report is not actionable due to complete lack of specifics.
> Please read
>
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> Having said that, my guess is that if there's anything to be done
> it would need to be done in oracle_fdw. So you need to take this
> up with the oracle_fdw authors, not here which is for core-server
> problem reports.
>
> regards, tom lane
>

--
Sébastien Caunes
+33 6 7 229 229 7