merging some features from plpgsql2 project

2016-12-27 8:54 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

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,

I forgot - the big plpgsql issue are too weak expressions on left part of
assignment statements.

On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule <pavel.stehule@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.

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

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.

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:

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?

merlin

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

2016-12-27 23:56 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

On Tue, Dec 27, 2016 at 1:54 AM, Pavel Stehule <pavel.stehule@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

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)

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

2016-12-28 5:09 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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.

ok. Just I would not to repeat Perl6 or Python3 story - it is big
adventure, but big fail too

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

We have not, but there are few possibilities:

1. enhance #option command
2. we can introduce PRAGMA command
https://en.wikipedia.org/wiki/Ada_(programming_language)#Pragmas

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

plpgsql_check can do some test, that are impossible in plpgsql - from
performance view, from features. But some "blessed collections of
extension" can be nice. More if will be joined with some automatic test and
build tools. Although lot of extensions are really mature, the knowleadge
about these extensions are minimal - and building extensions on windows is
hard work still (for Linux developer).

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.

I hit lot of older harder (now solved) issues - now, with more experience I
am able to see these issues. And I wrote plpgsql_check, partially for self
too. Years ago I prefer safe expressions.

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.

I think so there is way with extra check, or with persistent plpgsql
options - just use it, please. Some checks are clear, some other not.

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.

If you know ALGOL family languages, then it is not problem. What is a
harder problem for people is different implementation of mix SQL and PL -
different than Oracle, or MSSQL. Our model is better, simpler but
different. It is difficult for people without knowleadge of differences
between functions and procedures. Partially we badly speaking so our void
functions are procedures.

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

this is design - FOR is old PL/SQL syntax. FOREACH is prepared for
extending

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

It is hiding dynamic SQL - I am strongly against it - minimally due
performance issues. Important functionality should not be hidden.

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

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

I have not any problem with last two points

.

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

+1

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

Not sure about it (I have really realy wrong experience with some
developers about performance) - but PLPython, PLPerl can do it well, and I
miss some possibility - We can use transformations more time - SQL/MM is
based on new datatypes and transformations.

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.

yes. The design of transaction controlling inside stored procedures is hard
work not related to any PL. Some can be partially solved by functions
executed in autonomous transactions. With background workers we can
implement asynchronous autonomous transactions - what can coverage lot of
use cases where transaction controlling should be used in other databases.

Regards

Pavel

On 12/28/16 7:16 AM, Pavel Stehule wrote:

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

We have not, but there are few possibilities:

1. enhance #option command
2. we can introduce PRAGMA command
https://en.wikipedia.org/wiki/Ada_(programming_language)#Pragmas
<https://en.wikipedia.org/wiki/Ada_%28programming_language%29#Pragmas&gt;

I wanted to break this out separately, because IMO it's the real heart
of the matter.

I think it would be silly not to allow a global setting of
compatibility. You certainly don't want to force people to stick magic
keywords in their code forevermore.

To that end, would GUCs be a workable answer here? That should give you
the ability to control incompatibilities at a function, user, database
and global level. It would also allow you to chose between raising a
WARNING vs a FATAL.

I realize we've had some bad experiences with compatibility GUCs in the
past, but I'd argue we've also had some good experiences. I see that
add_missing_from is now completely gone, for example, presumably with no
complaints. There's probably several other compatibility GUCs we could
remove now.
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/28/16 7:16 AM, Pavel Stehule wrote:

2016-12-28 5:09 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
<mailto:Jim.Nasby@bluetreble.com>>:

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 <mailto:pavel.stehule@gmail.com>> wrote:
Which is why this is an external fork of plpgsql.

ok. Just I would not to repeat Perl6 or Python3 story - it is big
adventure, but big fail too

Yeah, creating an entirely "new" PL to deal with compatibility doesn't
seem like a good idea to me.

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

We have not, but there are few possibilities:

1. enhance #option command
2. we can introduce PRAGMA command
https://en.wikipedia.org/wiki/Ada_(programming_language)#Pragmas

See separate reply.

<discussion about "blessed extensions">

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.

I hit lot of older harder (now solved) issues - now, with more
experience I am able to see these issues. And I wrote plpgsql_check,
partially for self too. Years ago I prefer safe expressions.

