auto_explain & FDW

David Greco wrote:

In my development environment, I am using the auto_explain module to help debug queries the developers
complain about being slow. I am also using the oracle_fdw to perform queries against some oracle
servers. These queries are generally very slow and the application allows them to be. The trouble is
that it appears auto_explain kicks in on the query to try and explain them when they take longer than
the configured threshold. In this particular case, the Oracle user is very locked down and cannot
actually perform an explain. Therefore an error gets raised to the client.

I would suggest one of two things- either make the error that gets raised simply be a notice/warning,
or preferably just add an option to auto_explain to enable/disable its operation on queries involving
foreign servers.

I'm reluctant to change oracle_fdw to not throw an error if
it doesn't have the permission to explain the query when
you ask it to --- for one, what should it return in that case?

I'd say that the solution in this case would be to temporarily
allow the user to query the necessary Oracle catalogs.
If you debug in a production scenario, you'll have to make
compromises (similar to granting the PLUSTRACE role if you want
to use AUTOTRACE with SQL*Plus).

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Friday, June 28, 2013 4:05 AM
To: David Greco; pgsql-general@postgresql.org
Subject: RE: auto_explain & FDW

David Greco wrote:

In my development environment, I am using the auto_explain module to
help debug queries the developers complain about being slow. I am also
using the oracle_fdw to perform queries against some oracle servers.
These queries are generally very slow and the application allows them
to be. The trouble is that it appears auto_explain kicks in on the
query to try and explain them when they take longer than the configured threshold. In this particular case, the Oracle user is very locked down and cannot actually perform an explain. Therefore an error gets raised to the client.

I would suggest one of two things- either make the error that gets
raised simply be a notice/warning, or preferably just add an option to
auto_explain to enable/disable its operation on queries involving foreign servers.

I'm reluctant to change oracle_fdw to not throw an error if it doesn't have the permission to explain the query when you ask it to --- for one, what should it >return in that case?

I'd say that the solution in this case would be to temporarily allow the user to query the necessary Oracle catalogs.
If you debug in a production scenario, you'll have to make compromises (similar to granting the PLUSTRACE role if you want to use AUTOTRACE with >SQL*Plus).

I'm inclined to agree. The problem with granting the user in Oracle the permissions is that we simply do not have control over this. The Oracle database is maintained by a separate company. I have requested the rights, but it up to their DBAs discretion.

Therefore, I'd suggest adding an option to auto_explain to enable or disable for foreign servers, or perhaps a separate threshold setting for them.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general