Обсуждение: rw_redis_fdw: SQL Errors when statement is within a function

Hi,

I have faced an incident which, according to the developer, "The
problem seems to be that pgsql switches from a CONST sub-expression
into a FUNCEXPR after a few goes, ..."

Please have a look at the following two links which describe the problem:

https://github.com/nahanni/rw_redis_fdw/issues/13
https://github.com/nahanni/rw_redis_fdw/issues/14

The first link (13) has to do with PG10.x version.
The second one (14) has to do with PG11 version in which the developer
traces the "problem".

Please check and comment.

Tia

Hi,

On 2018-10-25 11:43:39 +0200, GPT wrote:
> I have faced an incident which, according to the developer, "The
> problem seems to be that pgsql switches from a CONST sub-expression
> into a FUNCEXPR after a few goes, ..."
> 
> Please have a look at the following two links which describe the problem:
> 
> https://github.com/nahanni/rw_redis_fdw/issues/13
> https://github.com/nahanni/rw_redis_fdw/issues/14
> 
> The first link (13) has to do with PG10.x version.
> The second one (14) has to do with PG11 version in which the developer
> traces the "problem".
> 
> Please check and comment.

The developer of the FDW seems to have made the requisite fixes. I'm not
sure what you'd like us to do?

Greetings,

Andres Freund

So, this kind of switch after a few goes is a normal behavior or
something unexpected which will change in future?

I am asking in order to have my mind on incidents with similar behavior.

Tia


On 10/25/18, Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> On 2018-10-25 11:43:39 +0200, GPT wrote:
>> I have faced an incident which, according to the developer, "The
>> problem seems to be that pgsql switches from a CONST sub-expression
>> into a FUNCEXPR after a few goes, ..."
>>
>> Please have a look at the following two links which describe the problem:
>>
>> https://github.com/nahanni/rw_redis_fdw/issues/13
>> https://github.com/nahanni/rw_redis_fdw/issues/14
>>
>> The first link (13) has to do with PG10.x version.
>> The second one (14) has to do with PG11 version in which the developer
>> traces the "problem".
>>
>> Please check and comment.
>
> The developer of the FDW seems to have made the requisite fixes. I'm not
> sure what you'd like us to do?
>
> Greetings,
>
> Andres Freund
>

## GPT (gptmailinglists@gmail.com):

> So, this kind of switch after a few goes is a normal behavior or
> something unexpected which will change in future?

It's expected, and even documented (when you look at the user-level
interface):
https://www.postgresql.org/docs/current/static/sql-prepare.html
  Prepared statements can use generic plans rather than re-planning
  with each set of supplied EXECUTE values. This occurs immediately
  for prepared statements with no parameters; otherwise it occurs
  only after five or more executions produce plans whose estimated
  cost average (including planning overhead) is more expensive than
  the generic plan cost estimate.

Regards,
Christoph

-- 
Spare Space.

I have searched in
https://github.com/nahanni/rw_redis_fdw/blob/master/redis_fdw.c for
PREPARE and EXECUTE keywords. There are not any of them, except in
comments.

So, the developer doesn´t use any PREPARE, EXECUTE statements.

So, this change occurs internally. If I am correct then the PG fails
to respond properly.

Tia

On 10/25/18, Christoph Moench-Tegeder <cmt@burggraben.net> wrote:
> ## GPT (gptmailinglists@gmail.com):
>
>> So, this kind of switch after a few goes is a normal behavior or
>> something unexpected which will change in future?
>
> It's expected, and even documented (when you look at the user-level
> interface):
> https://www.postgresql.org/docs/current/static/sql-prepare.html
>   Prepared statements can use generic plans rather than re-planning
>   with each set of supplied EXECUTE values. This occurs immediately
>   for prepared statements with no parameters; otherwise it occurs
>   only after five or more executions produce plans whose estimated
>   cost average (including planning overhead) is more expensive than
>   the generic plan cost estimate.
>
> Regards,
> Christoph
>
> --
> Spare Space.
>
>

## GPT (gptmailinglists@gmail.com):

> I have searched in
> https://github.com/nahanni/rw_redis_fdw/blob/master/redis_fdw.c for
> PREPARE and EXECUTE keywords. There are not any of them, except in
> comments.

