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

And one more question:

Why this incident has been observed when the statement is only within
a function with variable as input parameter and not when they run
directly with explicitly defined parameter/ In the first case, plan
remains stable and does not change; but in the second case plan
changes.

Anyway, this is too technical for me and even if you respond most
probably I am not gonna get it.

Tia

On 10/27/18, GPT <gptmailinglists@gmail.com> wrote:
> On 10/26/18, Christoph Moench-Tegeder <cmt@burggraben.net> wrote:
>> ## GPT (gptmailinglists@gmail.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!
> +
>> 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!!!))
> +
>> 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!
> +
> + 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!´
> + 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.
>>
>>
>