On 10/27/18 2:28 AM, GPT wrote:
On 10/26/18, Christoph Moench-Tegeder <cmt@xxxxxxxxxxxxxx> wrote:
## GPT (gptmailinglists@xxxxxxxxx):
...
And the important thing is: there is no guarantee that the same SQL
statement will always execute with the same plan:
+ Yes but there should be guarantee that when the statement is free of
any syntactic error to be executed successfully and return the
expected result!!! This is out of discussion and any negotiation!!!
+ If I construct a ship, or an airplane or a car and you turn the
wheel to the right and the vessel, at sixth time, turns to the left
and you have even a minor crash you are not gonna accept any excuse
about the turning wheel plan change!!!
+ Here, there is an obvious problem: The outcome of a correct
syntactically statement is not the expected one. It is very very
simple! Simpler cannot be done! Only if you keep your eyes sealed
closed you cannot see it; but even then you can hear the warnings that
something is wrong.
+
One reason would be
changing table statistics,
+ As a reason is accepted, but as an excuse in order to stay inactive it is not.
+
another is when PostgreSQL switches to
the generic plan for a prepared statement.
+ Same as above.
+
Your case looks like the
latter, especially the observation "After that (6th time)" in
https://github.com/nahanni/rw_redis_fdw/issues/13#issuecomment-428670890
hints to that.
So, where does that prepared statement come from? You don't really
describe your environment...
+ Ask me what ever you believe you need to find the reason of the
failure! That´s why I have sent a message to the mailing list! I am
not looking for a date! The minimum I was expecting was to be asked
plenty questions by developers. But it never has happened!
+ So, aaaaaaaaaask me, please!
You received an explanation from the developer of rw_redi_fdw in this:
https://github.com/nahanni/rw_redis_fdw/issues/14
which is further detailed here:
https://github.com/nahanni/rw_redis_fdw/commit/05f5f3247569e6c428360cc4270606a91e57c6ff
Postgres is going to do all sorts of things under the hood when you run
a query, that is not going to change. The issue you had bubbled up to
you the user because the FDW you where using got caught by a change in
behavior. That is going to happen and the developer dealt with it and
hopefully put in a test for it, for future changes.
+
It's unlikely that you're calling PREPARE
yourself - but some drivers are notorious for that (Perl DBI's
$dbh->prepare() or JDBC's PreparedStatement come to mind),
+ Oh, excellent! I usually use DBeaver as a GUI which uses JDBC.
+ (By the way, I grub the opportunity. I use DBeaver because Admin III
does not work properly with pg10 and 11 and BECAUSE Admin4 is a
NIGHTMARE to install it and make it to work (from the point of a
simple user!!!))
I would also suggest learning psql:
https://www.postgresql.org/docs/11/static/app-psql.html
It operates closer to the database and eliminates some of the
translation issues caused by running through drivers.
+
even PL/pgSQL uses prepared statements internally:
https://www.postgresql.org/docs/11/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
+ Ah, this is an internal part!
+ So, so far, we have two candidates which maybe responsible for the
outcome failure: JDBC and PL.
+ What else you need from me to help you find out the source of the problem?
+ If JDBC is responsible for the problem, we can inform the developers
to fix the problem, if they want to hear, of course!
+ If PL is responsible for the problem, then pg developers most
probably will state "It is not a problem, it is a project decision to
behave like this! ..."
So: plans are not stable between query executions, and you may have
prepared statements without knowing that.
+ SO WHAT! Does this mean that I have to accept the failure because
plan has decided to change!
As mentioned above the failure was down to a parsing issue in the FDW.
That has been fixed.
+
+ So, if there is an airplane crash due to an autopilot unstable
self-change, we will say ´Eh, guys no problem. Autopilot changed its
plan and decided to land improperly!´
No one was saying it was not going to be fixed, just that the place to
fix it was in the FDW, which it was. Bugs happen, they get caught, they
get fixed, life moves on.
+ Or if your car uses the braking system unexpectfully, and makes your
car stop will running in high-velocity lane, and the rear car chashes
at you back, what are you gonna say ´Eh, guys no problem, from time to
time my car likes passive doggy-style crashes!´
+
+ That´s TRAGIC!
Regards,
Christoph
--
Spare Space.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
