Re: [HACKERS] Cached plans and statement generalization

On 11.09.2017 12:24, Konstantin Knizhnik wrote: Attached please find rebased version of the patch. There are the updated performance results (pgbench -s 100 -c 1): protocol (-M) read-write read-only (-S) simple 3327 19325 extended 2256 16908

Re: [HACKERS] Cached plans and statement generalization

On 09.09.2017 06:35, Thomas Munro wrote: On Fri, May 26, 2017 at 3:54 AM, Konstantin Knizhnik wrote: Attached please find rebased version of the autoprepare patch based on Tom's proposal (perform analyze for tree with constant literals and then replace them with

Re: [HACKERS] Cached plans and statement generalization

On Fri, May 26, 2017 at 3:54 AM, Konstantin Knizhnik wrote: > Attached please find rebased version of the autoprepare patch based on Tom's > proposal (perform analyze for tree with constant literals and then replace > them with parameters). > Also I submitted this patch

Re: [HACKERS] Cached plans and statement generalization

On 10.05.2017 19:11, Konstantin Knizhnik wrote: Based on the Robert's feedback and Tom's proposal I have implemented two new versions of autoprepare patch. First version is just refactoring of my original implementation: I have extracted common code into prepare_cached_plan and

Re: [HACKERS] Cached plans and statement generalization

On 2017-05-18 11:57:57 +0300, Konstantin Knizhnik wrote: > From my own experience I found out that PG_TRY/PG_CATCH mechanism is not > providing proper cleanup (unlike C++ exceptions). Right, simply because there's no portable way to transparently do so. Would be possible on elf glibc platforms,

Re: [HACKERS] Cached plans and statement generalization

On 15.05.2017 18:31, Robert Haas wrote: On Wed, May 10, 2017 at 12:11 PM, Konstantin Knizhnik wrote: Robert, can you please explain why using TRY/CATCH is not safe here: This is definitely not a safe way of using TRY/CATCH. This has been discussed many, many

Re: [HACKERS] Cached plans and statement generalization

On Wed, May 10, 2017 at 12:11 PM, Konstantin Knizhnik wrote: > Robert, can you please explain why using TRY/CATCH is not safe here: >> >> This is definitely not a safe way of using TRY/CATCH. This has been discussed many, many times on this mailing list before, and I

Re: [HACKERS] Cached plans and statement generalization

On Fri, May 12, 2017 at 08:35:26PM +0300, Konstantin Knizhnik wrote: > > > On 12.05.2017 18:23, Bruce Momjian wrote: > >On Fri, May 12, 2017 at 10:50:41AM +0300, Konstantin Knizhnik wrote: > >>Definitely changing session context (search_path, date/time format, ...) may > >>cause incorrect

Re: [HACKERS] Cached plans and statement generalization

On May 12, 2017 12:50:41 AM PDT, Konstantin Knizhnik wrote: >Definitely changing session context (search_path, date/time format, >...) >may cause incorrect behavior of cached statements. >Actually you may get the same problem with explicitly prepared >statements

Re: [HACKERS] Cached plans and statement generalization

On 12.05.2017 18:23, Bruce Momjian wrote: On Fri, May 12, 2017 at 10:50:41AM +0300, Konstantin Knizhnik wrote: Definitely changing session context (search_path, date/time format, ...) may cause incorrect behavior of cached statements. I wonder if we should clear the cache whenever any SET

Re: [HACKERS] Cached plans and statement generalization

On Fri, May 12, 2017 at 10:50:41AM +0300, Konstantin Knizhnik wrote: > Definitely changing session context (search_path, date/time format, ...) may > cause incorrect behavior of cached statements. I wonder if we should clear the cache whenever any SET command is issued. > Actually you may get

Re: [HACKERS] Cached plans and statement generalization

On 12.05.2017 03:58, Bruce Momjian wrote: On Thu, May 11, 2017 at 10:41:45PM +0300, Konstantin Knizhnik wrote: This is why I have provided second implementation which replace literals with parameters after raw parsing. Certainly it is slower than first approach. But still provide significant

Re: [HACKERS] Cached plans and statement generalization

On Thu, May 11, 2017 at 10:41:45PM +0300, Konstantin Knizhnik wrote: > This is why I have provided second implementation which replace > literals with parameters after raw parsing. Certainly it is slower > than first approach. But still provide significant advantage in > performance: more than

Re: [HACKERS] Cached plans and statement generalization

On 05/11/2017 10:52 PM, Andres Freund wrote: On 2017-05-11 22:48:26 +0300, Konstantin Knizhnik wrote: On 05/11/2017 09:31 PM, Tom Lane wrote: Bruce Momjian writes: Good point. I think we need to do some measurements to see if the parser-only stage is actually significant.