Recognizing a problem ahead of time (or having plpgsql_check do it for
you) still means you have to find a way to work around it. In some cases
(ie: STRICT), that workaround can be a serious PITA. Better to just
eliminate the problem itself.

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.

I think so there is way with extra check, or with persistent plpgsql
options - just use it, please. Some checks are clear, some other not.

I will assert that there will ALWAYS be problems that you can't plaster
over with some kind of extra checking (like plpgsql_check). At some
point, in order to fix those, you have to somehow break compatibility.

Look at libpq as an example. There's a reason we're on protocol V3.

If you know ALGOL family languages, then it is not problem. What is a

Lets be realistic... what % of our users have even heard of ALGOL, let
alone used it? :)

harder problem for people is different implementation of mix SQL and PL
- different than Oracle, or MSSQL. Our model is better, simpler but
different. It is difficult for people without knowleadge of differences
between functions and procedures. Partially we badly speaking so our
void functions are procedures.

I suspect that's only confusing for people coming from Oracle (which of
course is a non-trivial number of people).

#6: The variations of syntax between the FOR variants is annoying
(specifically, FOREACH necessitating the ARRAY keyword).

this is design - FOR is old PL/SQL syntax. FOREACH is prepared for
extending

Understood. It still sucks though. :)

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

It is hiding dynamic SQL - I am strongly against it - minimally due
performance issues. Important functionality should not be hidden.

There's definitely ways around the performance issue. I do agree that it
needs to be clear when you're doing something dynamic so it's not
accidental. One way to do that would be to add support for variable
decorators and mandate the use of decorators when using a variable for
an identifier.

That said, *every* option to RAISE can be a variable except the level.
That's just plain silly and should be fixed.

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

Not sure about it (I have really realy wrong experience with some
developers about performance) - but PLPython, PLPerl can do it well, and
I miss some possibility - We can use transformations more time - SQL/MM
is based on new datatypes and transformations.

Well, there's probably some other things that could be done to make
plpgsql perform better in this regard. One thing I've wondered about is
allowing array-like access to a plain string (or in this case, cstring).
That would allow you to write code that would translate much better into
fast C code. IE: if you needed to scan through an entire string you
could do something like for (i=0; i<strlen(); i++).

yes. The design of transaction controlling inside stored procedures is
hard work not related to any PL. Some can be partially solved by
functions executed in autonomous transactions. With background workers
we can implement asynchronous autonomous transactions - what can
coverage lot of use cases where transaction controlling should be used
in other databases.

Well, those are all really hacks around a fundamental problem of
allowing user-defined, pre-stored code to execute outside of a
transaction. I don't think solving that is necessarily *that* hard
(though certainly non-trivial), but the fmgr interface is certainly NOT
the right way to go about that. In any case, that's a completely
different discussion.
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-12-28 18:54 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 12/28/16 7:16 AM, Pavel Stehule wrote:

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

We have not, but there are few possibilities:

1. enhance #option command
2. we can introduce PRAGMA command
https://en.wikipedia.org/wiki/Ada_(programming_language)#Pragmas
<https://en.wikipedia.org/wiki/Ada_%28programming_language%29#Pragmas&gt;

I wanted to break this out separately, because IMO it's the real heart of
the matter.

I think it would be silly not to allow a global setting of compatibility.
You certainly don't want to force people to stick magic keywords in their
code forevermore.

To that end, would GUCs be a workable answer here? That should give you
the ability to control incompatibilities at a function, user, database and
global level. It would also allow you to chose between raising a WARNING vs
a FATAL.

GUC are fragile - the source code and settings can be separated.

Our #option is more robust, because source code holds all flags required
for execution. So I would to see a mechanism, that will be strongly joined
with code.

Using function assigned GUC is similar, but it is looking less robust - and
some editors can forgot this information.

Lot of issues we can solved by plpgsq.extra_error, extra_warnings - but
probably not all - for example issue of FOUND variable or introducing new
auto variable ROW_COUNT. PLpgSQL - PL/SQL is safe - it propose the
statement GET DIAGNOSTICS, but I understand so isn't funny to write more
and more GET DIAGNOSTICS rc = ROW_COUNT; So some shortcuts can be nice, but
there is risk, so this shortcut breaks existing code, and the
costs/benefits are individual. There cannot be 100% agreement ever. So some
customisation should be good.