Of course not - the FDW does not execute SQL on the PostgreSQL side,
but sends commands to redis.
Still, the FDW operations are part of PostgreSQL's query plan; they
have to be - everything PostgreSQL does is organized in plans, and the
operations done on the Foreign Server (redis in this case) have to
have their interfaces in the plan.

And the important thing is: there is no guarantee that the same SQL
statement will always execute with the same plan: One reason would be
changing table statistics, another is when PostgreSQL switches to
the generic plan for a prepared statement. 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... 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), and
even PL/pgSQL uses prepared statements internally:
https://www.postgresql.org/docs/11/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

So: plans are not stable between query executions, and you may have
prepared statements without knowing that.

Regards,
Christoph

-- 
Spare Space.

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.
>
>

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.
>>
>>
>

On 10/27/18 2:28 AM, GPT 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!

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@aklaver.com

On 10/27/18 3:57 AM, GPT wrote:
> And one more question:
> 
> Anyway, this is too technical for me and even if you respond most
> probably I am not gonna get it.

Then why ask the question?

> 
> Tia
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/27/18, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 10/27/18 2:28 AM, GPT wrote:
> ...
> Postgres is going to do all sorts of things under the hood when you run
> a query, that is not going to change.
+ Ok. That's clear.
+
> The issue you had bubbled up to
> you the user because the FDW you where using got caught by a change in
> behavior.
+ OK. I keep that in mind for future similar incidents. It is not a
problem in my function, or in Redis, neither my system has suddenly
broken, neither re-installation of packages is required, neither
wοndering hours what has suddenly happened and a very simple sql
statement does not get executed,...
+
> That is going to happen and the developer dealt with it and
> hopefully put in a test for it, for future changes.
+ That's correct, the developer did his best.
> ...
> 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.
+ OK. Also clear!
>
> ...
+ Thanks Adrian for your short and substantial response.
+ Thanks to the other guys who has spent time to respond, too.
+ Sorry, if I have been unpleasant.
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

On 10/27/18 8:00 AM, GPT wrote:
> On 10/27/18, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 10/27/18 2:28 AM, GPT wrote:
>> ...
>> Postgres is going to do all sorts of things under the hood when you run
>> a query, that is not going to change.
> + Ok. That's clear.
> +
>> The issue you had bubbled up to
>> you the user because the FDW you where using got caught by a change in
>> behavior.
> + OK. I keep that in mind for future similar incidents. It is not a
> problem in my function, or in Redis, neither my system has suddenly
> broken, neither re-installation of packages is required, neither
> wοndering hours what has suddenly happened and a very simple sql
> statement does not get executed,...

Just to be clear the SQL works fine when run against Postgres directly. 
The issue is that the FDW reparse's the statement to make it work with 
Redis. It was the reparsing that caused the problem, this is not 
something Postgres has control over.

> +
>> That is going to happen and the developer dealt with it and
>> hopefully put in a test for it, for future changes.
> + That's correct, the developer did his best.

Something to note from here:

https://github.com/nahanni/rw_redis_fdw

"This project is currently work in progress and may have experience 
significant changes until it becomes stable. Use it with caution and at 
your own risk!

PostgreSQL version compatibility

Currently tested against PostgreSQL 9.4+, 10.5. Other versions might 
work but unconfirmed.
"

>> ...
>> 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.
> + OK. Also clear!
>>
>> ...
> + Thanks Adrian for your short and substantial response.
> + Thanks to the other guys who has spent time to respond, too.
> + Sorry, if I have been unpleasant.
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

## 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!!!

It does. Only there's often more than one way to get the correct
result; and PostgreSQL picks the plan which looks "best". And just
for the record: you haven't actually claimed that PostgreSQL returns
the wrong result. The only observation you gave was that PostgreSQL
"sometimes" switches the way to get that result. And to that the
answer is "yes, it does".

