On 12/27/16 4:56 PM, Merlin Moncure wrote:
On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule <pavel.steh...@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

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.


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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to