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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>
Cc: David Steele <david(at)pgmasters(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
Subject: Re: PoC plpgsql - possibility to force custom or generic plan
Date: 2017-03-20 10:30:56
Message-ID: CAFj8pRAcJfF8++NkhXzuXp9rnkBAzRvHD5F6HRwn8EizY6R1ew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

2017-03-19 14:30 GMT+01:00 Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>:

> On 19/03/17 12:32, Pavel Stehule wrote:
> >
> >
> > 2017-03-18 19:30 GMT+01:00 Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com
> > <mailto:petr(dot)jelinek(at)2ndquadrant(dot)com>>:
> >
> > 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 <pavel(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>
> > >> <mailto:pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)com
> >>>:
> > >>
> > >> 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 removed as an option to exec_*.
> > >>
> > >> I have to recheck it. Some cursor options going from
> dynamic
> > >> cursor variables and are related to dynamic query - not
> query
> > >> that creates query string.
> > >>
> > >> hmm .. so current state is better due using options like
> > >> CURSOR_OPT_PARALLEL_OK
> > >>
> > >> if (expr->plan == NULL)
> > >> exec_prepare_plan(estate, expr, (parallelOK ?
> > >> CURSOR_OPT_PARALLEL_OK : 0) |
> > >> expr->cursor_options);
> > >>
> > >> This options is not permanent feature of expression - and
> then I
> > >> cannot to remove cursor_option argument from exec_*
> > >>
> > >> I did minor cleaning - remove cursor_options from plpgsql_var
> > >>
> > >> + basic doc
> > >
> > > This patch still applies cleanly and compiles at cccbdde.
> > >
> > > Any reviewers want to have a look?
> > >
> >
> > I'll bite.
> >
> > I agree with Jim that it's not very nice to add yet another
> > block/ns-like layer. I don't see why pragma could not be added to
> either
> > PLpgSQL_stmt_block (yes pragma can be for whole function but function
> > body is represented by PLpgSQL_stmt_block as well so no issue
> there), or
> > to namespace code. In namespace since they are used for other thing
> > there would be bit of unnecessary propagation but it's 8bytes per
> > namespace, does not seem all that much.
> >
> > My preference would be to add it to PLpgSQL_stmt_block (unless we
> plan
> > to add posibility to add pragmas for other loops and other things)
> but I
> > am not sure if current block is easily (and in a fast way) accessible
> > from all places where it's needed. Maybe the needed info could be
> pushed
> > to estate from PLpgSQL_stmt_block during the execution.
> >
> >
> > There is maybe partial misunderstand of pragma - it is set of nested
> > configurations used in compile time only. It can be used in execution
> > time too - it change nothing.
> >
> > The pragma doesn't build a persistent tree. It is stack of
> > configurations that allows fast access to current configuration, and
> > fast leaving of configuration when the change is out of scope.
> >
> > I don't see any any advantage to integrate pragma to ns or to
> > stmt_block. But maybe I don't understand to your idea.
> >
> > I see a another possibility in code - nesting init_block_directives() to
> > plpgsql_ns_push and free_block_directives() to plpgsql_ns_pop()
> >
>
> That's more or less what I mean by "integrating" to ns :)
>
> The main idea is to not add 3rd layer of block push/pop that's sprinkled
> in "random" places.
>

ok fixed

I reworked a maintaining settings - now it use lazy copy - the copy of
settings is created only when pragma is used in namespace. It remove any
impact on current code without pragmas.

Regards

Pavel

>
> --
> Petr Jelinek http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

Attachment Content-Type Size
psql-pragma-plan_cache-01.patch text/x-patch 19.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ronan Dunklau 2017-03-20 10:31:10 [Proposal] Make the optimiser aware of partitions ordering
Previous Message Heikki Linnakangas 2017-03-20 10:30:10 Re: [COMMITTERS] pgsql: Add TAP tests for password-based authentication methods.