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@sss.pgh.pa.us> a écrit :
PG Bug reporting form <noreply@postgresql.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
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.