> + (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 wouldn't know about that, there are pre-built packages available
for my systems (which I can install with one command). Anyway, I
use psql for about all things PostgreSQL.

> + What else you need from me to help you find out the source of the problem?

First of all, we'd need to see a problem. As long as the correct data
is returned (and/or written), there is no obvious problem. How PostgreSQL
handles your query is for most cases an implementation detail, which
"normal" users can safely ignore.
The only problem we have seen so far was in rw_redis_fdw (and it has
been fixed) - it could not handle all the plans PostgreSQL could use.

Regards,
Christoph

-- 
Spare Space.

## GPT (gptmailinglists@gmail.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.

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@burggraben.net> wrote:
> ## GPT (gptmailinglists@gmail.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.
>
>

On 10/29/18 3:58 AM, GPT wrote:
> 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.

> happen as Adrian wrote, and life goes on.
> 
> What I would like to ask from developers is:
> 
> Please, if possible improve the error system!

The improvement is already there:

https://github.com/nahanni/rw_redis_fdw/issues/14

"Turning on debugging, the first 5 tries parses the where clause as:"

For more information see:

https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

In particular: Table 19.1. Message Severity Levels.

> 
> 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!
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

## GPT (gptmailinglists@gmail.com):

> - 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!

Your testing/debugging/validating processes seem to be a little
erratic. Sometimes it does make sense to build minimal test cases;
and to understand why things don't work or seem to be working 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.

Let me add another thing here: you're using software which is marked
as "not really ready" (rw_redis_fdw/README.md: "work in progress and may
have experience significant changes until it becomes stable") which
uses very low level, internal interfaces to PostgreSQL; in a perhaps
not-trivial project (there's PostgreSQL, Redis, rw_redis_fdw, you mentioned
node.js and microservices...). I'm not sure you really did calculate the
project risk of that. In some environments, such setup are called
"technology jenga": deep stack with an unstable equilibrium.

> 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.

I'm not sure we can do very much when external (not under any of our's
control - I guess most people here wouldn't even have known about
rw_redis_fdw until you mentioned it) components use interfaces in
a fragile way. And as it was noted several times: the developer of
that component identified the problem with the debug output (nothing
fancy here) and fixed it.

Regards,
Christoph

-- 
Spare Space

Very good morning!

If, in 2018 when the human structures are very close to reach the edge
of our universe, here on earth you are asking me (a simple end-user),
in order to run the following "complicated" and "sophisticated" SQL
statements:

INSERT INTO my_table(key, value, expiry) VALUES ('my_key', 'my_value', 10);
SELECT value FROM my_table where key = LOWER('my_key');

ten (10) times in total, to activate the debugger and check the log
files because the errors given to me were **totally wrong** ("KEY is
NULL" was not NULL) and **misleading**, then one more time: It´s
TRAGIC!

I, of course, recognise that I have made plenty mistakes! But, I could
never imagine that the most advanced open source database in the world
would behave in such a "poor" way!

@Christoph

As regards "rw_redis_fdw" I have to admit the following:
- it is honest. It informs the user from the very beginning that "This
project is currently work in progress and may have experience
significant changes until it becomes stable. Use it with caution and
at your own risk!"; although it is so far stable enough!!!
- it worked in pg9.x without problem!
- the developer did his best when I reported the problem; for my bad
luck he did not expirienced the same problem in PG10.x so as he starts
searching/debugging from the very first moment.
- after **including and NOT fixing** (because there was not any bug)
the FuncExpr subquery support, the module has been working fine and it
is stable! (until of course the next plan change that PG will induce
under the hood and brakes the interface; that´s a joke!) although the
developer uses (successfully - I add) very low level, internal
interfaces to PostgreSQL as you have already written.

And the most important:

<h1>The module **DOES** whatever claims to do without a problem, in a
very humble way!<h1>

As regards the risk of the project, I am aware of it. That´s why I am
trying to choose **reliable**, **responsible** and **trustworthy**
projects, even if they are not famous! From a very famous project, I
just recently got a burn! Do only **ONE** thing but do it
**"CORRECTLY"**! I always try to avoid rich-featured projects which,
by rule, most of the features are mis-implemented, or quality is poor!

As regards database choice, I do not have many options, PG or MariaDB
or SQLite. But, when the application is finished, it will be very easy
to maintain the application-DB interface and use any other DB. It is a
matter of translation. DB is just a tool like any other one, and not a
religous matter.

In the case of redis fdw I do not have many options. There are
two-three of them. Redis is the only db which offers TTL with very
high resolution (1 sec), and rw_redis_fdw implements TTL.

In addition, at first opportunity, I always contact the developer or
the community to get an idea of his/its mentality and the way they
work or react or keep their nose up.

Have all a nice day!

Tia

<div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br /> <table
style="border-top: 1px solid #D3D4DE;">
    <tr>
      <td style="width: 55px; padding-top: 18px;"><a
href="http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"
target="_blank"><img
src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png"
alt="" width="46" height="29" style="width: 46px; height: 29px;"
/></a></td>
        <td style="width: 470px; padding-top: 17px; color: #41424e;
font-size: 13px; font-family: Arial, Helvetica, sans-serif;
line-height: 18px;">Libre de virus. <a
href="http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"
target="_blank" style="color: #4453ea;">www.avg.com</a>         </td>
    </tr>
</table>
<a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"></a></div>

On 10/30/18 3:19 AM, GPT wrote:
> Very good morning!
> 
> If, in 2018 when the human structures are very close to reach the edge
> of our universe, here on earth you are asking me (a simple end-user),
> in order to run the following "complicated" and "sophisticated" SQL
> statements:
> 
> INSERT INTO my_table(key, value, expiry) VALUES ('my_key', 'my_value', 10);
> SELECT value FROM my_table where key = LOWER('my_key');
> 
> ten (10) times in total, to activate the debugger and check the log
> files because the errors given to me were **totally wrong** ("KEY is
> NULL" was not NULL) and **misleading**, then one more time: It´s
> TRAGIC!
> 
> I, of course, recognise that I have made plenty mistakes! But, I could
> never imagine that the most advanced open source database in the world
> would behave in such a "poor" way!

Postgres did not behave in a 'poor' way, the extension just did not 
interpret the results correctly. This gets to the crux of the problem, 
third party code using Postgres. Postgres has no way of knowing what 
happens after its data is passed on. You are looking for Postgres to 
follow its responses all the way through the software stack and tell you 
if the response is being misused. That is not going to happen. You did 
the correct thing, report the issue to the extension author and post to 
this list for further advice. As a result the issue was corrected. I am 
not seeing what the problem in the process is?

> 
> @Christoph
> 
> As regards "rw_redis_fdw" I have to admit the following:
> - it is honest. It informs the user from the very beginning that "This
> project is currently work in progress and may have experience
> significant changes until it becomes stable. Use it with caution and
> at your own risk!"; although it is so far stable enough!!!
> - it worked in pg9.x without problem!
> - the developer did his best when I reported the problem; for my bad
> luck he did not expirienced the same problem in PG10.x so as he starts
> searching/debugging from the very first moment.
> - after **including and NOT fixing** (because there was not any bug)
> the FuncExpr subquery support, the module has been working fine and it
> is stable! (until of course the next plan change that PG will induce
> under the hood and brakes the interface; that´s a joke!) although the

It is not a joke it is a fact of life and the reason for major version 
changes. They are allowed to make breaking changes in the code. It is 
how software progresses. It is also why there is a long testing period, 
alpha-->beta-->rc, to give users and third party developers plenty of 
opportunity to try their code against the changes.

> developer uses (successfully - I add) very low level, internal
> interfaces to PostgreSQL as you have already written.
> 
> And the most important:
> 
> <h1>The module **DOES** whatever claims to do without a problem, in a
> very humble way!<h1>

Except the problem you ran into:)

> 
> As regards the risk of the project, I am aware of it. That´s why I am
> trying to choose **reliable**, **responsible** and **trustworthy**
> projects, even if they are not famous! From a very famous project, I
> just recently got a burn! Do only **ONE** thing but do it
> **"CORRECTLY"**! I always try to avoid rich-featured projects which,
> by rule, most of the features are mis-implemented, or quality is poor!
> 
> As regards database choice, I do not have many options, PG or MariaDB
> or SQLite. But, when the application is finished, it will be very easy

There is also:

http://www.firebirdsql.org/
https://www.mysql.com/

> to maintain the application-DB interface and use any other DB. It is a
> matter of translation. DB is just a tool like any other one, and not a

Which is exactly where you ran into a problem, so I question the easy 
part. Still, go for it.

> religous matter.
> 
> In the case of redis fdw I do not have many options. There are
> two-three of them. Redis is the only db which offers TTL with very
> high resolution (1 sec), and rw_redis_fdw implements TTL.
> 
> In addition, at first opportunity, I always contact the developer or
> the community to get an idea of his/its mentality and the way they
> work or react or keep their nose up.

Pretty quickly from what I saw of their responses to your issues.

> 
> Have all a nice day!
> 
> Tia
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Good afternoon!
> ...
> Postgres did not behave in a 'poor' way, the extension just did not
> interpret the results correctly.
Eh! Eh! Adrian/Christoph one minute please because this is something
new (at least in the very clear way you formulate it now and I can
understand it easily)!
The statement was sent correctly from the module to PG; PG returned
the correct set of data to the module; BUT module failed to
interpret/present the data to me!

Q1: Is this what you are telling me?
  a) Yes
  b) No

