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

Hi, I had a wonderful Sunday, and have no intention to change that sense!

Dear PG developers, young and/or middle age, and rest users, please
check the errors the PG gave me.

- In PG10.5 I run, out of function, a simple statement for 5 times
successfully and the 6th time I get an error "KEY is NULL". In the
meantime of these times I added, removed code, packages got updated,
etc. Suddenly, an error. Key is NULL!!!??? Check the key, write
statements to check the value of the key. Eh, it is not NULL! Hm, undo
all changes, start again! Oh, now it runs! Ok, redo the changes one by
one. Ah, ok still run. Suddenly, error again! Check again and again.
Ok check Redis. Uninstall packages, reinstall packages... Finally,
install PG9.6 make it run, install fdw to the new system, check the
environment. OK run. Keep it as it is!
- What a very very bad timing! PG11 comes into the light. OK let´s
try with PG11. Install PG11, too. A system with PG11, 10.5, 9.6. Run
the statement (for bad luck, only out of functions). One time, two
times, ...tenth time. Yupiiiiiiii works. Uninstall 9.6, uninstall
10.5, create foreign environment in the PG11, and start working again.
Call functions, one time ok, sixth time ERROR. Dup, dup, dup the head
over the wall. Grrrrrrr, why did I remove the previous versions and
system setup which worked fine??? That´s big mistake!!!. "ERROR:
unrecognized node type: 222" node!?!?!?!?!?!?!
- What a coincidence, I use microservices. Check the nodes! Is there
222 node? Check errors related to nodes. Does one is similar with what
I get? All seems good.
- Oh, man, I use pg-promise. Check if there is any error documented
which is similar with that I get. No, there is not.
- Oh, man, node.js itself!?!? Error may come from node.js. Check if
there is any error documented similar with what I get. Noooo.
- In the meantime, check again error: [XX000] This is an internal
error, [HV004] This is a fdw related error. (I am not writing from my
laptop so the above line maybe not accurate. It is what I remember.)
Both errors are listed in PG document. But I shall try again, again,
and again!

So, in order this thread to get over:
- PG developers made a drastic change. Not problem at all, more then welcome.
- I was the "lucky guy" who had a painful experience. These things
happen as Adrian wrote, and life goes on.

What I would like to ask from developers is:

Please, if possible improve the error system!

Especially when there are internal changes which may affect
dynamically the outcome (from expected results to ERROR or whatever)
of a correct statement. For example, the error would include a
note/warning similar to "... after change of plan" or "... . Plan was
changed". Such a note/warning would have saved the whole situation and
I would have something in my hand to search and ask for help from the
very beginning.

As a simple end-user and not an IT folk, I have absolutely no word on
what and how things happen under the hood. But I expect the best
response, even if an error has appeared, which will safely enlighten
me at the shortest time. Your time is valuable, my time, too. So,
let´s respect our times and do the best to protect them against waste
in future.

Thanks and have a nice day and a wonderful week!

On 10/28/18, Christoph Moench-Tegeder <cmt(at)burggraben(dot)net> wrote:
> ## GPT (gptmailinglists(at)gmail(dot)com):
>
>> 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.
>
> There you have it: that's exactly the plan caching behaviour described
> in the link I posted upthread. PL/pgSQL created a prepared statement
> on the first execution of a statement/expression inside a function,
> and, to quote that documentation:
> If the statement has no parameters, or is executed many times, the
> SPI manager will consider creating a generic plan that is not dependent
> on specific parameter values[...]
>
>> Anyway, this is too technical for me and even if you respond most
>> probably I am not gonna get it.
>
> But perhaps the next person researching similar question will profit
> from the mailing list archives.
>
> Regards,
> Christoph
>
> --
> Spare Space.
>
>