Обсуждение: 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 evening,
Thanks for the response.
On Wed, Oct 31, 2018, 2:59 PM Adrian Klaver <adrian.klaver@aklaver.com wrote:
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