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

2017-01-25 21:06 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> 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 want more of this king of
>> stuff in the future. (I've certainly wished I could set a GUC in a
>> plpgsql block and have it's settings revert when exiting the block...)
>>
>>
>> I am not sure if I understand. ?? Setting GUC by PRAGMA can work - the
>> syntax supports it and GUC API supports nesting. Not sure about
>> exception handling - but it should not be problem probably.
>>
>> Please, can you show some examples.
>>
>
> From a code standpoint, there's already some ugliness around blocks:
> there's the code that handles blocks themselves (which IIRC is responsible
> for subtransactions), then there's the namespace code, which is very
> separate even though namespaces are very much tied to blocks. Your patch is
> adding another layer into the mix, separate from both blocks and
> namespaces. I think it would be better to combine all 3 together, or at
> least not make matters worse. So IMHO the pragma stuff should be part of
> handling blocks, and not something that's stand alone. IE: make the pragma
> info live in PLpgSQL_stmt_block.
>

I don't think it is fully correct - the pragma can be related to function
too - and namespaces can be related to some other statements - cycles. Any
PLpgSQL_stmt_block does some overhead and probably we want to build a fake
statements to ensure 1:1 relations between namespaces and blocks.

I didn't implement and proposed third level of pragma - statement. For
example the assertions in Ada language are implemented with pragma.
Currently I am not thinking about this form for Postgres.

The cursor options is better stored in expression - the block related GUC
probably should be stored in stmt_block. The pragma is additional
information, and how this information will be used and what impact will be
on generated code depends on pragma - can be different.

> 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 on
> block exit*.

I am think so it is solvable.

Regards

Pavel

>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)
>