Q2: Module sent the statement to the PG in a way A which PG does
understand. Did the PG sent back to the module in a way A or B or ...
which module understands?
  a) Yes
  b) No

Q3: And which part has "triggered" (so, is responsible for) the wrong
errors to appear on my screen,
  a) PG,
  b) the module,
  c) the java driver,
  d) PL/SQL,
  e) undefined,
  f) NULL or
  g) I do not know / I do not respond (joke!)?

>
> ... Postgres has no way of knowing what
> happens after its data is passed on.
In this case, I do not disagree at all!
>
> You are looking for Postgres to
> follow its responses all the way through the software stack and tell you
> if the response is being misused. That is not going to happen.
For God sake! No, I am not! As soon as the correct data left the
PG-space in the format that the statement requested, and the KEY was
not NULL, of course, I do not blame PG.

Q4: If I used psql, I would get the correct data or not?
>
> You did ... I am
> not seeing what the problem in the process is?
There is not any problem in the process at all. The process is
excellent. Now, I realise that our communication channels/frequencies
maybe are different. We have exchanged so many mails because we are
not able to understand each other.
>
>...
> It is not a joke ...
I do agree! That's why I said it's a joke! Ah, again different frequencies.
>
>...
> Except the problem you ran into:)
+ Eh, "you" added the new plan! Don't be unfair to the developer!
>
> ...
> There is also:
>
> http://www.firebirdsql.org/
> https://www.mysql.com/
There are more! Eh eh! "You" fooled me by writing that it is the most
advanced open source database in the world! I believed in "you" and
"your" words!
Mysql site writes "The world's most popular open source database". I
am not gonna get fooled again so easily!
>
> ...
> Which is exactly where you ran into a problem, so I question the easy
> part. Still, go for it.
I do not get it! Different frequencies...
> ...
> Pretty quickly from what I saw of their responses to your issues.
The enlightenment has not been quickly but easy easy we shall manage
to become together!
>
Have a nice evening!

