Re: rw_redis_fdw: SQL Errors when statement is within a function
On 10/26/18, Christoph Moench-Tegeder wrote: > ## GPT (gptmailingli...@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, aask 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. > >
Re: rw_redis_fdw: SQL Errors when statement is within a function
And one more question: Why this incident has been observed when the statement is only within a function with variable as input parameter and not when they run directly with explicitly defined parameter/ In the first case, plan remains stable and does not change; but in the second case plan changes. Anyway, this is too technical for me and even if you respond most probably I am not gonna get it. Tia On 10/27/18, GPT wrote: > On 10/26/18, Christoph Moench-Tegeder wrote: >> ## GPT (gptmailingli...@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, aask 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. >> >> >
Re: How to get partition info for a partition table?
see
https://www.postgresql.org/docs/9.5/static/app-psql.html
for psql options an commands:
psql -d ... -U ...
\d+ measurement_year_month
...
Partition key: RANGE (date_part('year'::text, logdate),
date_part('month'::text, logdate))
Number of partitions: 0
if you want to know how postgresql is retrieving those informations
try -E option for ECHO_HIDDEN
psql -d ... -U ... -E
\d+ measurement_year_month
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: rw_redis_fdw: SQL Errors when statement is within a function
On 10/27/18 2:28 AM, GPT wrote: On 10/26/18, Christoph Moench-Tegeder wrote: ## GPT (gptmailingli...@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, aask 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.kla...@aklaver.com
Re: rw_redis_fdw: SQL Errors when statement is within a function
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.kla...@aklaver.com
Re: rw_redis_fdw: SQL Errors when statement is within a function
On 10/27/18, Adrian Klaver 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.kla...@aklaver.com >
Ltree: set of allowed charcters is limited to [A-Za-z0-9_]. Could the dash "-" be included?
Dear friends, I would like to use ltree for search paths in a warehouse application, something like "Material-Entry-01.Main-Aisle.Shelf-Aisle-R07/R08.R07-12-03" Unfortunately I can not use common separators like dash (-) or slash(/) Documentation states only thes characters [A-Za-z0-9_] are allowed. https://www.postgresql.org/docs/10/static/ltree.html As far as I can see this limitation is set in source code of ltree, line 83: https://doxygen.postgresql.org/ltree_8h_source.html #define ISALNUM(x) ( t_isalpha(x) || t_isdigit(x) || ( pg_mblen(x) == 1 && t_iseq((x), '_') ) ) I would appreciate if at least the dash (-) and perhaps also the slash(/) would be included to the list of allowed characters. It is not only me, also other users are interested in this feature, some of them even changed the source code themselves to recompile their own Postgresql version.. https://stackoverflow.com/questions/29887093/valid-characters-in-postgres-ltree-label-in-utf8-charset/34057357 best regards Jörn Jaenecke
Re: Ltree: set of allowed charcters is limited to [A-Za-z0-9_]. Could the dash "-" be included?
On Saturday, October 27, 2018, joernbs wrote: > Dear friends, > > I would like to use ltree for search paths in a warehouse application, > something like "Material-Entry-01.Main-Aisle. > Shelf-Aisle-R07/R08.R07-12-03" > Unfortunately I can not use common separators like dash (-) or slash(/) > > Documentation states only thes characters [A-Za-z0-9_] are allowed. > https://www.postgresql.org/docs/10/static/ltree.html > I don’t see how this would be possible to do with the existing type - too much potential breakage of existing data. Your example itself shows why using dash as a separator is a bad idea. David J.
Re: rw_redis_fdw: SQL Errors when statement is within a function
On 10/27/18 8:00 AM, GPT wrote: On 10/27/18, Adrian Klaver 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.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Ltree: set of allowed charcters is limited to [A-Za-z0-9_]. Could the dash "-" be included?
> On 27 Oct 2018, at 18:14, David G. Johnston > wrote: > >> On Saturday, October 27, 2018, joernbs wrote: >> Dear friends, >> >> I would like to use ltree for search paths in a warehouse application, >> something like "Material-Entry-01.Main-Aisle.Shelf-Aisle-R07/R08.R07-12-03" >> Unfortunately I can not use common separators like dash (-) or slash(/) >> >> Documentation states only thes characters [A-Za-z0-9_] are >> allowed.https://www.postgresql.org/docs/10/static/ltree.html >> > I don’t see how this would be possible to do with the existing type - too > much potential breakage of existing data. Your example itself shows why > using dash as a separator is a bad idea. David, I don't think he's talking about separators ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: rw_redis_fdw: SQL Errors when statement is within a function
## GPT (gptmailingli...@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.
Re: rw_redis_fdw: SQL Errors when statement is within a function
## GPT (gptmailingli...@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.
