Re: PL/pgSQL 2

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.

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.

It has two serious defects in my view, that it inherits from v8.

First, and foremost, it has the old really really horrible Javascript 
scoping rules for variables. This makes it totally unsuitable for 
anything except trivially short functions. There is good news and bad 
news on this front: modern versions of v8 have code to allow proper 
lexical scoping as provided for in the draft ECMASCRIPT6 standard (the 
feature is named "harmony scoping"). Example of command line use:
   andrew@vpncli plv8js]$ d8 --use-strict --harmony   V8 version 3.14.5.10 [console: readline]   d8> var i = 10; for
(leti = 0; i < 3; i++) { let j = i; for (let i   = 4; i < 6; i++) { print ("j " + j + " i " + i); } }   j 0 i 4   j 0 i
5  j 1 i 4   j 1 i 5   j 2 i 4   j 2 i 5   d8> print(i);   10   d8> 
 

The bad news is that neither Hitosho nor I (yet) know how to allow 
setting these flags for the plv8 embedded engine.

The other defect is that its string handling is just awful. It has 
neither multiline strings, not interpolation into strings.

The good news is that the new draft standard addresses these issues too, 
with something called template strings. The bad news is that V8 doesn't 
yet have code to support the feature, AFAICT. The Mozilla people are a 
bit ahead here, and this feature is due in a release of their rhino 
javascript library that will be in Mozilla 34, due out in November, 
AIUI. Let's hope that the V8 guys get their act together on this.

cheers

andrew