Re: PL/pgSQL 2

On Fri, Sep 5, 2014 at 6:18 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> On 09/05/2014 12:37 PM, Merlin Moncure wrote:
>>
>> On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug <fgp@phlo.org> wrote:
>>>>
>>>> Cost of hidden IO cast is negative too. If we can change it, then we can
>>>> increase a sped.
>>>
>>> But the whole power of PL/pgSQL comes from the fact that it allows you to
>>> use the full set of postgres data types and operatores, and that it
>>> seamlessly
>>> integrated with SQL. Without that, PL/pgSQL is about as appealing as
>>> BASIC
>>> as a programming language...
>>
>> Right, and it's exactly those types and operators that are the cause
>> of the performance issues.  A compiled pl/pgsql would only get serious
>> benefit for scenarios involving tons of heavy iteration or funky local
>> data structure manipulation.  Those scenarios are somewhat rare in
>> practice for database applications and often better handled in a
>> another pl should they happen.
>>
>> plv8 is emerging as the best "non-sql" it's JIT compiled by the plv8
>> runtime, the javascript language is designed for embedding. and the
>> json data structure has nice similarities with postgres's arrays and
>> types.  In fact, if I *were* to attempt pl/pgsql compiling, I'd
>> probably translate the code to plv8 and hand it off to the llvm
>> engine.  You'd still have to let postgres handle most of the operator
>> and cast operations but you could pull some things into the plv8
>> engine.  Probably, this would be a net loser since plv8 (unlike
>> plpgsql) has to run everything through SPI.
>
> plpgsql makes extensive use of SPI. Just look at the source code if you
> don't believe me.

oh, certainly.  pl/pgsql also has the ability to bypass SPI for many
simple expressions.  Other pls generally don't do this because they
can't if they want to guarantee SQL semantics....that's ok then
because they don't have to as the language runtime handles operations
local to the function and everything runs under that language's rules.

In a nutshell, my thinking here is to translate pl/pgsql to pl/v8
javascript and then let the optimizing v8 runtime take it from there.
This is IMNSHO a tiny challenge relative to writing an optimization
engine for pl/pgsql by hand.  Think of it as coffeescript for
databases.

It's a nice thought, but there's a lot of roadblocks to making it
happen -- starting with the lack of a javascript library that would
wrap the C postgres datatype routines so you wouldn't have to call in
to SPI for every little thing; as you know even "i := i + 1;" can't be
handled by native javascript operations.

> plv8 also has a nice "find_function" gadget that lets you find and call
> another plv8 function directly instead of having to use an SPI call.

Yeah -- this is another reason why pl/v8 is a nice as a compilation
target.  javascript as we all know is a language with a long list of
pros and cons but it's designed for embedding.

merlin