On 12/28/16 12:15 PM, Pavel Stehule wrote:

GUC are fragile - the source code and settings can be separated.

*Can* be, but they don't *have* to be. That's a huge feature, not a bug.

Our #option is more robust, because source code holds all flags required
for execution. So I would to see a mechanism, that will be strongly
joined with code.

That means you must ALWAYS specify, which is an enormous pain. It
basically guarantees that users will NEVER switch to the new syntax.

Using function assigned GUC is similar, but it is looking less robust -
and some editors can forgot this information.

If you forget then you get an error. Then you remember.

Lot of issues we can solved by plpgsq.extra_error, extra_warnings - but
probably not all - for example issue of FOUND variable or introducing
new auto variable ROW_COUNT. PLpgSQL - PL/SQL is safe - it propose the
statement GET DIAGNOSTICS, but I understand so isn't funny to write more
and more GET DIAGNOSTICS rc = ROW_COUNT; So some shortcuts can be nice,
but there is risk, so this shortcut breaks existing code, and the
costs/benefits are individual. There cannot be 100% agreement ever. So
some customisation should be good.

That's the whole point of having settings to deal with
incompatibilities: so we can actually fix these warts without breaking
everyone's code, yet also make it clear to users that they should stop
using the warts and instead use the new and improved syntax.
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-12-28 19:23 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 12/28/16 12:15 PM, Pavel Stehule wrote:

GUC are fragile - the source code and settings can be separated.

*Can* be, but they don't *have* to be. That's a huge feature, not a bug.

Our #option is more robust, because source code holds all flags required

for execution. So I would to see a mechanism, that will be strongly
joined with code.

That means you must ALWAYS specify, which is an enormous pain. It
basically guarantees that users will NEVER switch to the new syntax.

Using function assigned GUC is similar, but it is looking less robust -

and some editors can forgot this information.

If you forget then you get an error. Then you remember.

Lot of issues we can solved by plpgsq.extra_error, extra_warnings - but

probably not all - for example issue of FOUND variable or introducing
new auto variable ROW_COUNT. PLpgSQL - PL/SQL is safe - it propose the
statement GET DIAGNOSTICS, but I understand so isn't funny to write more
and more GET DIAGNOSTICS rc = ROW_COUNT; So some shortcuts can be nice,
but there is risk, so this shortcut breaks existing code, and the
costs/benefits are individual. There cannot be 100% agreement ever. So
some customisation should be good.

That's the whole point of having settings to deal with incompatibilities:
so we can actually fix these warts without breaking everyone's code, yet
also make it clear to users that they should stop using the warts and
instead use the new and improved syntax.

Now, the incompatibility can be hard issue - it is big question if we lock
some users on old versions because some users can save to lines of code.
Introduction of ROW_COUNT is lowly incompatibility - it can be simply
detected - but for example change of behave of FOUND variable is terrible,
because the code will be quietly calculate differently. sometimes we can
break code - probably people will not be happy, but sometimes we can change
the results - it can be big fail. So on one side is big costs. On second
side is few lines less code.

Regards

Pavel

2016-12-28 18:54 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 12/28/16 7:16 AM, Pavel Stehule wrote:

2016-12-28 5:09 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
<mailto:Jim.Nasby@bluetreble.com>>:

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 <mailto:pavel.stehule@gmail.com>> wrote:
Which is why this is an external fork of plpgsql.

ok. Just I would not to repeat Perl6 or Python3 story - it is big
adventure, but big fail too

Yeah, creating an entirely "new" PL to deal with compatibility doesn't
seem like a good idea to me.

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

We have not, but there are few possibilities:

1. enhance #option command
2. we can introduce PRAGMA command
https://en.wikipedia.org/wiki/Ada_(programming_language)#Pragmas

See separate reply.

<discussion about "blessed extensions">

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.

I hit lot of older harder (now solved) issues - now, with more
experience I am able to see these issues. And I wrote plpgsql_check,
partially for self too. Years ago I prefer safe expressions.

Recognizing a problem ahead of time (or having plpgsql_check do it for
you) still means you have to find a way to work around it. In some cases
(ie: STRICT), that workaround can be a serious PITA. Better to just
eliminate the problem itself.

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.

