Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-19 20:49 GMT+02:00 Merlin Moncure : > On Tue, Sep 19, 2017 at 1:37 PM, Robert Haas > wrote: > > On Tue, Sep 19, 2017 at 12:45 PM, Pavel Stehule > wrote: > >>> You can already set a GUC with function scope. I'm not

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Tue, Sep 19, 2017 at 1:37 PM, Robert Haas wrote: > On Tue, Sep 19, 2017 at 12:45 PM, Pavel Stehule > wrote: >>> You can already set a GUC with function scope. I'm not getting your >>> point. >> >> yes, it is true. But implementation of #option

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-19 20:37 GMT+02:00 Robert Haas : > On Tue, Sep 19, 2017 at 12:45 PM, Pavel Stehule > wrote: > >> You can already set a GUC with function scope. I'm not getting your > >> point. > > > > yes, it is true. But implementation of #option is

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Tue, Sep 19, 2017 at 12:45 PM, Pavel Stehule wrote: >> You can already set a GUC with function scope. I'm not getting your >> point. > > yes, it is true. But implementation of #option is limited to PLpgSQL - so > there is not any too much questions - GUC is global -

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-19 18:33 GMT+02:00 Robert Haas : > On Mon, Sep 18, 2017 at 11:46 PM, Pavel Stehule > wrote: > > There is possibility to introduce new compile option #option to disable > plan > > cache on function scope. Do you think so it is acceptable

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Mon, Sep 18, 2017 at 11:46 PM, Pavel Stehule wrote: > There is possibility to introduce new compile option #option to disable plan > cache on function scope. Do you think so it is acceptable solution? It is > step forward. You can already set a GUC with function

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-11 14:44 GMT+02:00 Tom Lane : > Peter Eisentraut writes: > > On 9/8/17 13:14, Simon Riggs wrote: > >> 2. Allow a SET to apply only for a single statement > >> SET guc1 = x, guc2 = y FOR stmt > >> e.g. SET max_parallel_workers = 4 FOR

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Wed, Sep 6, 2017 at 10:43:39AM -0400, Robert Haas wrote: > helps. I don't think we can just indefinitely continue to resist > providing manual control over this behavior on the theory that some > day we'll fix it. It's been six years and we haven't made any > significant progress. In some

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Peter Eisentraut writes: > On 9/8/17 13:14, Simon Riggs wrote: >> 2. Allow a SET to apply only for a single statement >> SET guc1 = x, guc2 = y FOR stmt >> e.g. SET max_parallel_workers = 4 FOR SELECT count(*) FROM bigtable >> Internally a GUC setting already

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 9/8/17 13:14, Simon Riggs wrote: > 2. Allow a SET to apply only for a single statement > SET guc1 = x, guc2 = y FOR stmt > e.g. SET max_parallel_workers = 4 FOR SELECT count(*) FROM bigtable > Internally a GUC setting already exists for a single use, via > GUC_ACTION_SAVE, so we just need to

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-08 23:09 GMT+02:00 Tom Lane : > Pavel Stehule writes: > > personally I prefer syntax without FOR keyword - because following > keyword > > must be reserved keyword > > > SET x = .., y = .. SELECT ... ; > > Nope. Most of the statement-starting

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

> > > > SET x = .., y = .. SELECT ... ; > > This seems pretty ugly from a syntax perspective. > > We already have 'SET LOCAL', which manages scope to the current > transaction. How about SET BLOCK which would set until you've left > the current statement block? > This is reason why PRAGMA was

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Fri, Sep 8, 2017 at 2:09 PM, Tom Lane wrote: > Pavel Stehule writes: > > personally I prefer syntax without FOR keyword - because following > keyword > > must be reserved keyword > > > SET x = .., y = .. SELECT ... ; > > Nope. Most of the

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Merlin Moncure writes: > We already have 'SET LOCAL', which manages scope to the current > transaction. How about SET BLOCK which would set until you've left > the current statement block? (1) I do not think this approach will play terribly well in any of the PLs; their

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Pavel Stehule writes: > personally I prefer syntax without FOR keyword - because following keyword > must be reserved keyword > SET x = .., y = .. SELECT ... ; Nope. Most of the statement-starting keywords are *not* fully reserved; they don't need to be as long as they

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Fri, Sep 8, 2017 at 2:48 PM, Pavel Stehule wrote: > > > 2017-09-08 21:21 GMT+02:00 Daniel Gustafsson : >> >> > On 08 Sep 2017, at 19:14, Simon Riggs wrote: >> > >> > On 6 September 2017 at 07:43, Robert Haas

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-08 21:21 GMT+02:00 Daniel Gustafsson : > > On 08 Sep 2017, at 19:14, Simon Riggs wrote: > > > > On 6 September 2017 at 07:43, Robert Haas wrote: > > > >> LET custom_plan_tries = 0 IN SELECT ... > > > > Tom has pointed me at

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

