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)