I think so there is way with extra check, or with persistent plpgsql
options - just use it, please. Some checks are clear, some other not.

I will assert that there will ALWAYS be problems that you can't plaster
over with some kind of extra checking (like plpgsql_check). At some point,
in order to fix those, you have to somehow break compatibility.

Look at libpq as an example. There's a reason we're on protocol V3.

If you know ALGOL family languages, then it is not problem. What is a

Lets be realistic... what % of our users have even heard of ALGOL, let
alone used it? :)

not too much - but the problem is not in BEGIN, END. I wrote PL/PSM where
BEGIN END doesn't exists. The functionality was same as PLpgSQL - and there
was not anybody who use it.

harder problem for people is different implementation of mix SQL and PL

- different than Oracle, or MSSQL. Our model is better, simpler but
different. It is difficult for people without knowleadge of differences
between functions and procedures. Partially we badly speaking so our
void functions are procedures.

I suspect that's only confusing for people coming from Oracle (which of
course is a non-trivial number of people).

#6: The variations of syntax between the FOR variants is annoying

(specifically, FOREACH necessitating the ARRAY keyword).

this is design - FOR is old PL/SQL syntax. FOREACH is prepared for
extending

Understood. It still sucks though. :)

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

It is hiding dynamic SQL - I am strongly against it - minimally due
performance issues. Important functionality should not be hidden.

There's definitely ways around the performance issue. I do agree that it
needs to be clear when you're doing something dynamic so it's not
accidental. One way to do that would be to add support for variable
decorators and mandate the use of decorators when using a variable for an
identifier.

That said, *every* option to RAISE can be a variable except the level.
That's just plain silly and should be fixed.

I am sorry - I read it wrong - If there is not a parser issue, then it can
be fixed simply.

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

Not sure about it (I have really realy wrong experience with some
developers about performance) - but PLPython, PLPerl can do it well, and
I miss some possibility - We can use transformations more time - SQL/MM
is based on new datatypes and transformations.

Well, there's probably some other things that could be done to make
plpgsql perform better in this regard. One thing I've wondered about is
allowing array-like access to a plain string (or in this case, cstring).
That would allow you to write code that would translate much better into
fast C code. IE: if you needed to scan through an entire string you could
do something like for (i=0; i<strlen(); i++).

It is fixable - and faster string operations can be nice feature - Just I
am not volunteer for this task :)

yes. The design of transaction controlling inside stored procedures is

hard work not related to any PL. Some can be partially solved by
functions executed in autonomous transactions. With background workers
we can implement asynchronous autonomous transactions - what can
coverage lot of use cases where transaction controlling should be used
in other databases.

Well, those are all really hacks around a fundamental problem of allowing
user-defined, pre-stored code to execute outside of a transaction. I don't
think solving that is necessarily *that* hard (though certainly
non-trivial), but the fmgr interface is certainly NOT the right way to go
about that. In any case, that's a completely different discussion.

This points needs real use cases. Our current design is limited, but it
isn't bad - It is much better than Oracle or MSSQL design - we can talk
what use cases we can solve. I am sure, so we can find good mapping for
PLpgSQL for any design.

Regards

Pavel

On 12/28/16 12:51 PM, Pavel Stehule wrote:

Now, the incompatibility can be hard issue - it is big question if we
lock some users on old versions because some users can save to lines of
code. Introduction of ROW_COUNT is lowly incompatibility - it can be
simply detected - but for example change of behave of FOUND variable is
terrible, because the code will be quietly calculate differently.
sometimes we can break code - probably people will not be happy, but
sometimes we can change the results - it can be big fail. So on one side
is big costs. On second side is few lines less code.

That's my whole point of why this needs to be settable at a global
level: so that people with a lot of legacy code can set the OLD behavior
at a global level, and deal with the old code over time.

If there's no global setting then there are only two choices: we default
to new behavior and force everyone to add a bunch of stuff to *every*
function they have (loads of complaints), or we default to old behavior
and no one bothers to even adopt the new usage because they have to add
extra stuff to every function. Either way is a failure. This is why I
think there MUST be some way to control this at a higher level than per
function.

