Re: 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(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.

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