PoC plpgsql - possibility to force custom or generic plan

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: PoC plpgsql - possibility to force custom or generic plan
Date: 2017-01-23 20:10:49
Message-ID: CAFj8pRAGLaiEm8ur5DWEBo7qHRWTk9HxkuUAz00CZZtJj-LkCA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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 so the code cannot be checked by plpgsql check too.

The plan cache subsystem allows some control by options
CURSOR_OPT_GENERIC_PLAN and CURSOR_OPT_CUSTOM_PLAN. So we just a interface
how to use these options from PLpgSQL. I used Ada language feature (used in
PL/SQL too) - PRAGMA statement. It allows to set compiler directives. The
syntax of PRAGMA statements allows to set a level where entered compiler
directive should be applied. It can works on function level or block level.

Attached patch introduces PRAGMA plan_cache with options: DEFAULT,
FORCE_CUSTOM_PLAN, FORCE_GENERIC_PLAN. Plan cache is partially used every
time - the parser/analyzer result is cached every time.

Examples:

CREATE OR REPLACE FUNCTION foo(a int)
RETURNS int AS $$
DECLARE ..
BEGIN

DECLARE
/* block level (local scope) pragma */
PRAGMA plan_cache(FORCE_CUSTOM_PLAN);
BEGIN
SELECT /* slow query - dynamic sql is not necessary */
END;

END;

Benefits:

1. remove one case where dynamic sql is necessary now - security, static
check
2. introduce PRAGMAs - possible usage: autonomous transactions, implicit
namespaces settings (namespace for auto variables, namespace for function
arguments).

Comments, notes?

Regards

Pavel

Attachment Content-Type Size
plpgsql-pragma-plan_cache.patch text/x-patch 13.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2017-01-23 20:37:14 Re: \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)
Previous Message Jesper Pedersen 2017-01-23 19:53:35 Re: Microvacuum support for Hash Index