rw_redis_fdw: SQL Errors when statement is within a function

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

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

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

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

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/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&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&quot;
target="_blank"><img
src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png&quot;
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&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&quot;
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