Re: [HACKERS] merging some features from plpgsql2 project

On 12/28/16 7:16 AM, Pavel Stehule wrote:
>
>
> 2016-12-28 5:09 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>>:
>
>     On 12/27/16 4:56 PM, Merlin Moncure wrote:
>
>         On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule
>         <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:
>     Which is why this is an external fork of plpgsql.
>
>
> ok. Just I would not to repeat Perl6 or Python3 story - it is big
> adventure, but big fail too

Yeah, creating an entirely "new" PL to deal with compatibility doesn't 
seem like a good idea to me.

>     ** The real problem is that we have no mechanism for allowing a PL's
>     language/syntax/API to move forward without massive backwards
>     compatibility problems. **
>
>
> We have not, but there are few possibilities:
>
> 1. enhance #option command
> 2. we can introduce PRAGMA command
> https://en.wikipedia.org/wiki/Ada_(programming_language)#Pragmas

See separate reply.

<discussion about "blessed extensions">

>     I'm honestly surprised (even shocked) that you've never run into any
>     of the problems plpgsql2 is trying to solve. I've hit all those
>     problems except for OUT parameters. I'd say the order they're listed
>     in actually corresponds to how often I hit the problems.
>
>
> I hit lot of older harder (now solved) issues - now, with more
> experience I am able to see these issues. And I wrote plpgsql_check,
> partially for self too. Years ago I prefer safe expressions.

Recognizing a problem ahead of time (or having plpgsql_check do it for 
you) still means you have to find a way to work around it. In some cases 
(ie: STRICT), that workaround can be a serious PITA. Better to just 
eliminate the problem itself.

>     I think trying to move the ball forward in a meaningful way without
>     breaking compatibility is a lost cause. Some of these issues could
>     be addressed by adding more syntax, but even that has limits (do we
>     really want another variation of STRICT that allows only 0 or 1
>     rows?). And there's no way to fix your #1 item below without
>     breaking compatibility.
>
>
> I think so there is way with extra check, or with persistent plpgsql
> options - just use it, please. Some checks are clear, some other not.

I will assert that there will ALWAYS be problems that you can't plaster 
over with some kind of extra checking (like plpgsql_check). At some 
point, in order to fix those, you have to somehow break compatibility.

Look at libpq as an example. There's a reason we're on protocol V3.

> If you know ALGOL family languages, then it is not problem. What is a

Lets be realistic... what % of our users have even heard of ALGOL, let 
alone used it? :)

> harder problem for people is different implementation of mix SQL and PL
> - different than Oracle, or MSSQL. Our model is better, simpler but
> different. It is difficult for people without knowleadge of differences
> between functions and procedures. Partially we badly speaking so our
> void functions are procedures.

I suspect that's only confusing for people coming from Oracle (which of 
course is a non-trivial number of people).

>     #6: The variations of syntax between the FOR variants is annoying
>     (specifically, FOREACH necessitating the ARRAY keyword).
>
>
> this is design - FOR is old PL/SQL syntax. FOREACH is prepared for
> extending

Understood. It still sucks though. :)

>     #8: EVERYTHING command option should accept a variable. In
>     particular, RAISE should accept a variable for level, but there's
>     other cases of this I've run into. I'd also be nice if you could
>     plop variables into SQL commands where you'd have an identifier,
>     though presumably that would require some kind of explicit variable
>     identifier.
>
>
> It is hiding dynamic SQL - I am strongly against it - minimally due
> performance issues. Important functionality should not be hidden.

There's definitely ways around the performance issue. I do agree that it 
needs to be clear when you're doing something dynamic so it's not 
accidental. One way to do that would be to add support for variable 
decorators and mandate the use of decorators when using a variable for 
an identifier.

That said, *every* option to RAISE can be a variable except the level. 
That's just plain silly and should be fixed.

>     #13: cstring support would allow a lot more people to experiment
>     with things like custom types. Yes, plpgsql might be slow as hell
>     for this, but sometimes that doesn't matter. Even if it does, it can
>     be a lot easier to prototype in something other than C. (Granted, I
>     think there's some non-plpgsql stuff that would need to happen to
>     allow this.)
>
>
> Not sure about it (I have really realy wrong experience with some
> developers about performance) - but PLPython, PLPerl can do it well, and
> I miss some possibility - We can use transformations more time - SQL/MM
> is based on new datatypes and transformations.

Well, there's probably some other things that could be done to make 
plpgsql perform better in this regard. One thing I've wondered about is 
allowing array-like access to a plain string (or in this case, cstring). 
That would allow you to write code that would translate much better into 
fast C code. IE: if you needed to scan through an entire string you 
could do something like for (i=0; i<strlen(); i++).

> yes. The design of transaction controlling inside stored procedures is
> hard work not related to any PL. Some can be partially solved by
> functions executed in autonomous transactions. With background workers
> we can implement asynchronous autonomous transactions - what can
> coverage lot of use cases where transaction controlling should be used
> in other databases.

Well, those are all really hacks around a fundamental problem of 
allowing user-defined, pre-stored code to execute outside of a 
transaction. I don't think solving that is necessarily *that* hard 
(though certainly non-trivial), but the fmgr interface is certainly NOT 
the right way to go about that. In any case, that's a completely 
different discussion.
-- 
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)