Re: [HACKERS] Cached plans and statement generalization

On 2017-05-11 22:48:26 +0300, Konstantin Knizhnik wrote: > On 05/11/2017 09:31 PM, Tom Lane wrote: > > Bruce Momjian writes: > > > Good point. I think we need to do some measurements to see if the > > > parser-only stage is actually significant. I have a hunch that > > >

Re: [HACKERS] Cached plans and statement generalization

On 05/11/2017 09:31 PM, Tom Lane wrote: Bruce Momjian writes: Good point. I think we need to do some measurements to see if the parser-only stage is actually significant. I have a hunch that commercial databases have much heavier parsers than we do. FWIW, gram.y does show

Re: [HACKERS] Cached plans and statement generalization

On 05/11/2017 06:12 PM, Bruce Momjian wrote: On Wed, May 10, 2017 at 07:11:07PM +0300, Konstantin Knizhnik wrote: I am going to continue work on this patch I will be glad to receive any feedback and suggestions for its improvement. In most cases, applications are not accessing Postgres

Re: [HACKERS] Cached plans and statement generalization

On May 11, 2017 11:31:02 AM PDT, Tom Lane wrote: >Bruce Momjian writes: >> Good point. I think we need to do some measurements to see if the >> parser-only stage is actually significant. I have a hunch that >> commercial databases have much heavier

Re: [HACKERS] Cached plans and statement generalization

Bruce Momjian writes: > Good point. I think we need to do some measurements to see if the > parser-only stage is actually significant. I have a hunch that > commercial databases have much heavier parsers than we do. FWIW, gram.y does show up as significant in many of the

Re: [HACKERS] Cached plans and statement generalization

On Thu, May 11, 2017 at 05:39:58PM +, Douglas Doole wrote: > One interesting idea from Doug Doole was to do it between the tokenizer > and > parser.  I think they are glued together so you would need a way to run > the > tokenizer separately and compare that to the tokens you

Re: [HACKERS] Cached plans and statement generalization

> > One interesting idea from Doug Doole was to do it between the tokenizer > and parser. I think they are glued together so you would need a way to run > the tokenizer separately and compare that to the tokens you stored for the > cached plan. > When I did this, we had the same problem that the

Re: [HACKERS] Cached plans and statement generalization

On Wed, May 10, 2017 at 07:11:07PM +0300, Konstantin Knizhnik wrote: > I am going to continue work on this patch I will be glad to receive any > feedback and suggestions for its improvement. > In most cases, applications are not accessing Postgres directly, but using > some connection pooling

Re: [HACKERS] Cached plans and statement generalization

On 02.05.2017 21:26, Robert Haas wrote: I am sympathetic to the fact that this is a hard problem to solve. I'm just telling you that the way you've got it is not acceptable and nobody's going to commit it like that (or if they do, they will end up having to revert it). If you want to have a

Re: [HACKERS] Cached plans and statement generalization

On Tue, May 2, 2017 at 5:50 AM, Konstantin Knizhnik wrote: >> I don't see something with a bunch of hard-coded rules for particular type >> OIDs having any chance of being acceptable. > > Well, what I need is ... Regarding this... > Definitely copying of code is bad

Re: [HACKERS] Cached plans and statement generalization