Tia

On 10/30/18 9:15 AM, GPT wrote:
> Good afternoon!
>> ...
>> Postgres did not behave in a 'poor' way, the extension just did not
>> interpret the results correctly.
> Eh! Eh! Adrian/Christoph one minute please because this is something
> new (at least in the very clear way you formulate it now and I can
> understand it easily)!
> The statement was sent correctly from the module to PG; PG returned
> the correct set of data to the module; BUT module failed to
> interpret/present the data to me!
> 
> Q1: Is this what you are telling me?
>    a) Yes
>    b) No

a) Yes

> 
> Q2: Module sent the statement to the PG in a way A which PG does
> understand. Did the PG sent back to the module in a way A or B or ...
> which module understands?
>    a) Yes
>    b) No

Not sure of exactly where the breakdown in communication happened, that 
would need input from the extension developer. From what I gather the 
extension reparse's the query at some point and failed to take into 
account that the internal representation of the query may change after a 
number of repetitions of the query.



> 
> Q3: And which part has "triggered" (so, is responsible for) the wrong
> errors to appear on my screen,
>    a) PG,
>    b) the module,
>    c) the java driver,
>    d) PL/SQL,
>    e) undefined,
>    f) NULL or
>    g) I do not know / I do not respond (joke!)?

b) the module.

See below for explanation:
https://github.com/nahanni/rw_redis_fdw/commit/05f5f3247569e6c428360cc4270606a91e57c6ff


> 
>>
>> ... Postgres has no way of knowing what
>> happens after its data is passed on.
> In this case, I do not disagree at all!
>>
>> You are looking for Postgres to
>> follow its responses all the way through the software stack and tell you
>> if the response is being misused. That is not going to happen.
> For God sake! No, I am not! As soon as the correct data left the
> PG-space in the format that the statement requested, and the KEY was
> not NULL, of course, I do not blame PG.
> 
> Q4: If I used psql, I would get the correct data or not?

