Re: merging some features from plpgsql2 project

2016-12-28 18:54 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 12/28/16 7:16 AM, Pavel Stehule wrote:
>
>>
>>
>> 2016-12-28 5:09 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com
>> <mailto:Jim(dot)Nasby(at)bluetreble(dot)com>>:
>>
>> On 12/27/16 4:56 PM, Merlin Moncure wrote:
>>
>> On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule
>> <pavel(dot)stehule(at)gmail(dot)com <mailto:pavel(dot)stehule(at)gmail(dot)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? :)

not too much - but the problem is not in BEGIN, END. I wrote PL/PSM where
BEGIN END doesn't exists. The functionality was same as PLpgSQL - and there
was not anybody who use 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.

I am sorry - I read it wrong - If there is not a parser issue, then it can
be fixed simply.

>
>
> #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++).

It is fixable - and faster string operations can be nice feature - Just I
am not volunteer for this task :)

>
>
> 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.

This points needs real use cases. Our current design is limited, but it
isn't bad - It is much better than Oracle or MSSQL design - we can talk
what use cases we can solve. I am sure, so we can find good mapping for
PLpgSQL for any design.

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)
>