Re: rw_redis_fdw: SQL Errors when statement is within a function

On 10/27/18 2:28 AM, GPT wrote:
> On 10/26/18, Christoph Moench-Tegeder <cmt(at)burggraben(dot)net> wrote:
>> ## GPT (gptmailinglists(at)gmail(dot)com):
>>
>> ...
>>
>> 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(dot)klaver(at)aklaver(dot)com