Re: [HACKERS] merging some features from plpgsql2 project

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

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

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

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

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

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.

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

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

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

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

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

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

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