Re: merging some features from plpgsql2 project

2016-12-27 23:56 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > Hi
> >
> > I reread ideas described on page https://github.com/trustly/plpgsql2
> >
> > Some points are well and can be benefit for PlpgSQL.
> >
> > 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
> > 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,
>
> 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
> the rationale but not nearly enough to justify implementation fork. So
> basically I agree. Having said that, If you don't mind I'd like to
> run with the topic (which I'm loosely interpreting as, "Things I'd
> like to do in SQL/PLPGSQL and can't").
>
> #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.
>

There is little bit cleaner language for this purpose - SQL/PSM. But it is
hard to switch main language without big lost of reputation. I am not sure
about benefit.

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

It is long story about implementation procedures - it is not related to
PLpgSQL - the language is not a issue.

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

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

I don't think so implementation of procedures will be simple, but I don't
see any issue in PLpgSQL.

> It would be pretty neat if postgres SQL implementation could directly
> incorporate limited flow control and command execution. For example,
> CREATE my_proc(Done OUT BOOL) RETURNS BOOL AS
> $$
> BEGIN;
> SET transaction_isolation = 'serializable';
> SELECT some_plpgsql_func_returning_bool();
> COMMIT;
> $$;
> CALL my_proc() UNTIL Done;
>
> Key points here are:
> *) my_proc is in native SQL (not plpgsql), and run outside of snapshot
> *) CALL is invocation into stored procedure. I extended it in similar
> fashion as pl/sql CALL
> (https://docs.oracle.com/cd/B19306_01/server.102/b14200/
> statements_4008.htm)
> but anything will do for syntaxs as long as you get arbitrary control
> of procedure lifetime external to snapshot and transaction
> *) simple addition of UNTIL gets us out of the debate for best 'stored
> procedure language'. Keeping things to pure SQL really simplifies
> things since we already have statement parsing at tcop level. We just
> need some special handling for CALL.
> *) In my usage of plpgsql maybe 80% of database cases are covered
> purely in language but maybe 20% of cases need support from
> application typically where threading and transaction management is
> involved. With the above it would be more like 95% would be covered
> and if you extended CALL to something like:
>

It is similar to my older proposals of stored procedures.

>
> CALL my_proc() IN BACKGROUND UNTIL Done;
>
> ..where "IN BACKGOUND" moved execution to a background worker one
> could do just about everything in SQL in tasks that do nothing but
> read and write to the database that today need significant support
> from outside language (primarily bash for me).
>
> With respect to stuff you mentioned, like smarter handling of INTO,
> are you really sure you need to break compatibility for that?
>

I didn't propose any compatibility break.

Can we talk about another proposals separately, please. Stored procedures,
batch processing, different language are different topic.

Regards

Pavel

>
> merlin
>