The query would run correctly, you would not be able to move data to 
Redis though.

>>
>> You did ... I am
>> not seeing what the problem in the process is?
> There is not any problem in the process at all. The process is
> excellent. Now, I realise that our communication channels/frequencies
> maybe are different. We have exchanged so many mails because we are
> not able to understand each other.
>>
>> ...
>> It is not a joke ...
> I do agree! That's why I said it's a joke! Ah, again different frequencies.
>>
>> ...
>> Except the problem you ran into:)
> + Eh, "you" added the new plan! Don't be unfair to the developer!

Not trying to be unfair, just pointing out things change and code needs 
to be tested against the changes.

>>
>> ...
>> There is also:
>>
>> http://www.firebirdsql.org/
>> https://www.mysql.com/
> There are more! Eh eh! "You" fooled me by writing that it is the most
> advanced open source database in the world! I believed in "you" and
> "your" words!

That is the projects motto, it has nothing to do with me:)

> Mysql site writes "The world's most popular open source database". I
> am not gonna get fooled again so easily!

I see motto's/slogans as marketing and I consider most marketing as a 
form of lying.

>>
>> ...
>> Which is exactly where you ran into a problem, so I question the easy
>> part. Still, go for it.
> I do not get it! Different frequencies...

I was referring to this:

"But, when the application is finished, it will be very easy
to maintain the application-DB interface and use any other DB. It is a
matter of translation."

The problem you had was a matter of translation. Translation is always 
going to be tricky, especially the more translations you have to do in a 
project.

>> ...
>> Pretty quickly from what I saw of their responses to your issues.
> The enlightenment has not been quickly but easy easy we shall manage
> to become together!
>>
> Have a nice evening!
> 
> Tia
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Very good morning,

Thanks very much for your direct, clear and enlightening response!

As regards Q2, and any other dynamic behaviour/feature or whatever PG
includes or will include in the future and has to do with 3rd entities
(modules, or whatever) of which the behaviour is out of the PG
control, safe precautions would be take easily, in favour of the
passive protection of the end-user, and the good reputation of PG
(consider the last as marketing cookie addressed to the commercial
community ;) ).

For example: in the .control file more fileds would be added to
clarify dynamic manners/behaviours/communications.
For example: subexpr_type = T_Param, T_RelabelType

So, when a module (which makes use of internal parts of PG) is
created, those parameters are recorded in the DB. When the 3rd party
initiates an activity/communication with PG, PG checks this parameters
and behaves/responds to a compatible manner that 3rd party always
understands. A warning about an old-fashion parameter value would be
triggered by PG in every communication instance (or not) to inform the
user/developer that something has changed/improved! When such a
message/warning is seen by them, then they can easily add the new
feature, such as T_FuncExpr, after, of course, the code has been
updated properly, to declare the support.

So, PG continues being developed under the hood, retains backward
compatibility without any real cost and retains the operability of the
3rd entities improving, at the same time, the control on them (and the
eco-system, in general), and end-users are protected, too!

Tia

On 10/31/18 2:03 AM, GPT wrote:
> Very good morning,
> 
> Thanks very much for your direct, clear and enlightening response!
> 
> As regards Q2, and any other dynamic behaviour/feature or whatever PG
> includes or will include in the future and has to do with 3rd entities
> (modules, or whatever) of which the behaviour is out of the PG
> control, safe precautions would be take easily, in favour of the
> passive protection of the end-user, and the good reputation of PG
> (consider the last as marketing cookie addressed to the commercial
> community ;) ).
> 
> For example: in the .control file more fileds would be added to
> clarify dynamic manners/behaviours/communications.
> For example: subexpr_type = T_Param, T_RelabelType
> 
> So, when a module (which makes use of internal parts of PG) is
> created, those parameters are recorded in the DB. When the 3rd party
> initiates an activity/communication with PG, PG checks this parameters
> and behaves/responds to a compatible manner that 3rd party always
> understands. A warning about an old-fashion parameter value would be
> triggered by PG in every communication instance (or not) to inform the
> user/developer that something has changed/improved! When such a
> message/warning is seen by them, then they can easily add the new
> feature, such as T_FuncExpr, after, of course, the code has been
> updated properly, to declare the support.
> 
> So, PG continues being developed under the hood, retains backward
> compatibility without any real cost and retains the operability of the
> 3rd entities improving, at the same time, the control on them (and the
> eco-system, in general), and end-users are protected, too!

