pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
6+ messages / 4 participants
[nested] [flat]
* BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
@ 2023-03-14 11:18 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2023-03-14 11:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bokanist@gmail.com
The following bug has been logged on the website:
Bug reference: 17840
Logged by: Sebastien Caunes
Email address: bokanist@gmail.com
PostgreSQL version: 13.2
Operating system: Linux
Description:
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 ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
@ 2023-03-14 14:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-03-14 14:10 UTC (permalink / raw)
To: bokanist@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
@ 2023-03-15 16:49 Sébastien <bokanist@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Sébastien @ 2023-03-15 16:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
@ 2023-03-15 19:33 David G. Johnston <david.g.johnston@gmail.com>
parent: Sébastien <bokanist@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2023-03-15 19:33 UTC (permalink / raw)
To: bokanist@gmail.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On Wed, Mar 15, 2023 at 11:24 AM Sébastien <bokanist@gmail.com> wrote:
> 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.
>
>
It may be an undesired behavior, and I do sympathize with the position, but
the current design is quite intentional and long-standing and is not going
to be treated as a bug to be fixed immediately and back-patched.
A query you can execute should be incapable of failing if EXPLAIN for the
same query is issued. If some interplay with oracle_fdw causes this
fundamental assumption to be falsified then auto_explain, which works under
that assumption, and oracle_fdw are incompatible and you will need to
decide which one (possibly on a per-transaction basis) you wish to use.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
@ 2023-03-15 22:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-03-15 22:06 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: bokanist@gmail.com; pgsql-bugs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> A query you can execute should be incapable of failing if EXPLAIN for the
> same query is issued.
Indeed. This should be especially true for auto_explain, which isn't
even doing a re-parse or re-plan, but just dumping data from the
executor state tree for the just-finished query. Barring edge cases
like out-of-memory, it really shouldn't fail; so I see no reason why
we should consider major structural changes to make it (perhaps) less
likely to fail.
I continue to think that the most likely explanation is oracle_fdw
doing something it probably shouldn't be doing. I have no interest
in poking into that code myself, though.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback.
@ 2023-03-16 07:40 Sébastien <bokanist@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Sébastien @ 2023-03-16 07:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org
Thank you for your answers.
Now I understand the problem have been understood.
Oracle_fdw author is working on this.
Best regards
Le mer. 15 mars 2023, 23:06, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > A query you can execute should be incapable of failing if EXPLAIN for the
> > same query is issued.
>
> Indeed. This should be especially true for auto_explain, which isn't
> even doing a re-parse or re-plan, but just dumping data from the
> executor state tree for the just-finished query. Barring edge cases
> like out-of-memory, it really shouldn't fail; so I see no reason why
> we should consider major structural changes to make it (perhaps) less
> likely to fail.
>
> I continue to think that the most likely explanation is oracle_fdw
> doing something it probably shouldn't be doing. I have no interest
> in poking into that code myself, though.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-03-16 07:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-14 11:18 BUG #17840: Failing to execute auto_explain for logging leads to transaction rollback. PG Bug reporting form <noreply@postgresql.org>
2023-03-14 14:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-15 16:49 ` Sébastien <bokanist@gmail.com>
2023-03-15 19:33 ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-15 22:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-16 07:40 ` Sébastien <bokanist@gmail.com>
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