2016-12-27 23:56 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: > On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule <pavel.steh...@gmail.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 >