The short version:

The above is not going to happen.

The long version:

1) You are asking Postgres to do what previously you said you did not 
want it to do:

https://www.postgresql.org/message-id/CADep2PMJVpVu-ne42yYpqjzGHQ1cunvX92Oo6_hNLfgrj%2BMa_Q%40mail.gmail.com

" You are looking for Postgres to
 > follow its responses all the way through the software stack and tell you
 > if the response is being misused. That is not going to happen.
For God sake! No, I am not! As soon as the correct data left the
PG-space in the format that the statement requested, and the KEY was
not NULL, of course, I do not blame PG."


2) Trying to track the state of every third party code that hits a 
database and it's internal diff from the current internal state of the 
Postgres database code would be intensive and intrusive, for little or 
no benefit in all but a few cases. Those few cases are better dealt with 
by the existing process of issue reporting.

3) Having said 1) and 2) Postgres does do a limited version of what you 
want:

https://www.postgresql.org/docs/11/static/protocol.html

"This document describes version 3.0 of the protocol, implemented in 
PostgreSQL 7.4 and later. For descriptions of the earlier protocol 
versions, see previous releases of the PostgreSQL documentation. A 
single server can support multiple protocol versions. The initial 
startup-request message tells the server which protocol version the 
client is attempting to use. If the major version requested by the 
client is not supported by the server, the connection will be rejected 
(for example, this would occur if the client requested protocol version 
4.0, which does not exist as of this writing). If the minor version 
requested by the client is not supported by the server (e.g. the client 
requests version 3.1, but the server supports only 3.0), the server may 
either reject the connection or may respond with a 
NegotiateProtocolVersion message containing the highest minor protocol 
version which it supports. The client may then choose either to continue 
with the connection using the specified protocol version or to abort the 
connection."

Though it should be noted the above is for the public API, not the 
private parts your extension had a problem with. They are 
private(internal) for a reason. If code needs to touch them then the 
developer becomes responsible for keeping up to date with their changes.

4) 3) also addresses the backwards comparability issue as the current 
protocol extends back to 7.4, which went EOL of life 8 years ago.


> 
> Tia
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Good morning,

>> 1) You are asking Postgres to do what previously you said you did not
>> want it to do:
>>
>>
>> https://www.postgresql.org/message-id/CADep2PMJVpVu-ne42yYpqjzGHQ1cunvX92Oo6_hNLfgrj%2BMa_Q%40mail.gmail.com
>>
>> " You are looking for Postgres to
>>  > follow its responses all the way through the software stack and tell
>> you
>>  > if the response is being misused. That is not going to happen.
>> For God sake! No, I am not! As soon as the correct data left the
>> PG-space in the format that the statement requested, and the KEY was
>> not NULL, of course, I do not blame PG."

No, I am asking PG to send the response in a "language" the client can
understand.

Do not try to fool me!

- A friend of mine who has no PC and internet connection asks me to
send a message to the list.
- I send the message in english to the list. I am the one who
initiated the conversation as a client.
- "You" respond to my message as a server. And you "respond" in
english. Why? Because, you correctly assumed that I understand
english, too. After your response in English, for me it is de facto
that you speak english, and our conversation will continue in english.
- I receive your message in English and send it to my friend to read it.
- ...
- Upon my friend´s sixth message, "you" suddenly change idioma and
respond in german.
- Then, I do not understand and send to my friend "ERROR: message
received in german! By the way, this is a correct message! A wrong
message would be: "Your  message is NULL"! Does it remind you
something? ;)

So, "You" are not responsible what message I have sent to my friend;
and, of course, you do not have to follow what happens after you have
sent your response! "You" do not have to do with my friend.

But, it is obvious that you are responsible for the sudden change of
the language.
So, I come back and complain why "you" have changed language. "You"
tell me that I have to learn german if I want to send more than 5
messages to "you", because within "you" a language change occurs as
you have been recently upgraded!

The most honest and straight answer was the short version: "The above
is not going to happen."

As regards 2), I do not have the knowledge to respond. So, I leave it
to the judgement of the readers, if it is true or not.

Have all of you a nice month and day!

Tia