Certainly GUCs aren't the only option, we could invent something else.
One feature I could see being useful is being able to set a default on a
schema level, which isn't currently possible with a GUC. But I can
certainly see database and global settings being useful, and perhaps
per-user as well. GUCs already have those.
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-12-28 20:25 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 12/28/16 12:51 PM, Pavel Stehule wrote:

Now, the incompatibility can be hard issue - it is big question if we
lock some users on old versions because some users can save to lines of
code. Introduction of ROW_COUNT is lowly incompatibility - it can be
simply detected - but for example change of behave of FOUND variable is
terrible, because the code will be quietly calculate differently.
sometimes we can break code - probably people will not be happy, but
sometimes we can change the results - it can be big fail. So on one side
is big costs. On second side is few lines less code.

That's my whole point of why this needs to be settable at a global level:
so that people with a lot of legacy code can set the OLD behavior at a
global level, and deal with the old code over time.

If there's no global setting then there are only two choices: we default
to new behavior and force everyone to add a bunch of stuff to *every*
function they have (loads of complaints), or we default to old behavior and
no one bothers to even adopt the new usage because they have to add extra
stuff to every function. Either way is a failure. This is why I think there
MUST be some way to control this at a higher level than per function.

we can have both - plpgsql.variable_conflict can be precedent.

Certainly GUCs aren't the only option, we could invent something else. One
feature I could see being useful is being able to set a default on a schema
level, which isn't currently possible with a GUC. But I can certainly see
database and global settings being useful, and perhaps per-user as well.
GUCs already have those.

yes, without GUC you cannot set the behave of plpgsql globally.

Regards

Pavel

Hi

I wrote some initial patch

Do you think so has sense to continue in this topic?

Regards

Pavel

On 1/1/17 12:17 PM, Pavel Stehule wrote:

I wrote some initial patch

Do you think so has sense to continue in this topic?

Perhaps I'm not understanding what plpgsql_extra_errors does, but I
don't think either of these should depend on that being true. IMO these
two checks should be default to throwing an exception.

I think instead of tying these to extra_*, each GUC should accept a LOG
level.
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-02 18:36 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 1/1/17 12:17 PM, Pavel Stehule wrote:

I wrote some initial patch

Do you think so has sense to continue in this topic?

Perhaps I'm not understanding what plpgsql_extra_errors does, but I don't
think either of these should depend on that being true. IMO these two
checks should be default to throwing an exception.

There are use cases where these patters should be used and has sense like

SELECT (polymorphiccomposite).* INTO c1, c2; -- take first two columns

SELECT xx FROM tab ORDER BY yy INTO target -- more rows not a issue

I understand plpgsql_extra_errors as feature that can be enabled on
developer, test, or preprod environments and can help to identify some
strange places.

I think instead of tying these to extra_*, each GUC should accept a LOG
level.

Why? Why the none, warning, error are not enough? Why are you think so
separate GUC can be better than plpgsql_extra_* ?

The fast setting plpgsql.extra_errors = 'all' can switch to some "safe"
configuration.
The fast setting plpgsql.extra_warnings = 'all' can helps with
identification, but doesn't break production (or doesn't breaks other tests)

Regards

Pavel

On 1/2/17 12:06 PM, Pavel Stehule wrote:

SELECT (polymorphiccomposite).* INTO c1, c2; -- take first two columns

SELECT xx FROM tab ORDER BY yy INTO target -- more rows not a issue

I understand plpgsql_extra_errors as feature that can be enabled on
developer, test, or preprod environments and can help to identify some
strange places.

Yes, but the two cases you mentioned above are the "strange" cases, and
you should have to do something extra to allow those, not the other way
around.

I think instead of tying these to extra_*, each GUC should accept a
LOG level.

Why? Why the none, warning, error are not enough? Why are you think so
separate GUC can be better than plpgsql_extra_* ?

The fast setting plpgsql.extra_errors = 'all' can switch to some "safe"
configuration.
The fast setting plpgsql.extra_warnings = 'all' can helps with
identification, but doesn't break production (or doesn't breaks other tests)

I see two problems with those settings:

1) Neither is enabled by default, so 90% of users have no idea they
exist. Obviously that's an easy enough fix, but...
2) There's no way to incrementally change those values for a single
function. If you've set extra_errors = 'all' globally, a single function
can't say "turn off the too many rows setting for this function".