On 01.05.2017 18:52, Robert Haas wrote: On Fri, Apr 28, 2017 at 6:01 AM, Konstantin Knizhnik > wrote: Any comments and suggestions for future improvement of this patch are welcome. +PG_TRY(); +{ +

Re: [HACKERS] Cached plans and statement generalization

On Fri, Apr 28, 2017 at 6:01 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > Any comments and suggestions for future improvement of this patch are > welcome. > +PG_TRY(); +{ +query = parse_analyze_varparams(parse_tree, +

Re: [HACKERS] Cached plans and statement generalization

On 26.04.2017 13:46, Pavel Stehule wrote: I attach new patch which allows to limit the number of autoprepared statements (autoprepare_limit GUC variable). Also I did more measurements, now with several concurrent connections and read-only statements. Results of pgbench

Re: [HACKERS] Cached plans and statement generalization

On 04/26/2017 08:08 PM, Doug Doole wrote: A naive option would be to invalidate anything that depends on table or view *.FOOBAR. You could probably make it a bit smarter by also requiring that schema A appear in the path. This has been rumbling around in my head. I wonder if you could

Re: [HACKERS] Cached plans and statement generalization

> > A naive option would be to invalidate anything that depends on table or > view *.FOOBAR. You could probably make it a bit smarter by also requiring > that schema A appear in the path. > This has been rumbling around in my head. I wonder if you could solve this problem by registering

Re: [HACKERS] Cached plans and statement generalization

2017-04-26 12:30 GMT+02:00 Konstantin Knizhnik : > > > On 26.04.2017 10:49, Konstantin Knizhnik wrote: > > > > On 26.04.2017 04:00, Tsunakawa, Takayuki wrote: Are you considering some > upper limit on the number of prepared statements? > In this case we need some kind

Re: [HACKERS] Cached plans and statement generalization

On 26.04.2017 10:49, Konstantin Knizhnik wrote: On 26.04.2017 04:00, Tsunakawa, Takayuki wrote: Are you considering some upper limit on the number of prepared statements? In this case we need some kind of LRU for maintaining cache of autoprepared statements. I think that it is good idea

Re: [HACKERS] Cached plans and statement generalization

On 26.04.2017 04:00, Tsunakawa, Takayuki wrote: From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Konstantin Knizhnik Well, first of all I want to share results I already get: pgbench with default parameters, scale 10 and one connection: So

Re: [HACKERS] Cached plans and statement generalization

On 26.04.2017 01:34, Andres Freund wrote: Hi, (FWIW, on this list we don't do top-quotes) On 2017-04-25 22:21:22 +, Doug Doole wrote: Plan invalidation was no different than for any SQL statement. DB2 keeps a list of the objects the statement depends on. If any of the objects changes in

Re: [HACKERS] Cached plans and statement generalization

On 26.04.2017 00:47, Andres Freund wrote: On 2017-04-25 21:11:08 +, Doug Doole wrote: When I did this in DB2, I didn't use the parser - it was too expensive. I just tokenized the statement and used some simple rules to bypass the invalid cases. For example, if I saw the tokens "ORDER" and

Re: [HACKERS] Cached plans and statement generalization

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Konstantin > Knizhnik > Well, first of all I want to share results I already get: pgbench with default > parameters, scale 10 and one connection: > > So autoprepare is as efficient as explicit

Re: [HACKERS] Cached plans and statement generalization

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx=9.4.52=10.11.6=email_footer_2] On Tue, Apr 25, 2017 at 3:48 PM, Doug Doole wrote: It's not always that simple, at least in postgres, unless you disregard search_path. Consider e.g. cases like CREATE SCHEMA a;

Re: [HACKERS] Cached plans and statement generalization

On 4/25/17, 6:34 PM, "pgsql-hackers-ow...@postgresql.org on behalf of Andres Freund" wrote: It's not always that simple, at least in postgres, unless you disregard search_path. Consider e.g. cases like CREATE

Re: [HACKERS] Cached plans and statement generalization

> > (FWIW, on this list we don't do top-quotes) > I know. Forgot and just did "reply all". My bad. It's not always that simple, at least in postgres, unless you disregard > search_path. Consider e.g. cases like > > CREATE SCHEMA a; > CREATE SCHEMA b; > CREATE TABLE a.foobar(somecol int); > SET

Re: [HACKERS] Cached plans and statement generalization

On Tue, Apr 25, 2017 at 3:24 PM, David Fetter wrote: > I don't have an exploit yet. What concerns me is attackers' access to > what is in essence the ability to poke at RULEs when they only have > privileges to read. > ​If they want to see how it works they can read the

Re: [HACKERS] Cached plans and statement generalization

Hi, (FWIW, on this list we don't do top-quotes) On 2017-04-25 22:21:22 +, Doug Doole wrote: > Plan invalidation was no different than for any SQL statement. DB2 keeps a > list of the objects the statement depends on. If any of the objects changes > in an incompatible way the plan is

Re: [HACKERS] Cached plans and statement generalization

On Tue, Apr 25, 2017 at 11:35:21PM +0300, Konstantin Knizhnik wrote: > On 04/25/2017 07:54 PM, David Fetter wrote: > > On Tue, Apr 25, 2017 at 06:11:09PM +0300, Konstantin Knizhnik wrote: > > > On 24.04.2017 21:43, Andres Freund wrote: > > > > Hi, > > > > > > > > On 2017-04-24 11:46:02 +0300,

Re: [HACKERS] Cached plans and statement generalization

Plan invalidation was no different than for any SQL statement. DB2 keeps a list of the objects the statement depends on. If any of the objects changes in an incompatible way the plan is invalidated and kicked out of the cache. I suspect what is more interesting is plan lookup. DB2 has something

Re: [HACKERS] Cached plans and statement generalization

On 2017-04-25 21:11:08 +, Doug Doole wrote: > When I did this in DB2, I didn't use the parser - it was too expensive. I > just tokenized the statement and used some simple rules to bypass the > invalid cases. For example, if I saw the tokens "ORDER" and "BY" then I'd > disallow replacement

Re: [HACKERS] Cached plans and statement generalization

When I did this in DB2, I didn't use the parser - it was too expensive. I just tokenized the statement and used some simple rules to bypass the invalid cases. For example, if I saw the tokens "ORDER" and "BY" then I'd disallow replacement replacement until I hit the end of the current subquery or

Re: [HACKERS] Cached plans and statement generalization

On 04/25/2017 11:40 PM, Serge Rielau wrote: On Apr 25, 2017, at 1:37 PM, Konstantin Knizhnik > wrote: SELECT ‘hello’::CHAR(10) || ‘World’, 5 + 6; You can substitute ‘hello’, ‘World’, 5, and 6. But not 10. I am substituting

Re: [HACKERS] Cached plans and statement generalization

> On Apr 25, 2017, at 1:37 PM, Konstantin Knizhnik > wrote: >> >> SELECT ‘hello’::CHAR(10) || ‘World’, 5 + 6; >> >> You can substitute ‘hello’, ‘World’, 5, and 6. But not 10. > > I am substituting only string literals. So the query above will be > transformed to

Re: [HACKERS] Cached plans and statement generalization

On 04/25/2017 08:09 PM, Serge Rielau wrote: On Tue, Apr 25, 2017 at 9:45 AM, Konstantin Knizhnik wrote: On 25.04.2017 19:12, Serge Rielau wrote: On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik

Re: [HACKERS] Cached plans and statement generalization

On 04/25/2017 07:54 PM, David Fetter wrote: On Tue, Apr 25, 2017 at 06:11:09PM +0300, Konstantin Knizhnik wrote: On 24.04.2017 21:43, Andres Freund wrote: Hi, On 2017-04-24 11:46:02 +0300, Konstantin Knizhnik wrote: So what I am thinking now is implicit query caching. If the same query with

Re: [HACKERS] Cached plans and statement generalization

On Tue, Apr 25, 2017 at 9:45 AM, Konstantin Knizhnik wrote: On 25.04.2017 19:12, Serge Rielau wrote: On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru [k.knizh...@postgrespro.ru] > wrote: Another problem is caused by using integer literals

Re: [HACKERS] Cached plans and statement generalization

On Tue, Apr 25, 2017 at 06:11:09PM +0300, Konstantin Knizhnik wrote: > On 24.04.2017 21:43, Andres Freund wrote: > > Hi, > > > > On 2017-04-24 11:46:02 +0300, Konstantin Knizhnik wrote: > > > So what I am thinking now is implicit query caching. If the same query > > > with > > > different

Re: [HACKERS] Cached plans and statement generalization

On 25.04.2017 19:12, Serge Rielau wrote: On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik > wrote: Another problem is caused by using integer literals in context where parameters can not be used, for example "order by 1”. You will

Re: [HACKERS] Cached plans and statement generalization

> On Apr 25, 2017, at 8:11 AM, Konstantin Knizhnik > wrote: > Another problem is caused by using integer literals in context where > parameters can not be used, for example "order by 1”. You will also need to deal with modifiers in types such as VARCHAR(10). Not

Re: [HACKERS] Cached plans and statement generalization

On 24.04.2017 21:43, Andres Freund wrote: Hi, On 2017-04-24 11:46:02 +0300, Konstantin Knizhnik wrote: So what I am thinking now is implicit query caching. If the same query with different literal values is repeated many times, then we can try to generalize this query and replace it with

Re: [HACKERS] Cached plans and statement generalization

Hi, On 2017-04-24 11:46:02 +0300, Konstantin Knizhnik wrote: > So what I am thinking now is implicit query caching. If the same query with > different literal values is repeated many times, then we can try to > generalize this query and replace it with prepared query with > parameters. That's

Re: [HACKERS] Cached plans and statement generalization

On 24.04.2017 13:24, Alexander Korotkov wrote: Hi, Konstantin! On Mon, Apr 24, 2017 at 11:46 AM, Konstantin Knizhnik > wrote: There were a lot of discussions about query plan caching in hackers mailing list, but I failed

Re: [HACKERS] Cached plans and statement generalization

Hi, Konstantin! On Mon, Apr 24, 2017 at 11:46 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > There were a lot of discussions about query plan caching in hackers > mailing list, but I failed to find some clear answer for my question and > the current consensus on this question in

[HACKERS] Cached plans and statement generalization

Hi hackers, There were a lot of discussions about query plan caching in hackers mailing list, but I failed to find some clear answer for my question and the current consensus on this question in Postgres community. As far as I understand current state is the following: 1. We have