Re: merging some features from plpgsql2 project

2016-12-28 5:09 GMT+01:00 Jim Nasby <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>
>> wrote:
>>
>>> First I describe my initial position. I am strongly against introduction
>>> "new" language - plpgsql2 or new plpgsql, or any else. The trust of
>>> developers to us is important and introduction of any not compatible or
>>> different feature has to have really big reason. PostgreSQL is
>>> conservative
>>> environment, and PLpgSQL should not be a exception. More - I have not any
>>>
>>
> 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

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

>
> This is NOT unique to plpgsql. plpython (for one) definitely has some
> stupidity that will require an API break to fix.
>
> A secondary issue is the lack of a blessed collection of extensions. If we
> had that we could maintain some of this stuff outside of the core release
> schedule, as well as provide more room for people to run experimental
> versions of extensions if they desired. If we had this then perhaps
> plpgsql_check would become a viable answer to some of this (though IMHO
> plpgsql_check is just a work-around for our lack of dealing with API
> compatibility).
>

plpgsql_check can do some test, that are impossible in plpgsql - from
performance view, from features. But some "blessed collections of
extension" can be nice. More if will be joined with some automatic test and
build tools. Although lot of extensions are really mature, the knowleadge
about these extensions are minimal - and building extensions on windows is
hard work still (for Linux developer).

> information from my customers, colleagues about missing features in this
>>> language. If there is some gaps, then it is in outer environment - IDE,
>>> deployment, testing,
>>>
>>
> 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.

>
> Breaking language compatibility is a really big deal. There has to be
>> a lot of benefits to the effort and you have to make translation from
>> plpgsql1 to plpgsql2 really simple. You have made some good points on
>>
>
> 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.

> There *are* other ways this could be done, besides creating a different
> PL. One immediate possibility is custom GUCs; there may be other options.
>
> #1 problem with plpgsql in my point of view is that the language and
>> grammar are not supersets of sql. A lot of PLPGSQL keywords (EXECUTE,
>> BEGIN, INTO, END) have incompatible meanings with our SQL
>> implementation. IMNSHO, SQL ought to give the same behavior inside or
>> outside of plpgsql. It doesn't, and this is one of the reasons why
>> plpgsql may not be a good candidate for stored procedure
>> implementation.
>>
>
> While this doesn't bug me, it's got to be confusing as hell for newbies.
>

If you know ALGOL family languages, then it is not problem. What is a
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.

>
> #2 problem with plpgsql is after function entry it's too late to do
>> things like set transaction isolation level and change certain kinds
>> of variables (like statement_timeout). This is very obnoxious, I
>> can't wrap the database in an API 100%; the application has to manage
>> things that really should be controlled in SQL.
>>
>
> +1
>
> #3 problem with plpgsql is complete lack of inlining. inlining
>> function calls in postgres is a black art even for very trivial cases.
>> This makes it hard for us to write quick things and in the worst case
>> causes endless duplications of simple expressions.
>>
>
> Instead of banging our heads against the fmgr API to try and solve this, I
> suspect it would be much simpler (and easier to understand) if we had the
> equivalent to a #define for queries. The fmgr API just isn't amenable to
> trying to inline stuff. This would allow you to define things like views
> that accept arguments, so you can shove the argument way down in the guts
> of the query without getting tripped up by fences.
>
> Here's some other plpgsql pain-points (though, not all of these require an
> API break):
>
> #4: it's impossible to operate on a Datum in-place. Though, maybe the work
> Tom did with ExpandedObjects eliminates some of this problem, but if it
> does it's hidden behind the existing syntax and you have no way to know it
> (and AFAICT the only thing using that infrastructure right now is arrays).
> Aside from the performance aspects, it'd be damn nice to be able to do
> things like ++, +=, etc.
>
> #5: handling of parameter name collisions still sucks. One way to improve
> this would be to put parameters inside the outer-most statement block, so
> you could use a block name that was different from the function name.
> Something else that might help is the ability to assign a namespace for
> query identifiers, so you don't have to alias every individual relation in
> a query.
>
> #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

>
> #7: = vs := vs INTO. = can do everything the others can do except for
> STRICT, and when it comes to STRICT I actually wish we had language support
> for whether 0 or >1 rows are allowed. I've wanted that in the past for
> views, and if we had that then you'd be able to use it in SQL functions as
> well. If that's not possible then we should fid some other way to handle
> this in plpgsql, because STRICT is often too broad.
>
> #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.

>
> #9: You should be able to modify an exception before re-raising it.

> #10: Exception information should be passed around as a composite

I have not any problem with last two points

> .
>
> #11: Composite support is very lacking. There's no easy way to get a list
> of what fields exist in a composite, let alone do something generic to some
> set of them. There are ways to work around this, but they're very tedious
> and ugly.
>

+1

>
> #12: It'd be nice if any was allowed, as there are operations that can
> apply to more than one class of data type.
>
> #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.

>
> In short I guess the issue is that we don't have stored procedures and
>> I don't see an easy path to getting there with the current language.
>> There are a lot of other little annoyances but most of them can be
>> solved without a compatibility break.
>>
>
> Well, actual stored *procedures* is an entirely different problem, which
> (again) fmgr is absolutely not designed to handle. All the PL handlers that
> I've looked at have completely in-grained the notion that they're running
> inside a transaction, so it would be a lot of work to try and change that.
> While there may be some plpgsql-specific problems with it supporting stored
> procs, there are much bigger questions to answer before worrying about that.

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.

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