> On 08 Sep 2017, at 19:14, Simon Riggs wrote: > > On 6 September 2017 at 07:43, Robert Haas wrote: > >> LET custom_plan_tries = 0 IN SELECT ... > > Tom has pointed me at this proposal, since on another thread I asked > for something very similar.

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-08 19:14 GMT+02:00 Simon Riggs : > On 6 September 2017 at 07:43, Robert Haas wrote: > > > LET custom_plan_tries = 0 IN SELECT ... > > Tom has pointed me at this proposal, since on another thread I asked > for something very similar. (No need

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 6 September 2017 at 07:43, Robert Haas wrote: > LET custom_plan_tries = 0 IN SELECT ... Tom has pointed me at this proposal, since on another thread I asked for something very similar. (No need to reprise that discussion, but I wanted prepared queries to be able to do

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 6 September 2017 at 07:43, Robert Haas wrote: > LET custom_plan_tries = 0 IN SELECT ... Tom has pointed me at this proposal, since on another thread I asked for something very similar. (No need to reprise that discussion, but I wanted prepared queries to be able to do

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

> > > > Hmm. I think the core problem here is that we're trying to control > > the plancache, which is a pretty much behind-the-scenes mechanism. > > Except in the case of an explicit PREPARE, you can't even see from > > SQL that the cache is being used, or when it's used. So part of what > >

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

> > > I think a GUC is a decent, though not perfect, mechanism for this. > This problem isn't restricted to PL/pgsql; indeed, the cases I've seen > have come via prepared queries, not PL/pgsql functions. Even without > that, one advantage of a GUC is that they are fairly broadly > understood and

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Wed, Sep 6, 2017 at 11:03 AM, Tom Lane wrote: > That's fair enough. We need to have a discussion about exactly what > the knob does, which is distinct from the question of how you spell > the incantation for twiddling it. I'm dubious that a dumb "force a > custom plan"

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Robert Haas writes: > I don't think we can just indefinitely continue to resist > providing manual control over this behavior on the theory that some > day we'll fix it. That's fair enough. We need to have a discussion about exactly what the knob does, which is distinct

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On Tue, Sep 5, 2017 at 1:38 PM, Tom Lane wrote: > The complaint I have about PRAGMA is that it's yet another syntax for > accomplishing pretty much the same thing. If you don't like the GUC > solution, we've already got the "comp_option" syntax for static options > in

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-05 19:38 GMT+02:00 Tom Lane : > Pavel Stehule writes: > > 2. what syntax we should to use (if we accept this feature)? There was > not > > another proposal if I remember well - The PRAGMA syntax is strong because > > we can very well specify

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Pavel Stehule writes: > 2. what syntax we should to use (if we accept this feature)? There was not > another proposal if I remember well - The PRAGMA syntax is strong because > we can very well specify to range where the plans caching will be > explicitly controlled. It

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-09-05 15:01 GMT+02:00 Daniel Gustafsson : > > On 08 Apr 2017, at 09:42, Pavel Stehule wrote: > > > > 2017-04-08 2:30 GMT+02:00 Peter Eisentraut < > peter.eisentr...@2ndquadrant.com >>: > > On 4/6/17 14:32,

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

> On 08 Apr 2017, at 09:42, Pavel Stehule wrote: > > 2017-04-08 2:30 GMT+02:00 Peter Eisentraut >: > On 4/6/17 14:32, Pavel Stehule wrote: > > I like to see any proposals about syntax or

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-04-08 2:30 GMT+02:00 Peter Eisentraut : > On 4/6/17 14:32, Pavel Stehule wrote: > > I like to see any proposals about syntax or implementation. > > > > Using PRAGMA is one variant - introduced by PLpgSQL origin - Ada > > language. The PRAGMA syntax can be

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 4/6/17 14:32, Pavel Stehule wrote: > I like to see any proposals about syntax or implementation. > > Using PRAGMA is one variant - introduced by PLpgSQL origin - Ada > language. The PRAGMA syntax can be used for PRAGMA autonomous with well > known syntax. It scales well - it supports

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-04-06 12:30 GMT+02:00 Andrew Dunstan : > > > On 04/05/2017 05:41 PM, Andres Freund wrote: > > On 2017-04-05 17:22:34 -0400, Tom Lane wrote: > >> Andres Freund writes: > >>> I'd like some input from other committers whether we want this.

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 04/05/2017 05:41 PM, Andres Freund wrote: > On 2017-04-05 17:22:34 -0400, Tom Lane wrote: >> Andres Freund writes: >>> I'd like some input from other committers whether we want this. I'm >>> somewhat doubtful, but don't have particularly strong feelings. >> I don't

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 05/04/17 23:22, Tom Lane wrote: > Andres Freund writes: >> I'd like some input from other committers whether we want this. I'm >> somewhat doubtful, but don't have particularly strong feelings. > > I don't really want to expose the workings of the plancache at user level.

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-04-06 8:08 GMT+02:00 Pavel Stehule : > > > 2017-04-05 23:22 GMT+02:00 Tom Lane : > >> Andres Freund writes: >> > I'd like some input from other committers whether we want this. I'm >> > somewhat doubtful, but don't have

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

> > That echoes my perception - so let's move this to the next CF? It's not > like this patch has been pending for very long. > sure Regards Pavel > > - Andres >

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-04-05 23:22 GMT+02:00 Tom Lane : > Andres Freund writes: > > I'd like some input from other committers whether we want this. I'm > > somewhat doubtful, but don't have particularly strong feelings. > > I don't really want to expose the workings of the

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-04-05 22:33 GMT+02:00 Andres Freund : > Hi, > > > I'd like some input from other committers whether we want this. I'm > somewhat doubtful, but don't have particularly strong feelings. > > > > + > > + > > + Block level PRAGMA > > + > > + > > +PRAGMA > > +in

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 2017-04-05 17:22:34 -0400, Tom Lane wrote: > Andres Freund writes: > > I'd like some input from other committers whether we want this. I'm > > somewhat doubtful, but don't have particularly strong feelings. > > I don't really want to expose the workings of the plancache

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Andres Freund writes: > I'd like some input from other committers whether we want this. I'm > somewhat doubtful, but don't have particularly strong feelings. I don't really want to expose the workings of the plancache at user level. The heuristics it uses certainly need

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Hi, I'd like some input from other committers whether we want this. I'm somewhat doubtful, but don't have particularly strong feelings. > + > + > + Block level PRAGMA > + > + > +PRAGMA > +in PL/pgSQL > + > + > + > +The block level PRAGMA allows to change the > +

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-03-28 20:29 GMT+02:00 Petr Jelinek : > On 28/03/17 19:43, Pavel Stehule wrote: > > Hi > > > > rebased due last changes in pg_exec.c > > > > Thanks, I went over this and worked over the documentation/comments a > bit (attached updated version of the patch with my

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 28/03/17 19:43, Pavel Stehule wrote: > Hi > > rebased due last changes in pg_exec.c > Thanks, I went over this and worked over the documentation/comments a bit (attached updated version of the patch with my changes). >From my side this can go to committer. -- Petr Jelinek

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Hi rebased due last changes in pg_exec.c Regards Pavel diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index d356deb9f5..56da4d6163 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -802,6 +802,32 @@ $$ LANGUAGE plpgsql; happen in a plain SQL

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-03-19 14:30 GMT+01:00 Petr Jelinek : > On 19/03/17 12:32, Pavel Stehule wrote: > > > > > > 2017-03-18 19:30 GMT+01:00 Petr Jelinek > >: > > > > On 16/03/17 17:15, David Steele wrote: > >

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 19/03/17 12:32, Pavel Stehule wrote: > > > 2017-03-18 19:30 GMT+01:00 Petr Jelinek >: > > On 16/03/17 17:15, David Steele wrote: > > On 2/1/17 3:59 PM, Pavel Stehule wrote: > >> Hi > >> > >> 2017-01-24

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-03-18 19:30 GMT+01:00 Petr Jelinek : > On 16/03/17 17:15, David Steele wrote: > > On 2/1/17 3:59 PM, Pavel Stehule wrote: > >> Hi > >> > >> 2017-01-24 21:33 GMT+01:00 Pavel Stehule >> >: > >> > >>

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 16/03/17 17:15, David Steele wrote: > On 2/1/17 3:59 PM, Pavel Stehule wrote: >> Hi >> >> 2017-01-24 21:33 GMT+01:00 Pavel Stehule > >: >> >> Perhaps that's as simple as renaming all the existing _ns_* >>

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 2/1/17 3:59 PM, Pavel Stehule wrote: > Hi > > 2017-01-24 21:33 GMT+01:00 Pavel Stehule >: > > Perhaps that's as simple as renaming all the existing _ns_* > functions to _block_ and then adding support for

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 1/27/17 4:14 AM, Greg Stark wrote: On 25 January 2017 at 20:06, Jim Nasby wrote: GUCs support SET LOCAL, but that's not the same as local scoping because the setting stays in effect unless the substrans aborts. What I'd like is the ability to set a GUC in a plpgsql

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Hi 2017-01-24 21:33 GMT+01:00 Pavel Stehule : > > > >> >>> Perhaps that's as simple as renaming all the existing _ns_* functions to >>> _block_ and then adding support for pragmas... >>> >>> Since you're adding cursor_options to PLpgSQL_expr it should probably be >>>

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 25 January 2017 at 20:06, Jim Nasby wrote: > GUCs support SET LOCAL, but that's not the same as local scoping because the > setting stays in effect unless the substrans aborts. What I'd like is the > ability to set a GUC in a plpgsql block *and have the setting revert

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-01-25 21:06 GMT+01:00 Jim Nasby : > On 1/23/17 11:38 PM, Pavel Stehule wrote: > >> >> Instead of paralleling all the existing namespace stuff, I wonder if >> it'd be better to create explicit block infrastructure. AFAIK >> PRAGMAs are going to have a lot

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 1/23/17 11:38 PM, Pavel Stehule wrote: Instead of paralleling all the existing namespace stuff, I wonder if it'd be better to create explicit block infrastructure. AFAIK PRAGMAs are going to have a lot of the same requirements (certainly the nesting is the same), and we might

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-01-24 6:38 GMT+01:00 Pavel Stehule : > Hi > > 2017-01-23 21:59 GMT+01:00 Jim Nasby : > >> On 1/23/17 2:10 PM, Pavel Stehule wrote: >> >>> Comments, notes? >>> >> >> +1 on the idea. It'd also be nice if we could expose control of plans for >>

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

2017-01-23 21:59 GMT+01:00 Jim Nasby : > On 1/23/17 2:10 PM, Pavel Stehule wrote: > >> Comments, notes? >> > > +1 on the idea. It'd also be nice if we could expose control of plans for > dynamic SQL, though I suspect that's not terribly useful without some kind > of

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

Hi 2017-01-23 21:59 GMT+01:00 Jim Nasby : > On 1/23/17 2:10 PM, Pavel Stehule wrote: > >> Comments, notes? >> > > +1 on the idea. It'd also be nice if we could expose control of plans for > dynamic SQL, though I suspect that's not terribly useful without some kind > of

Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan

On 1/23/17 2:10 PM, Pavel Stehule wrote: Comments, notes? +1 on the idea. It'd also be nice if we could expose control of plans for dynamic SQL, though I suspect that's not terribly useful without some kind of global session storage. A couple notes on a quick read-through: Instead of

[HACKERS] PoC plpgsql - possibility to force custom or generic plan

Hi, this patch is based on discussions related to plpgsql2 project. Currently we cannot to control plan cache from plpgsql directly. We can use dynamic SQL if we can enforce oneshot plan - but it means little bit less readable code (if we enforce dynamic SQL from performance reasons). It means