BTW, while I can see value in being able to change these settings for an
entire function, I think the recommended use should be to only change
them for a specific statement.
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-02 20:16 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 1/2/17 12:06 PM, Pavel Stehule wrote:

SELECT (polymorphiccomposite).* INTO c1, c2; -- take first two columns

SELECT xx FROM tab ORDER BY yy INTO target -- more rows not a issue

I understand plpgsql_extra_errors as feature that can be enabled on
developer, test, or preprod environments and can help to identify some
strange places.

Yes, but the two cases you mentioned above are the "strange" cases, and
you should have to do something extra to allow those, not the other way
around.

The second example is really strange. But the first example is used in
composite types conversion - when you convert from base to extend type.
This routine is used in plpgsql when you use a assignment statement

composite_var := another_composite_var

I think instead of tying these to extra_*, each GUC should accept a

LOG level.

Why? Why the none, warning, error are not enough? Why are you think so
separate GUC can be better than plpgsql_extra_* ?

The fast setting plpgsql.extra_errors = 'all' can switch to some "safe"
configuration.
The fast setting plpgsql.extra_warnings = 'all' can helps with
identification, but doesn't break production (or doesn't breaks other
tests)

I see two problems with those settings:

1) Neither is enabled by default, so 90% of users have no idea they exist.
Obviously that's an easy enough fix, but...

We can strongly talk about it - there can be a chapter in plpgsql doc. Now,
the patterns and antipatterns are not officially documented.

2) There's no way to incrementally change those values for a single
function. If you've set extra_errors = 'all' globally, a single function
can't say "turn off the too many rows setting for this function".

We can enhance the GUC syntax like "all -too_many_rows,-xxx"

BTW, while I can see value in being able to change these settings for an
entire function, I think the recommended use should be to only change them
for a specific statement.

What you can do in plain assign statement

target := expression ?

My border is any compatibility break - and I would not to across it. First
issue is probably harder

related to typo "select 1 x into c1,c2" and it can be detected by
plpgsql_check.

Second issue is not a performance issue today (we read only 2 rows
everytime) and it is hard how often it returns wrong result. This issue
cannot be detected by plpgsql_check now.

Regards

Pavel

On Tue, Dec 27, 2016 at 10:09 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

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

Just got back from break :-). Have some thoughts on this. Backwards
compatibility is really a fundamental problem. There's really no
solution to it other than to try and avoid using syntax to solve
problems. It should be obvious to everyone that plgsql cannot
withstand a compatibility break. Another language could be offered as
an alternative in core (say, pl/psm or pl/v8), but pl/pgsql has to
support old code. Some really out there features could maybe be
redacted (in particular, using = for assignment), but not not much.
But I guess we're stuck with the status quo.

I think we ought to avoid language features that influence the
behavior (performance is ok) of the code (and that includes throwing
errors). That's a freight train headed towards javscript's 'strict'
mode, which is thinly disguised language fork. #option and pragma
type syntaxes are trying to cheat the language -- hardly anyone uses
them and it's a tricky way to try and make the language into something
other than it is.

C does it right -- dubious code is raised as warnings and it's up to
the end user to determine which warnings are interesting and likely to
be an error. So, rather than hacking the language to control throwing
and errors and such there should be some ability validate the function
heavily and verify suspicious use of INTO or other dubious things
(unused variables, masked assignments, etc). The validation output
could even be a set returning function.

So -1 to strict mode, unless we can make a case why this can't be done
as part of checking/validation.

Other random points:
*) Another major pain point is swapping in the input variables for
debugging purposes. Something that emits a script based on a set of
arguments would be wonderful.

*) Would also like to have a FINALLY block

*) A mechanic to manually print out a stack trace for debugging
purposes would be helpful.

*) COPY not being able to accept arguments as variables (in particular
the filename) is a major headache

*) Upon error, we ought to print a few lines of context on either side
of the error. Not very fun to try and figure out where the errors is
when you are working off of 'syntax error near "FROM"' etc. This is a
major problem throughout the platform IMO.

*) Some user visible mechanic other than forcing SQL through EXECUTE
to be able to control plan caching would be useful.

merlin

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