Re: PL/pgSQL 2

Hi,

For those of you who use PL/pgSQL every day, I'm quite certain you all feel
there are a number of things you would like to change in the language, but
realize it cannot be achieved without possibly breaking compatibility, at
least in theory. Even though you own code would survive the change, there
might be code somewhere in the world which would break. This is of course
not acceptable and that's why we have the current status quo of
development, or at least not far away from a status quo.

So instead of continue to adding optional settings to the config file, and
instead of killing discussions around what can be done by bringing up the
backwards-compatibility argument, let's instead fork the language and call
it plpgsql2. Since no code is yet written in plpgsql2, we can start of from
a clean sheet, and no good ideas need to be killed due to
backwards-compatibility concerns.

The interest for such a project is probably limited to a small number of
companies/people around the world, as most users are probably perfectly
happy with the current version of plpgsql, as they only use it occasionally
and not every day like we do at my company.

Just like with plpgsql, once released, plpgsql2 cannot break compatibility
with future versions, so we only have one chance to carefully think though
what we would like to change in the language.

From the top of my head, these are Things I personally would want to see in
plpgsql2:
+ Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
row, as that's the most common use-case, and provide alternative syntax to
modify multiple or zero rows.
+ Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
STRICT only works if no rows should be an error, but there is currently no
nice way if no rows OR exactly 1 row should be found by the query.
+ Change all warnings into errors

These are small changes, probably possible with just a few hundred lines of
code in total, which also should be the ambition, as larger changes would
never survive during time as it would require too much efforts to keep up
with the main project. Secondly, I trust plpgsql mainly because it's being
used by a lot of people in a lot of production systems, the same would not
hold true for plpgsql2 for the first years of existence, so we who would
use it in production systems must understand every single line of code
changed and feel the risk of possible bugs and their impact are within
acceptable boundaries.

I can probably think of a few more things, but these are the major
annoyances.

Please share your wish list of things you would want in plpgsql2 which are
not possible to implement in plpgsql because they could possibly break
compatibility.

Regards, Joel

On 2014-09-01 11:04:53 +0200, Joel Jacobson wrote:
> For those of you who use PL/pgSQL every day, I'm quite certain you all feel
> there are a number of things you would like to change in the language, but
> realize it cannot be achieved without possibly breaking compatibility, at
> least in theory. Even though you own code would survive the change, there
> might be code somewhere in the world which would break. This is of course
> not acceptable and that's why we have the current status quo of
> development, or at least not far away from a status quo.
>
> So instead of continue to adding optional settings to the config file, and
> instead of killing discussions around what can be done by bringing up the
> backwards-compatibility argument, let's instead fork the language and call
> it plpgsql2. Since no code is yet written in plpgsql2, we can start of from
> a clean sheet, and no good ideas need to be killed due to
> backwards-compatibility concerns.
>
> The interest for such a project is probably limited to a small number of
> companies/people around the world, as most users are probably perfectly
> happy with the current version of plpgsql, as they only use it occasionally
> and not every day like we do at my company.
>
> Just like with plpgsql, once released, plpgsql2 cannot break compatibility
> with future versions, so we only have one chance to carefully think though
> what we would like to change in the language.
>
> From the top of my head, these are Things I personally would want to see in
> plpgsql2:
> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
> row, as that's the most common use-case, and provide alternative syntax to
> modify multiple or zero rows.
> + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
> STRICT only works if no rows should be an error, but there is currently no
> nice way if no rows OR exactly 1 row should be found by the query.
> + Change all warnings into errors

-many.

Look at the *disaster* the few minor changes in python3 were. It's now,
years after, only starting to get used again.

You're going to have to find a more gradual way of doing this.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 11:24 AM, Andres Freund wrote:
> On 2014-09-01 11:04:53 +0200, Joel Jacobson wrote:
>> For those of you who use PL/pgSQL every day, I'm quite certain you all feel
>> there are a number of things you would like to change in the language, but
>> realize it cannot be achieved without possibly breaking compatibility, at
>> least in theory. Even though you own code would survive the change, there
>> might be code somewhere in the world which would break. This is of course
>> not acceptable and that's why we have the current status quo of
>> development, or at least not far away from a status quo.
>>
>> So instead of continue to adding optional settings to the config file, and
>> instead of killing discussions around what can be done by bringing up the
>> backwards-compatibility argument, let's instead fork the language and call
>> it plpgsql2. Since no code is yet written in plpgsql2, we can start of from
>> a clean sheet, and no good ideas need to be killed due to
>> backwards-compatibility concerns.
>>
>> The interest for such a project is probably limited to a small number of
>> companies/people around the world, as most users are probably perfectly
>> happy with the current version of plpgsql, as they only use it occasionally
>> and not every day like we do at my company.
>>
>> Just like with plpgsql, once released, plpgsql2 cannot break compatibility
>> with future versions, so we only have one chance to carefully think though
>> what we would like to change in the language.
>>
>> From the top of my head, these are Things I personally would want to see in
>> plpgsql2:
>> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
>> row, as that's the most common use-case, and provide alternative syntax to
>> modify multiple or zero rows.
>> + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
>> STRICT only works if no rows should be an error, but there is currently no
>> nice way if no rows OR exactly 1 row should be found by the query.
>> + Change all warnings into errors
> -many.
>
> Look at the *disaster* the few minor changes in python3 were. It's now,
> years after, only starting to get used again.
>
> You're going to have to find a more gradual way of doing this.
Probably a better way (and there has been some talk of it) is
having some kind of PRAGMA functionality, or pl/pgsql specific
LOCAL SET to affect "just this function" and not spill to nested
functions as is the case for SETs now.

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On 9/1/14 11:53 AM, Hannu Krosing wrote:
> On 09/01/2014 11:24 AM, Andres Freund wrote:
>> Look at the *disaster* the few minor changes in python3 were. It's now,
>> years after, only starting to get used again.
>>
>> You're going to have to find a more gradual way of doing this.
> Probably a better way (and there has been some talk of it) is
> having some kind of PRAGMA functionality, or pl/pgsql specific
> LOCAL SET to affect "just this function" and not spill to nested
> functions as is the case for SETs now.

I can't imagine how that would work for anyone who has thousands of
functions.

I've tried my best over the past ~year or so, but any attempts at
breaking backwards compatibility have been rejected. I really don't see
any gradual way of doing this. We either break things, live with what
we have right now, or create a new language.

.marko

On Mon, Sep 1, 2014 at 11:24 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> -many.
>
> Look at the *disaster* the few minor changes in python3 were. It's now,
> years after, only starting to get used again.
>
> You're going to have to find a more gradual way of doing this.

I agree this is a valid concern, and is also something I've taken into
account already.

The things I would like to see change, won't break any of my own code,
and probably not much of others either.

Let's examine my list and see what impact if would have to modern
plpgsql code written according to best practices:

> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows.

If you application already aims to modify 1 row exactly at most places
in the code where you have UPDATE/INSERT/DELETE,
then making that the default behaviour of the default syntax would be
an improvement, as you wouldn't need special syntax for the default
use-case.
Instead, by providing a way to do the less common use-case of
modifying <>1 row, using an alternative way, we make it possible to
rewrite any existing code.
The amount of code we need to rewrite is minimized by optimizing the
syntax for the most common use-case.

> + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
> STRICT only works if no rows should be an error, but there is currently no
> nice way if no rows OR exactly 1 row should be found by the query.

This wouldn't have any impact for most applications, except when
things go wrong, and if the query *would* return more than 1 row you
probably want an error to be thrown, that's the most common use-case.
By making it necessary to explicity say you are OK with more than 1
row, we again optimize the syntax for the most common use-case, but
provide an alternative way of allowing the corner-case.

> + Change all warnings into errors
The warnings should of course be errors, if we wouldn't need to worry
about legacy code, which we don't with plpgsql2.

In summary, if you write new code, it will work without any changes in
most of the cases, and in the cases where you need to change it, there
is a very good valid reason why you want to do that anyway, as the
alternative is either insecure or ugly.

Also, a stricter language with fewer alternative obsolete syntax
variations and optional settings means the lines of code and the
complexity can be reduced.

Compared with the changes in python3, the changes between python2 and
python3 *did* break compatibility and all code had to be rewritten. In
contrast, most plpgsql functions won't need to be rewritten, only a
few of them will need to be rewritten, which is acceptable.

Another difference when comparing it to python3 is the possibility to
mix plpgsql and plpgsql2 functions within the same system. With
python3, you can't use existing python2 modules, but new plpgsql2
functions can of course call any existing plpgsql functions.

The "gradual way" of doing this is to add plpgsql2, and then write all
new code in the language, and then port function by function from
plpgsql to plpgsql2, without any deadlines.

On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote:
> On 9/1/14 11:53 AM, Hannu Krosing wrote:
> >>You're going to have to find a more gradual way of doing this.
> >Probably a better way (and there has been some talk of it) is
> >having some kind of PRAGMA functionality, or pl/pgsql specific
> >LOCAL SET to affect "just this function" and not spill to nested
> >functions as is the case for SETs now.
>
> I can't imagine how that would work for anyone who has thousands of
> functions.

How's that fundamentally different from changing languages? If we had a
way to *add* such attributes to *existing* functions I don't see the
fundamental problem?

> I've tried my best over the past ~year or so, but any attempts at breaking
> backwards compatibility have been rejected. I really don't see any gradual
> way of doing this. We either break things, live with what we have right
> now, or create a new language.

I think to some degree that was also influenced by the approach you
took. Several of the changes didn't really have a meaningful explanation
why they'd be helpful in the field. I.e. the change was explained, but
not the reasoning *leading* to the change and which other solutions you
thought about.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 12:00 PM, Marko Tiikkaja wrote:
> On 9/1/14 11:53 AM, Hannu Krosing wrote:
>> On 09/01/2014 11:24 AM, Andres Freund wrote:
>>> Look at the *disaster* the few minor changes in python3 were. It's now,
>>> years after, only starting to get used again.
>>>
>>> You're going to have to find a more gradual way of doing this.
>> Probably a better way (and there has been some talk of it) is
>> having some kind of PRAGMA functionality, or pl/pgsql specific
>> LOCAL SET to affect "just this function" and not spill to nested
>> functions as is the case for SETs now.
>
> I can't imagine how that would work for anyone who has thousands of
> functions.
>
> I've tried my best over the past ~year or so, but any attempts at
> breaking backwards compatibility have been rejected. I really don't
> see any gradual way of doing this. We either break things, live with
> what we have right now, or create a new language.
>
>
> .marko
My approach would be to add optional LOCAL modifier to WITH, so instead

CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = }
default_expr ] [, ...] ] )
...
[ WITH ( attribute [, ...] ) ]

it would be

...
[ WITH ( [LOCAL] attribute [, ...] ) ]

where LOCAL attributes are _not_ inherited by nested functions
but the LOCALs would shadow globals in the function definitions
that have them.

I know it is easier said than done, but from the user perspective
this could be a nice clean approach.

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On Mon, Sep 1, 2014 at 12:32 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> it would be
>
> ...
> [ WITH ( [LOCAL] attribute [, ...] ) ]
>
> where LOCAL attributes are _not_ inherited by nested functions
> but the LOCALs would shadow globals in the function definitions
> that have them.
>
> I know it is easier said than done, but from the user perspective
> this could be a nice clean approach.

Drawbacks of that approach are mainly you would need to specify a lot
of attributes for newly written functions to get the behaviour you
want, and it also won't reduce the complexity of the language, rather
the contrary, the more settings and the more alternative ways of doing
things, the more complex will the implementation of the language
become. This is why postgresql is surperiour to for instance mysql, as
with mysql you need to set a lot of obscure settings to get the
desired behaviour, like making 2014-03-31 an error instead of allow
the invalid value. I wish plpgsql was just like postgresql, a very
strict language.

On 9/1/14 12:12 PM, Andres Freund wrote:
> On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote:
>> On 9/1/14 11:53 AM, Hannu Krosing wrote:
>>>> You're going to have to find a more gradual way of doing this.
>>> Probably a better way (and there has been some talk of it) is
>>> having some kind of PRAGMA functionality, or pl/pgsql specific
>>> LOCAL SET to affect "just this function" and not spill to nested
>>> functions as is the case for SETs now.
>>
>> I can't imagine how that would work for anyone who has thousands of
>> functions.
>
> How's that fundamentally different from changing languages? If we had a
> way to *add* such attributes to *existing* functions I don't see the
> fundamental problem?

Adding 5-10 of these for every function you create seems significantly
more painful than saying "this function uses plpgsql2". Though perhaps
what's being suggested is a *single* option which changes everything at
once? Then there wouldn't be a huge difference.

>> I've tried my best over the past ~year or so, but any attempts at breaking
>> backwards compatibility have been rejected. I really don't see any gradual
>> way of doing this. We either break things, live with what we have right
>> now, or create a new language.
>
> I think to some degree that was also influenced by the approach you
> took. Several of the changes didn't really have a meaningful explanation
> why they'd be helpful in the field. I.e. the change was explained, but
> not the reasoning *leading* to the change and which other solutions you
> thought about.

Yes, I agree I didn't always do a terrific job (see: EXIT USING
ROLLBACK), but some of them have been outright rejected even though
people clearly saw the value (I would put ASSERT into that category, and
the change to SELECT .. INTO obviously belongs here).

.marko

On 09/01/2014 05:24 PM, Andres Freund wrote:
> Look at the *disaster* the few minor changes in python3 were. It's now,
> years after, only starting to get used again.

While that's valid, I'd like to point out that Python2 and Python3 don't
share a runtime and can't easily use each others' modules and functions.

The difference with any "plpgsql2" is that it'd live within the same
runtime - PostgreSQL - as plpgsql and the other languages, and would
share the same interfaces.

Python2 -> Python3 would've been a lot less painful if you could mark,
on a module-by-module basis, whether a module was python2 or python3
code. It wasn't very practical for Python because python code can reach
deep into the guts of unrelated objects discovered at runtime - it can
add/replace member functions, even hot-patch bytecode. That's not
something we allow in PL/PgSQL, though; from the outside a PL/PgSQL
function is pretty opaque to callers.

That's not to say I'm keen on a "plpgsql2" unless there's no other way;
I'd be rather happier with language version pragmas or similar. But I
don't think it's analogous to Python2 vs Python3.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2014-09-01 12:49:22 +0200, Marko Tiikkaja wrote:
> On 9/1/14 12:12 PM, Andres Freund wrote:
> >On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote:
> >>On 9/1/14 11:53 AM, Hannu Krosing wrote:
> >>>>You're going to have to find a more gradual way of doing this.
> >>>Probably a better way (and there has been some talk of it) is
> >>>having some kind of PRAGMA functionality, or pl/pgsql specific
> >>>LOCAL SET to affect "just this function" and not spill to nested
> >>>functions as is the case for SETs now.
> >>
> >>I can't imagine how that would work for anyone who has thousands of
> >>functions.
> >
> >How's that fundamentally different from changing languages? If we had a
> >way to *add* such attributes to *existing* functions I don't see the
> >fundamental problem?
>
> Adding 5-10 of these for every function you create seems significantly more
> painful than saying "this function uses plpgsql2". Though perhaps what's
> being suggested is a *single* option which changes everything at once? Then
> there wouldn't be a huge difference.

The likelihood of us now knowing all the things that we want to break
rigth now seems about zero. There *will* be further ones. If we go with
the approach of creating new language versions for all of them we'll end
up with a completely unmaintainable mess. For PG devs, application dev
and DBAs.

Since what you seemingly want - sensibly so imo - is to set the default
errors for *new* functions, but leave the old set of errors for
preexisting ones, I suggest adding a GUC that defines the set of
warnings/errors *new* functions get. There'd need to be some syntax to
opt out for pg_dump and similar, but that sounds unproblematic.

One question here imo is whether we design something for plpgsql or more
generic...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi

2014-09-01 11:04 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> Hi,
>
> For those of you who use PL/pgSQL every day, I'm quite certain you all
> feel there are a number of things you would like to change in the language,
> but realize it cannot be achieved without possibly breaking compatibility,
> at least in theory. Even though you own code would survive the change,
> there might be code somewhere in the world which would break. This is of
> course not acceptable and that's why we have the current status quo of
> development, or at least not far away from a status quo.
>
> So instead of continue to adding optional settings to the config file,
> and instead of killing discussions around what can be done by bringing up
> the backwards-compatibility argument, let's instead fork the language and
> call it plpgsql2. Since no code is yet written in plpgsql2, we can start of
> from a clean sheet, and no good ideas need to be killed due to
> backwards-compatibility concerns.
>
> The interest for such a project is probably limited to a small number of
> companies/people around the world, as most users are probably perfectly
> happy with the current version of plpgsql, as they only use it
> occasionally and not every day like we do at my company.
>
> Just like with plpgsql, once released, plpgsql2 cannot break
> compatibility with future versions, so we only have one chance to carefully
> think though what we would like to change in the language.
>
> From the top of my head, these are Things I personally would want to see
> in plpgsql2:
> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
> row, as that's the most common use-case, and provide alternative syntax to
> modify multiple or zero rows.
> + Make SELECT .. INTO .. throw an error if it selects more than 1 row.
> INTO STRICT only works if no rows should be an error, but there is
> currently no nice way if no rows OR exactly 1 row should be found by the
> query.
> + Change all warnings into errors
>
> These are small changes, probably possible with just a few hundred lines
> of code in total, which also should be the ambition, as larger changes
> would never survive during time as it would require too much efforts to
> keep up with the main project. Secondly, I trust plpgsql mainly because
> it's being used by a lot of people in a lot of production systems, the same
> would not hold true for plpgsql2 for the first years of existence, so we
> who would use it in production systems must understand every single line of
> code changed and feel the risk of possible bugs and their impact are within
> acceptable boundaries.
>
> I can probably think of a few more things, but these are the major
> annoyances.
>
> Please share your wish list of things you would want in plpgsql2 which are
> not possible to implement in plpgsql because they could possibly break
> compatibility.
>
>
I agree with Andres - it is not a good for plpgsql and for plpgsql users.
The benefit must be significant for 90% of users.

Almost all from your mentioned issue can be solved by some extensions with
some new hooks. I don't agree, so UPDATE/INSERT/DELETE should to work only
with one row.

What I dislike on plpgsql:

* manipulation with expressions
* supply lot of SPI API in plpgsql
* inconsistent internal casting to target / returned values
* missing internal API for more stricter / smarted validation
* strange week implementation of left part of assign statement
* sometimes strange work with composite types
* late IO casting

on second hand it is fast practical language.

Official implementation of plpgsql2 can be very wrong and dangerous signal
- so we should not to do.

My plan .. maybe too long

enhancing SPI to better expression support
new PL API for support variables stack and handling variables
new API with communication with gdb
============================================
implementation of SQL/PSM .. it is new language .. based on relative good
ANSI SQL specification without compatibility issues
reimplementation plpgsql based on new API .. it should to significantly
reduce size

otherwise plpgsql2 is wrong name .. with respect to your goals it should be
"stricter plpgsql"

Regards

Pavel

> Regards, Joel
>
>

On Mon, Sep 1, 2014 at 12:55 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> The likelihood of us now knowing all the things that we want to break
> rigth now seems about zero. There *will* be further ones. If we go with
> the approach of creating new language versions for all of them we'll end
> up with a completely unmaintainable mess. For PG devs, application dev
> and DBAs.

PL/pgSQL was added in 1998 (16 years ago).

Compared this with again Python:
1994 Python 1.0
2000 Python 2.0 (6 years later)
2008 Python 3.0 (8 years later)

Of course we don't know all the things we want to break in the *future*,
but there is a good chance all users of PL/pgSQL know what they want
to change *today*,
thanks to the 16 years of active development in the language.

In 16 years from now, maybe there is a need for PL/pgSQL 3, or maybe
not, who knows.

2014-09-01 13:30 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Mon, Sep 1, 2014 at 12:55 PM, Andres Freund <andres(at)2ndquadrant(dot)com>
> wrote:
> > The likelihood of us now knowing all the things that we want to break
> > rigth now seems about zero. There *will* be further ones. If we go with
> > the approach of creating new language versions for all of them we'll end
> > up with a completely unmaintainable mess. For PG devs, application dev
> > and DBAs.
>
> PL/pgSQL was added in 1998 (16 years ago).
>
> Compared this with again Python:
> 1994 Python 1.0
> 2000 Python 2.0 (6 years later)
> 2008 Python 3.0 (8 years later)
>
> Of course we don't know all the things we want to break in the *future*,
> but there is a good chance all users of PL/pgSQL know what they want
> to change *today*,
> thanks to the 16 years of active development in the language.
>
> In 16 years from now, maybe there is a need for PL/pgSQL 3, or maybe
> not, who knows.
>
>
For lot of people is Python3 big fail - and it can be much more dangerous
for Postgres than for much more larger Python community.

I don't see a necessity to do again. I have very good knowledge about users
in Czech, and probably only I know a limits of plpgsql.

I am thinking so some enhancing of plpgsql (extensions, extra errors, extra
warnings) is possible.

Regards

Pavel

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

On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> I agree with Andres - it is not a good for plpgsql and for plpgsql users.
> The benefit must be significant for 90% of users.
...
> Official implementation of plpgsql2 can be very wrong and dangerous signal -
> so we should not to do.

Do you argue the introduction of plpgsql2 would hurt the users of
plpgsql in some way? How?

If you have X% who continue to happily use plpgsql, and (100-X%) who
find they can use plpgsql2 in their project, for new functions or all
functions (for a new project), then you have made (100-X)% of the
users more happy, than they would be if they were forced to use
plpgsql and suffer from its problems.

It *would* be a problem if you had to choose between writing all
functions in their plpgsql or plpgsql2, but thanks to postgres support
for different pl-languages and mixing different languages in the same
project, I cannot see the problem.

> implementation of SQL/PSM .. it is new language .. based on relative good
> ANSI SQL specification without compatibility issues
> reimplementation plpgsql based on new API .. it should to significantly
> reduce size

A new language like SQL/PSM would be helpful for new projects,
but personally I have a huge code base written in plpgsql which
I would at some point want to port to plpgsql2, and the least time consuming
way of doing so would be to make sure most existing plpgsql-functions
require no modifications at all to work with plpgsql2.
A new language would mean I would have to rewrite all functions,
which is much worse than doing no or minor modifications to existing functions.

> otherwise plpgsql2 is wrong name .. with respect to your goals it should be
> "stricter plpgsql"

I think plpgsql2 is a perfect name for it, because it is a new version
of plpgsql,
based on all the empirical knowledge gained from the 16 years of
development in plpgsql.
And while most improvements fall in the "stricter" category, there are
probably other things
which we would want to change when having the possibility of breaking
compatibility.

I think the main difference in what is possible with plpgsql2 compared
to improvements of plpgsql,
boil down to not having to evaluate any proposed change against "could
this break compatibility in theory?"
but instead "will this most certainly break compatilibity for most users?".

Today, if a proposed code change in plpgsql would have an impact >0%,
the change is rejected.
With plpgsql2, maybe we could allow an impact of <X% of lines of code.

If greater than X%, users will think it's unrealistic to port all
their code from plpgsql to plpgsql2,
which might be a long-term realistic requirement for some users,
especially for the project,
as in Y years from now, maybe the development of plpgsql can be put to halt,
to avoid having to maintain both code bases, which *is* undoubtably an
increased workload for the project.

Also, all your work and effort with plpgsql_check_function() would be
a natural fit for plpgsql2,
the problems it detect should of course be errors by default in the language.

2014-09-01 14:27 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > I agree with Andres - it is not a good for plpgsql and for plpgsql users.
> > The benefit must be significant for 90% of users.
> ...
> > Official implementation of plpgsql2 can be very wrong and dangerous
> signal -
> > so we should not to do.
>
> Do you argue the introduction of plpgsql2 would hurt the users of
> plpgsql in some way? How?
>

yes, anybody who has thousands lines in plpgsql will be messy, when we
publish so there will be new not fully compatible plpgsql2.

>
> If you have X% who continue to happily use plpgsql, and (100-X%) who
> find they can use plpgsql2 in their project, for new functions or all
> functions (for a new project), then you have made (100-X)% of the
> users more happy, than they would be if they were forced to use
> plpgsql and suffer from its problems.
>

It bad signal to have two languages plpgsql and plpgsql2. Who will believe
to us so we will continue development of plpgsql?

>
> It *would* be a problem if you had to choose between writing all
> functions in their plpgsql or plpgsql2, but thanks to postgres support
> for different pl-languages and mixing different languages in the same
> project, I cannot see the problem.
>
> > implementation of SQL/PSM .. it is new language .. based on relative good
> > ANSI SQL specification without compatibility issues
> > reimplementation plpgsql based on new API .. it should to significantly
> > reduce size
>
> A new language like SQL/PSM would be helpful for new projects,
> but personally I have a huge code base written in plpgsql which
> I would at some point want to port to plpgsql2, and the least time
> consuming
> way of doing so would be to make sure most existing plpgsql-functions
> require no modifications at all to work with plpgsql2.
>

I understand - just I don't would to repeat a issues of Python3 or Perl6 or
..

I don't believe so people understand different casting rules in almost all
same language plpgsql and plpgsql2. So it is one reason why start from zero
with less know syntax.

More I don't feel a real request from users.

> A new language would mean I would have to rewrite all functions,
> which is much worse than doing no or minor modifications to existing
> functions.
>
> > otherwise plpgsql2 is wrong name .. with respect to your goals it should
> be
> > "stricter plpgsql"
>
> I think plpgsql2 is a perfect name for it, because it is a new version
> of plpgsql,
> based on all the empirical knowledge gained from the 16 years of
> development in plpgsql.
> And while most improvements fall in the "stricter" category, there are
> probably other things
> which we would want to change when having the possibility of breaking
> compatibility.
>

you can do it - but will be better as independent project.

There is big space for improvement in plpgsql - but almost all can be done
without some stronger incompatibility.

Or this incompatibility (or stronger restrictivity) can be introduced in
longer time window.

>
> I think the main difference in what is possible with plpgsql2 compared
> to improvements of plpgsql,
> boil down to not having to evaluate any proposed change against "could
> this break compatibility in theory?"
> but instead "will this most certainly break compatilibity for most users?".
>
> Today, if a proposed code change in plpgsql would have an impact >0%,
> the change is rejected.
>

because it was useless - it was not any new value.

> With plpgsql2, maybe we could allow an impact of <X% of lines of code.
>
> If greater than X%, users will think it's unrealistic to port all
> their code from plpgsql to plpgsql2,
> which might be a long-term realistic requirement for some users,
> especially for the project,
> as in Y years from now, maybe the development of plpgsql can be put to
> halt,
> to avoid having to maintain both code bases, which *is* undoubtably an
> increased workload for the project.
>
> Also, all your work and effort with plpgsql_check_function() would be
> a natural fit for plpgsql2,
> the problems it detect should of course be errors by default in the
> language.
>

plpgsql_check is necessary because we don't would to introduce strong
dependency between functions and database schema. It is 70% motivation.

Next 30% can be integrated to language well. And I believe if PL engine was
more friendly to extensions, it can be 80% less code.

Pavel

On 09/01/2014 12:55 PM, Andres Freund wrote:
> On 2014-09-01 12:49:22 +0200, Marko Tiikkaja wrote:
>> On 9/1/14 12:12 PM, Andres Freund wrote:
>>> On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote:
>>>> On 9/1/14 11:53 AM, Hannu Krosing wrote:
>>>>>> You're going to have to find a more gradual way of doing this.
>>>>> Probably a better way (and there has been some talk of it) is
>>>>> having some kind of PRAGMA functionality, or pl/pgsql specific
>>>>> LOCAL SET to affect "just this function" and not spill to nested
>>>>> functions as is the case for SETs now.
>>>> I can't imagine how that would work for anyone who has thousands of
>>>> functions.
>>> How's that fundamentally different from changing languages? If we had a
>>> way to *add* such attributes to *existing* functions I don't see the
>>> fundamental problem?
>> Adding 5-10 of these for every function you create seems significantly more
>> painful than saying "this function uses plpgsql2".
You could package up these 5-10 SET LOCAL options as a separate language
handler (called plpgsql2) which really does nothing more than set the
local options and call the base handler :)

this will still leave you with flexibility of adding/removing features for
single functions

For extra convenience you could even create a "CREATE META LANGUAGE ..."
option for defining such language handlers.

>> Though perhaps what's
>> being suggested is a *single* option which changes everything at once? Then
>> there wouldn't be a huge difference.
> The likelihood of us now knowing all the things that we want to break
> rigth now seems about zero. There *will* be further ones. If we go with
> the approach of creating new language versions for all of them we'll end
> up with a completely unmaintainable mess. For PG devs, application dev
> and DBAs.
>
> Since what you seemingly want - sensibly so imo - is to set the default
> errors for *new* functions, but leave the old set of errors for
> preexisting ones, I suggest adding a GUC that defines the set of
> warnings/errors *new* functions get. There'd need to be some syntax to
> opt out for pg_dump and similar, but that sounds unproblematic.
>
> One question here imo is whether we design something for plpgsql or more
> generic...
I for one would like to have a generic "SET LOCAL" feature so it
could also be used for pl/pythonu or pl/v8

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On 9/1/14 2:53 PM, Pavel Stehule wrote:
> 2014-09-01 14:27 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:
>
>> On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>> I agree with Andres - it is not a good for plpgsql and for plpgsql users.
>>> The benefit must be significant for 90% of users.
>> ...
>>> Official implementation of plpgsql2 can be very wrong and dangerous
>> signal -
>>> so we should not to do.
>>
>> Do you argue the introduction of plpgsql2 would hurt the users of
>> plpgsql in some way? How?
>>
>
> yes, anybody who has thousands lines in plpgsql will be messy, when we
> publish so there will be new not fully compatible plpgsql2.

That's a good thing. PL/PgSQL is broken in various subtle ways.

>>
>> If you have X% who continue to happily use plpgsql, and (100-X%) who
>> find they can use plpgsql2 in their project, for new functions or all
>> functions (for a new project), then you have made (100-X)% of the
>> users more happy, than they would be if they were forced to use
>> plpgsql and suffer from its problems.
>>
>
> It bad signal to have two languages plpgsql and plpgsql2. Who will believe
> to us so we will continue development of plpgsql?

I think what should happen is that we stop adding features to plpgsql.
We should design plpgsql2 in such a way that it's easier to add new
features to it in the future (to the extent that that's possible), and
then add the new stuff only to that one.

>> A new language like SQL/PSM would be helpful for new projects,
>> but personally I have a huge code base written in plpgsql which
>> I would at some point want to port to plpgsql2, and the least time
>> consuming
>> way of doing so would be to make sure most existing plpgsql-functions
>> require no modifications at all to work with plpgsql2.
>>
>
> I understand - just I don't would to repeat a issues of Python3 or Perl6 or
> ..
>
> I don't believe so people understand different casting rules in almost all
> same language plpgsql and plpgsql2. So it is one reason why start from zero
> with less know syntax.

I'm not convinced. Seems to me that it would be better in every way to
just fix the familiar syntax.

> More I don't feel a real request from users.

Yeah, that's the problem with subtle problems: only people who use the
language a lot and pay attention are going to notice them.

>> A new language would mean I would have to rewrite all functions,
>> which is much worse than doing no or minor modifications to existing
>> functions.
>>
>>> otherwise plpgsql2 is wrong name .. with respect to your goals it should
>> be
>>> "stricter plpgsql"
>>
>> I think plpgsql2 is a perfect name for it, because it is a new version
>> of plpgsql,
>> based on all the empirical knowledge gained from the 16 years of
>> development in plpgsql.
>> And while most improvements fall in the "stricter" category, there are
>> probably other things
>> which we would want to change when having the possibility of breaking
>> compatibility.
>>
>
> you can do it - but will be better as independent project.
>
> There is big space for improvement in plpgsql - but almost all can be done
> without some stronger incompatibility.

That's very very general and it would depend on the details, but I still
disagree in general.

> Or this incompatibility (or stronger restrictivity) can be introduced in
> longer time window.

I'd think that that would be worse for the current users of PL/PgSQL,
not better.

>> If greater than X%, users will think it's unrealistic to port all
>> their code from plpgsql to plpgsql2,
>> which might be a long-term realistic requirement for some users,
>> especially for the project,
>> as in Y years from now, maybe the development of plpgsql can be put to
>> halt,
>> to avoid having to maintain both code bases, which *is* undoubtably an
>> increased workload for the project.
>>
>> Also, all your work and effort with plpgsql_check_function() would be
>> a natural fit for plpgsql2,
>> the problems it detect should of course be errors by default in the
>> language.
>>
>
> plpgsql_check is necessary because we don't would to introduce strong
> dependency between functions and database schema. It is 70% motivation.
>
> Next 30% can be integrated to language well. And I believe if PL engine was
> more friendly to extensions, it can be 80% less code.

Yeah, PL/PgSQL is a bit hostile to to extensions like plpgsql_check.
But that doesn't mean that we have to bin everything we have and start
from scratch.

.marko

On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> It bad signal to have two languages plpgsql and plpgsql2. Who will believe
> to us so we will continue development of plpgsql?

Depends on how you define "development".
Bugfixes of plpgsql? Yes, of course.
New features? No, but that's a non-issue since we all know it's more
or less impossible to introduce new features without breaking
compatibility, I think you will agree on that, no?

>> A new language like SQL/PSM would be helpful for new projects,
>> but personally I have a huge code base written in plpgsql which
>> I would at some point want to port to plpgsql2, and the least time
>> consuming
>> way of doing so would be to make sure most existing plpgsql-functions
>> require no modifications at all to work with plpgsql2.
>
>
> I understand - just I don't would to repeat a issues of Python3 or Perl6 or

The fatal problems with Python3 and Perl6 was the inability to mix
code between Python2/3 and Perl5/6.
We don't have that problem with pl-languages in postgres, so please
don't make that comparison, as it's incorrect.

>> I think plpgsql2 is a perfect name for it, because it is a new version
>> of plpgsql,
>> based on all the empirical knowledge gained from the 16 years of
>> development in plpgsql.
>> And while most improvements fall in the "stricter" category, there are
>> probably other things
>> which we would want to change when having the possibility of breaking
>> compatibility.
>
>
>
> you can do it - but will be better as independent project.
>
> There is big space for improvement in plpgsql - but almost all can be done
> without some stronger incompatibility.
>
> Or this incompatibility (or stronger restrictivity) can be introduced in
> longer time window.

With "can be done" you have to take into account what kind of changes
the project accepts into the plpgsql-code
Looking back a few years of efforts from people (including yourself),
it looks like much of the energy and hours invested would have made a
much better pay-off in a new language.

On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > It bad signal to have two languages plpgsql and plpgsql2. Who will believe
> > to us so we will continue development of plpgsql?
>
> Depends on how you define "development".
> Bugfixes of plpgsql? Yes, of course.
> New features? No, but that's a non-issue since we all know it's more
> or less impossible to introduce new features without breaking
> compatibility, I think you will agree on that, no?

Sorry, but that's just plain wrong. There've been plenty of new features
for plpgsql. You're not very convincing if you use bogus arguments like
this.

EOD for me.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 05:04 PM, Joel Jacobson wrote:
> Just like with plpgsql, once released, plpgsql2 cannot break
> compatibility with future versions, so we only have one chance to
> carefully think though what we would like to change in the language.

You're not proposing to copy plpgsql's runtime though, right? Just add
conditional paths where v1 and v2 differ.

Personally I'd rather look at adding language version pragmas or a new
function attribute, but that's mostly bikeshedding.

> From the top of my head, these are Things I personally would want to see
> in plpgsql2:

- Accept RECORD input, dynamic access to fields of records without
resorting to hstore hacks. This is certainly my #1.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 05:04 PM, Joel Jacobson wrote:
> From the top of my head, these are Things I personally would want to see
> in plpgsql2:

Oh, also, I'd *love* to improve how non-plannable statements with
PL/PgSQL variable subsitutions behave.

*I* understand why the following is wrong:

DO
$$
DECLARE
tablename text;
BEGIN
tablename := 'sometable';
DROP TABLE tablename;
END;
$$;

and produces:

ERROR: table "tablename" does not exist
CONTEXT: SQL statement "DROP TABLE tablename"
PL/pgSQL function inline_code_block line 6 at SQL statement

but going by the number of questions I see about this, and similar
issues with ALTER USER and so on, it's not obvious to new users.

It's not clear how to make this friendly without making it dangerous
though. If you have:

DROP TABLE x;

then someone declares a variable

x := 'customers';

you don't want to suddenly be issuing a

DROP TABLE customers;

... which is why I suspect this might need to be not 100% backward
compatible, perhaps requiring a variable-marker prefix.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2014-09-01 15:12 GMT+02:00 Marko Tiikkaja <marko(at)joh(dot)to>:

> On 9/1/14 2:53 PM, Pavel Stehule wrote:
>
>> 2014-09-01 14:27 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:
>>
>> On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>> wrote:
>>>
>>>> I agree with Andres - it is not a good for plpgsql and for plpgsql
>>>> users.
>>>> The benefit must be significant for 90% of users.
>>>>
>>> ...
>>>
>>>> Official implementation of plpgsql2 can be very wrong and dangerous
>>>>
>>> signal -
>>>
>>>> so we should not to do.
>>>>
>>>
>>> Do you argue the introduction of plpgsql2 would hurt the users of
>>> plpgsql in some way? How?
>>>
>>>
>> yes, anybody who has thousands lines in plpgsql will be messy, when we
>> publish so there will be new not fully compatible plpgsql2.
>>
>
> That's a good thing. PL/PgSQL is broken in various subtle ways.
>
>
>
>>> If you have X% who continue to happily use plpgsql, and (100-X%) who
>>> find they can use plpgsql2 in their project, for new functions or all
>>> functions (for a new project), then you have made (100-X)% of the
>>> users more happy, than they would be if they were forced to use
>>> plpgsql and suffer from its problems.
>>>
>>>
>> It bad signal to have two languages plpgsql and plpgsql2. Who will believe
>> to us so we will continue development of plpgsql?
>>
>
> I think what should happen is that we stop adding features to plpgsql. We
> should design plpgsql2 in such a way that it's easier to add new features
> to it in the future (to the extent that that's possible), and then add the
> new stuff only to that one.
>
>
> A new language like SQL/PSM would be helpful for new projects,
>>> but personally I have a huge code base written in plpgsql which
>>> I would at some point want to port to plpgsql2, and the least time
>>> consuming
>>> way of doing so would be to make sure most existing plpgsql-functions
>>> require no modifications at all to work with plpgsql2.
>>>
>>>
>> I understand - just I don't would to repeat a issues of Python3 or Perl6
>> or
>> ..
>>
>> I don't believe so people understand different casting rules in almost all
>> same language plpgsql and plpgsql2. So it is one reason why start from
>> zero
>> with less know syntax.
>>
>
> I'm not convinced. Seems to me that it would be better in every way to
> just fix the familiar syntax.
>
>
> More I don't feel a real request from users.
>>
>
> Yeah, that's the problem with subtle problems: only people who use the
> language a lot and pay attention are going to notice them.
>
>
> A new language would mean I would have to rewrite all functions,
>>> which is much worse than doing no or minor modifications to existing
>>> functions.
>>>
>>> otherwise plpgsql2 is wrong name .. with respect to your goals it should
>>>>
>>> be
>>>
>>>> "stricter plpgsql"
>>>>
>>>
>>> I think plpgsql2 is a perfect name for it, because it is a new version
>>> of plpgsql,
>>> based on all the empirical knowledge gained from the 16 years of
>>> development in plpgsql.
>>> And while most improvements fall in the "stricter" category, there are
>>> probably other things
>>> which we would want to change when having the possibility of breaking
>>> compatibility.
>>>
>>>
>> you can do it - but will be better as independent project.
>>
>> There is big space for improvement in plpgsql - but almost all can be
>> done
>> without some stronger incompatibility.
>>
>
> That's very very general and it would depend on the details, but I still
> disagree in general.

>
> Or this incompatibility (or stronger restrictivity) can be introduced in
>> longer time window.
>>
>
> I'd think that that would be worse for the current users of PL/PgSQL, not
> better.

I am sorry. Users around me are allergic on any +X language, so I am
careful.

I understand to you, understand to your motivation, but I disagree with
your proposal.

We can talk about possibility to design a extensions, what you need

and we can redesign plpgsql engine to allow to different setup for any
specific usage (with extensions, some config).

But still I would to respect some relation to PL/SQL and ADA (not necessary
compatibility).

Regards

Pavel

>
>
> If greater than X%, users will think it's unrealistic to port all
>>> their code from plpgsql to plpgsql2,
>>> which might be a long-term realistic requirement for some users,
>>> especially for the project,
>>> as in Y years from now, maybe the development of plpgsql can be put to
>>> halt,
>>> to avoid having to maintain both code bases, which *is* undoubtably an
>>> increased workload for the project.
>>>
>>> Also, all your work and effort with plpgsql_check_function() would be
>>> a natural fit for plpgsql2,
>>> the problems it detect should of course be errors by default in the
>>> language.
>>>
>>>
>> plpgsql_check is necessary because we don't would to introduce strong
>> dependency between functions and database schema. It is 70% motivation.
>>
>> Next 30% can be integrated to language well. And I believe if PL engine
>> was
>> more friendly to extensions, it can be 80% less code.
>>
>
> Yeah, PL/PgSQL is a bit hostile to to extensions like plpgsql_check. But
> that doesn't mean that we have to bin everything we have and start from
> scratch.
>
>
> .marko
>

2014-09-01 15:52 GMT+02:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> On 09/01/2014 05:04 PM, Joel Jacobson wrote:
> > From the top of my head, these are Things I personally would want to see
> > in plpgsql2:
>
> Oh, also, I'd *love* to improve how non-plannable statements with
> PL/PgSQL variable subsitutions behave.
>
> *I* understand why the following is wrong:
>
> DO
> $$
> DECLARE
> tablename text;
> BEGIN
> tablename := 'sometable';
> DROP TABLE tablename;
> END;
> $$;
>
> and produces:
>
> ERROR: table "tablename" does not exist
> CONTEXT: SQL statement "DROP TABLE tablename"
> PL/pgSQL function inline_code_block line 6 at SQL statement
>
>
> but going by the number of questions I see about this, and similar
> issues with ALTER USER and so on, it's not obvious to new users.
>
> It's not clear how to make this friendly without making it dangerous
> though. If you have:
>
> DROP TABLE x;
>
> then someone declares a variable
>
> x := 'customers';
>
> you don't want to suddenly be issuing a
>
> DROP TABLE customers;
>
> ... which is why I suspect this might need to be not 100% backward
> compatible, perhaps requiring a variable-marker prefix.
>

It is in ToDo - allow parametrization for COMMANDs.

But this is one point, when I am not sure if we would it. Now - situation
is very simply. Variables should not be used as table or column name. With
your proposal, the situation will by much more complex, and probably
variables should be used in SELECT * FROM x; but it can be ambiguous SELECT
x FROM table ... so maybe better to don't allow it

Regards

Pavel

>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 09/01/2014 09:58 PM, Pavel Stehule wrote:
>
> It is in ToDo - allow parametrization for COMMANDs.
>
> But this is one point, when I am not sure if we would it. Now -
> situation is very simply. Variables should not be used as table or
> column name. With your proposal, the situation will by much more
> complex, and probably variables should be used in SELECT * FROM x; but
> it can be ambiguous SELECT x FROM table ... so maybe better to don't
> allow it

We would have to define a specific parameter marker, as used by things
like JDBC.

SELECT x FROM ?tablename

DROP TABLE ?tablename

or whatever your favourite place-holder syntax is.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2014-09-01 16:01 GMT+02:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> On 09/01/2014 09:58 PM, Pavel Stehule wrote:
> >
> > It is in ToDo - allow parametrization for COMMANDs.
> >
> > But this is one point, when I am not sure if we would it. Now -
> > situation is very simply. Variables should not be used as table or
> > column name. With your proposal, the situation will by much more
> > complex, and probably variables should be used in SELECT * FROM x; but
> > it can be ambiguous SELECT x FROM table ... so maybe better to don't
> > allow it
>
> We would have to define a specific parameter marker, as used by things
> like JDBC.
>
> SELECT x FROM ?tablename
>
> DROP TABLE ?tablename
>
> or whatever your favourite place-holder syntax is.
>

It can be solution, but I dislike it .. It increase a language complexity
.. vars with or without prefix .. and more, hidden dynamic SQL

Nothing what I like - I have a mental barrier to this concept.

Pavel

>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

On 2014-09-01 22:01:33 +0800, Craig Ringer wrote:
> On 09/01/2014 09:58 PM, Pavel Stehule wrote:
> >
> > It is in ToDo - allow parametrization for COMMANDs.
> >
> > But this is one point, when I am not sure if we would it. Now -
> > situation is very simply. Variables should not be used as table or
> > column name. With your proposal, the situation will by much more
> > complex, and probably variables should be used in SELECT * FROM x; but
> > it can be ambiguous SELECT x FROM table ... so maybe better to don't
> > allow it
>
> We would have to define a specific parameter marker, as used by things
> like JDBC.
>
> SELECT x FROM ?tablename
>
> DROP TABLE ?tablename
>
> or whatever your favourite place-holder syntax is.

Imo this is still something that's more dynamic SQL (i.e. EXECUTE's
remit) than something that shouldn't be doable implicitly. So perhaps
the solution is to extend EXECUTE to allow specifying tablenames as
variables more conveniently?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 10:11 PM, Pavel Stehule wrote:
>
> It can be solution, but I dislike it .. It increase a language
> complexity .. vars with or without prefix .. and more, hidden dynamic SQL
>
> Nothing what I like - I have a mental barrier to this concept.

Yeah - the question is whether it's better than using EXECUTE.

I'm not convinced it is, since it doesn't make things any more
discoverable for new users, and existing users already know how to do it
right.

It'd only really improve things if PL/PgSQL had started off using
$variable notation, or something that wasn't otherwise legal as an
identifier.

I don't love how it works now, but I don't have a better answer really.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 10:17 PM, Andres Freund wrote:
> Imo this is still something that's more dynamic SQL (i.e. EXECUTE's
> remit) than something that shouldn't be doable implicitly. So perhaps
> the solution is to extend EXECUTE to allow specifying tablenames as
> variables more conveniently?

With format(...) it's pretty easy, really.

Perhaps just changing the docs to remove all the quote_ident based
examples in favour of format(...) would be enough.

Pavel's points are pretty good - what exists now isn't ideal from a
usability and friendliness PoV, but changing it would require making a
big mess elsewhere that's not worth doing.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2014-09-01 16:18 GMT+02:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> On 09/01/2014 10:11 PM, Pavel Stehule wrote:
> >
> > It can be solution, but I dislike it .. It increase a language
> > complexity .. vars with or without prefix .. and more, hidden dynamic
> SQL
> >
> > Nothing what I like - I have a mental barrier to this concept.
>
> Yeah - the question is whether it's better than using EXECUTE.
>
> I'm not convinced it is, since it doesn't make things any more
> discoverable for new users, and existing users already know how to do it
> right.
>
> It'd only really improve things if PL/PgSQL had started off using
> $variable notation, or something that wasn't otherwise legal as an
> identifier.
>

yes .. dynamic SQL is terrible

but it is good to understand to this concept quickly - because it is core
of integration SQL to PL/pgSQL.

>
> I don't love how it works now, but I don't have a better answer really.
>

I have same opinion. It is not ideal now, but I don't any better ideal

>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

On 2014-09-01 22:20:37 +0800, Craig Ringer wrote:
> On 09/01/2014 10:17 PM, Andres Freund wrote:
> > Imo this is still something that's more dynamic SQL (i.e. EXECUTE's
> > remit) than something that shouldn't be doable implicitly. So perhaps
> > the solution is to extend EXECUTE to allow specifying tablenames as
> > variables more conveniently?
>
> With format(...) it's pretty easy, really.

I know of format(), but it doesn't allow you to pass parameters as
actual query variables unfortunately.
I'm wondering if there's a way to marry USING and format()...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/01/2014 10:24 PM, Andres Freund wrote:
> I know of format(), but it doesn't allow you to pass parameters as
> actual query variables unfortunately.
> I'm wondering if there's a way to marry USING and format()...

Well, the idiom:

EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;

is not lovely. It works, but it's clumsy.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Sep 1, 2014 at 3:25 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
>> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> > It bad signal to have two languages plpgsql and plpgsql2. Who will believe
>> > to us so we will continue development of plpgsql?
>>
>> Depends on how you define "development".
>> Bugfixes of plpgsql? Yes, of course.
>> New features? No, but that's a non-issue since we all know it's more
>> or less impossible to introduce new features without breaking
>> compatibility, I think you will agree on that, no?
>
> Sorry, but that's just plain wrong. There've been plenty of new features
> for plpgsql. You're not very convincing if you use bogus arguments like
> this.

You misunderstood, what I said was it's "more or less impossible",
that's different from "impossible".
*If* a feature can be added to plpgsql, it sure can be added to
plpgsql2 too, so of course it should be added to both.
I'm just saying it's much less probable you can add new features to
plpgsql than to plpgsql2, as you have to take into account the risk of
breaking compatibility.

+1

I use underscore for *all* variables and input parameters in all
functions. Making that a requirement in plpgsql2 wouldn't break any of
my code.

On Mon, Sep 1, 2014 at 3:52 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 09/01/2014 05:04 PM, Joel Jacobson wrote:
>> From the top of my head, these are Things I personally would want to see
>> in plpgsql2:
>
> Oh, also, I'd *love* to improve how non-plannable statements with
> PL/PgSQL variable subsitutions behave.
>
> *I* understand why the following is wrong:
>
> DO
> $$
> DECLARE
> tablename text;
> BEGIN
> tablename := 'sometable';
> DROP TABLE tablename;
> END;
> $$;
>
> and produces:
>
> ERROR: table "tablename" does not exist
> CONTEXT: SQL statement "DROP TABLE tablename"
> PL/pgSQL function inline_code_block line 6 at SQL statement
>
>
> but going by the number of questions I see about this, and similar
> issues with ALTER USER and so on, it's not obvious to new users.
>
> It's not clear how to make this friendly without making it dangerous
> though. If you have:
>
> DROP TABLE x;
>
> then someone declares a variable
>
> x := 'customers';
>
> you don't want to suddenly be issuing a
>
> DROP TABLE customers;
>
> ... which is why I suspect this might need to be not 100% backward
> compatible, perhaps requiring a variable-marker prefix.
>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

2014-09-01 16:26 GMT+02:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> On 09/01/2014 10:24 PM, Andres Freund wrote:
> > I know of format(), but it doesn't allow you to pass parameters as
> > actual query variables unfortunately.
> > I'm wondering if there's a way to marry USING and format()...
>
> Well, the idiom:
>
> EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>
> is not lovely. It works, but it's clumsy.
>

It is not intuitive, but It is well descriptive about PL/pgSQL. I cannot to
imagine some different - it is mix of two independent worlds. And at end it
is elegant.

If we start new language from scratch, then we don't need to introduce
concept of embedded SQL or dynamic SQL.

But why? If it is too clumsy for you, use PLPerl or PLPython. Everytime
will be problem how to merge two different namespaces together.

>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

On 09/01/2014 03:45 PM, Craig Ringer wrote:
> On 09/01/2014 05:04 PM, Joel Jacobson wrote:
>> Just like with plpgsql, once released, plpgsql2 cannot break
>> compatibility with future versions, so we only have one chance to
>> carefully think though what we would like to change in the language.
> You're not proposing to copy plpgsql's runtime though, right? Just add
> conditional paths where v1 and v2 differ.
>
> Personally I'd rather look at adding language version pragmas or a new
> function attribute, but that's mostly bikeshedding.
>
>> From the top of my head, these are Things I personally would want to see
>> in plpgsql2:
> - Accept RECORD input, dynamic access to fields of records without
> resorting to hstore hacks. This is certainly my #1.
>
Also, an easy way to tell pl/pgsql to *not* cache plans without
resorting to EXECUT'ins trings would nice

Cheers
Hannu

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On 2014-09-01 16:29:18 +0200, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 3:25 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
> >> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >> > It bad signal to have two languages plpgsql and plpgsql2. Who will believe
> >> > to us so we will continue development of plpgsql?
> >>
> >> Depends on how you define "development".
> >> Bugfixes of plpgsql? Yes, of course.
> >> New features? No, but that's a non-issue since we all know it's more
> >> or less impossible to introduce new features without breaking
> >> compatibility, I think you will agree on that, no?
> >
> > Sorry, but that's just plain wrong. There've been plenty of new features
> > for plpgsql. You're not very convincing if you use bogus arguments like
> > this.
>
> You misunderstood, what I said was it's "more or less impossible",
> that's different from "impossible".

It's still bullshit. It's very hard to change *existing* semantics and
thus existing code. Which isn't something plpgsql specifically has
difficulties with. It's a far more general concern affecting pretty much
all released software; more so software with a large existing user
base. Remember all the flak postgres got for the cast issues with 8.3?

It's about has hard to add additional features, that don't break
existing code, to plpgsql as to most of the rest of postgres. Which
isn't to say it's easy. Believe me, I know that.

> I'm just saying it's much less probable you can add new features to
> plpgsql than to plpgsql2, as you have to take into account the risk of
> breaking compatibility.

That's just a difference of one release. The release after the set of
problems is nearly identical.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> Well, the idiom:
>
> EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>
> is not lovely. It works, but it's clumsy.

This is exactly why we need a new language.
All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
plpgsql2, with the most beautiful syntax we can come up with.

I guess it's a question if we want to support things like this. If we
want to, then we also want a new language.

On 09/01/2014 05:41 PM, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> Well, the idiom:
>>
>> EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>>
>> is not lovely. It works, but it's clumsy.
>
> This is exactly why we need a new language.

We could certainly improve that syntax in PL/pgSQL. No need to start
from scratch for that..

Got a suggestion what the syntax should look like?

- Heikki

2014-09-01 16:39 GMT+02:00 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:

> On 09/01/2014 03:45 PM, Craig Ringer wrote:
> > On 09/01/2014 05:04 PM, Joel Jacobson wrote:
> >> Just like with plpgsql, once released, plpgsql2 cannot break
> >> compatibility with future versions, so we only have one chance to
> >> carefully think though what we would like to change in the language.
> > You're not proposing to copy plpgsql's runtime though, right? Just add
> > conditional paths where v1 and v2 differ.
> >
> > Personally I'd rather look at adding language version pragmas or a new
> > function attribute, but that's mostly bikeshedding.
> >
> >> From the top of my head, these are Things I personally would want to see
> >> in plpgsql2:
> > - Accept RECORD input, dynamic access to fields of records without
> > resorting to hstore hacks. This is certainly my #1.
> >
> Also, an easy way to tell pl/pgsql to *not* cache plans without
> resorting to EXECUT'ins trings would nice
>

It is good request

maybe

#option noplancache

or

SELECT /* NOPLANCACHE */

Pavel

>
> Cheers
> Hannu
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

2014-09-01 16:41 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer <craig(at)2ndquadrant(dot)com>
> wrote:
> > Well, the idiom:
> >
> > EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
> >
> > is not lovely. It works, but it's clumsy.
>
> This is exactly why we need a new language.
> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
> plpgsql2, with the most beautiful syntax we can come up with.
>
> I guess it's a question if we want to support things like this. If we
> want to, then we also want a new language.
>

when you fix it, then you designed totally new language with thin relation
to plpgsql

Pavel

On Mon, Sep 1, 2014 at 4:41 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> I'm just saying it's much less probable you can add new features to
>> plpgsql than to plpgsql2, as you have to take into account the risk of
>> breaking compatibility.
>
> That's just a difference of one release. The release after the set of
> problems is nearly identical.

That's not true. The first release (plpgsql -> plpgsql2) will be a
major release.
After that, we can do minor releases for the following X years, until
we possible need for a new major version.
Each minor release would be guaranteed not to break any backwards compatibility.

plpgsql -> plpgsql2 would be the single giant leap we take into the future.

I think this reasoning is quite compatible with the versioning policy
of the project in general, where we distinguish between major and
minor releases.

On 09/01/2014 10:41 PM, Joel Jacobson wrote:
> This is exactly why we need a new language.
> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
> plpgsql2, with the most beautiful syntax we can come up with.
>
> I guess it's a question if we want to support things like this. If we
> want to, then we also want a new language.

Given how much bike shedding occurs around trivial features, can you
imagine how long that'd take?

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 09/01/2014 10:41 PM, Joel Jacobson wrote:
>> This is exactly why we need a new language.
>> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
>> plpgsql2, with the most beautiful syntax we can come up with.
>>
>> I guess it's a question if we want to support things like this. If we
>> want to, then we also want a new language.
>
> Given how much bike shedding occurs around trivial features, can you
> imagine how long that'd take?

I wasn't aware of the expression "bike shedding" so I had to look it up.
It apparently means "spend the majority of its time on relatively
unimportant but easy-to-grasp issues".
If you feel the development of plpgsql falls into this category, that
most time is spent on the smaller unimportant things, isn't that a
clear sign we need plpgsql2, for there to be any hope of progress on
the important things?

Joel Jacobson-2 wrote
> On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer &lt;

> craig@

> &gt; wrote:
>> Well, the idiom:
>>
>> EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>>
>> is not lovely. It works, but it's clumsy.
>
> This is exactly why we need a new language.
> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
> plpgsql2, with the most beautiful syntax we can come up with.
>
> I guess it's a question if we want to support things like this. If we
> want to, then we also want a new language.

Regardless of how they are ultimately implemented - a new language, PRAGMA
infrastructure, or adding syntax to plpgsql - improving upon the current
6-item ToDo on the Wiki would be a lot more valuable at this point in time.
I do think better organization than the current ToDo format can be had but
even just moving many of these ideas there would be a start.

The goal isn't to introduce a new language version - that would simply be a
means to achieving an end that is not reasonably achievable in any other
way.

Ideally, for each feature and behavior, we'd be able to provide suggestions
on how it could be implemented in the various cases and the pros/cons of
doing so in each. If there are enough items with beautiful syntax in
version 2 of the language then that concrete evidence and action plan would
have a much better chance of gaining commiter support than generalities and
theories.

Adding a whole new language to avoid 2 pet-peeves and an introduce a
behavior (syntax to allow multi-row DDL) that is controversial is not likely
to seem worth it to those you are asking to write and maintain this new
language. I get the goal of the post was to generate ideas but at the same
time the idea generation can occur without you putting forth the conclusion
that a new language is required - conclusions like that are not useful at
the start of a research project (even if that conclusion motivated the
project in the first place).

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817199.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
>> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> It bad signal to have two languages plpgsql and plpgsql2. Who will believe
>>> to us so we will continue development of plpgsql?

>> Depends on how you define "development".
>> Bugfixes of plpgsql? Yes, of course.
>> New features? No, but that's a non-issue since we all know it's more
>> or less impossible to introduce new features without breaking
>> compatibility, I think you will agree on that, no?

> Sorry, but that's just plain wrong. There've been plenty of new features
> for plpgsql. You're not very convincing if you use bogus arguments like
> this.

And even more to the point: once plpgsql2 is released, the *exact same*
compatibility arguments will limit further development of it.

Perhaps, if you were very smart and designed a language from scratch
without worrying about whether it looked anything like plpgsql, you
could come up with something that would be easier to extend without
creating compatibility issues. But that's not what's being proposed here.

What is actually being proposed, AFAICS, is a one-shot fix for a bunch
of unfortunate choices. That might be worth doing, but let's not fool
ourselves about whether it's one-shot or not.

regards, tom lane

On Mon, Sep 1, 2014 at 5:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> What is actually being proposed, AFAICS, is a one-shot fix for a bunch
> of unfortunate choices. That might be worth doing, but let's not fool
> ourselves about whether it's one-shot or not.

I'm glad to hear you think it *might* be worth doing.
A one-shot is exactly what it is, like a new major version of postgres
itself (but a new major version of postgres has a much longer release
note of changes :).
Once released, there is obviously no way to include new non-backwards
compatible code in future minor versions.

I guess it boils down to if the project can agree on if there are any
significant *important* changes worth doing that are *not* possible or
feasible to implement in plpgsql.

I see two possible approaches of a plpgsql2 project, both aiming to
require minimal/no changes of most existing best-practice plpgsql
code:
a) fork plpgsql code base and implement changes with as few lines of
code as possible, making it easier to understand the changes, verify
their correctness and apply future patches of the plpgsql code.
b) fork plpgsql code and remove as much code as possible thanks to the
reduced complexity possible thanks to the stricter behaviour achieved
by removing settings and enforcing a stricter coding convention and
killing obsolete quirks.

Given plpgsql2 is a one-shot, the time window to gather input of what
non-compatible changes to include probably needs to be at least a
year.
During that period, the mostly-compatible changes discussed could be
implemented, which are the ones I'm personally most interested in
anyway, but if we are creating a new language, then naturally we
should take the chance to include all important changes we wish we
could do but cannot with plpgsql.

On 01/09/14 14:27, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> I agree with Andres - it is not a good for plpgsql and for plpgsql users.
>> The benefit must be significant for 90% of users.
> ...
>> Official implementation of plpgsql2 can be very wrong and dangerous signal -
>> so we should not to do.
> Do you argue the introduction of plpgsql2 would hurt the users of
> plpgsql in some way? How?
>
> If you have X% who continue to happily use plpgsql, and (100-X%) who
> find they can use plpgsql2 in their project, for new functions or all
> functions (for a new project), then you have made (100-X)% of the
> users more happy, than they would be if they were forced to use
> plpgsql and suffer from its problems.
>
> It *would* be a problem if you had to choose between writing all
> functions in their plpgsql or plpgsql2, but thanks to postgres support
> for different pl-languages and mixing different languages in the same
> project, I cannot see the problem.

What it's clear from my "non-hacker, casual hackers ml reader"
opinion here, is that there is room for new language features or a new
in-core language at once. I find Joel's reasoning quite clear about the
general concepts of improving on plpgsql, although the precise changes
may not be big enough to justify just a new version. But if there are
enough changes, and breaking compatibility with the current plpgsql is a
major concern, I fail to buy other arguments of why doing plpgsql2 is a
bad thing. The comparisons with Python/Perl are very misleading, as they
have nothing to do with Postgres, and the case is obviously different.

What I can add is that, if Postgres is to devote resources to a new
language, I would plan it with a broader scope. What would attract most
users? Would it bring non postgres users to Postgres? What could be one
of the killer features of any next version? My trivial answer to most of
these questions is: PL/SQL. I don't know with detail how complex this is
to get in Postgres (well, EDB probably knows), but if I had to chose a
new language, this is it. So my questions would rather be:

- Is it feasible (resources, time, interest) to implement PL/SQL in
Postgres?
- Does it support all the requested new features Joel and others
mentioned in this thread as desires for the new language?
- If the answer to the previous question is no, could those unsupported
features be implemented as a compatible superset of PL/SQL?

Sorry if this sounds too unconventional for this list, but this is
what IMVHO many users would be more pleased with.

My 2 cents,

Álvaro

2014-09-01 20:23 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Mon, Sep 1, 2014 at 5:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > What is actually being proposed, AFAICS, is a one-shot fix for a bunch
> > of unfortunate choices. That might be worth doing, but let's not fool
> > ourselves about whether it's one-shot or not.
>
> I'm glad to hear you think it *might* be worth doing.
> A one-shot is exactly what it is, like a new major version of postgres
> itself (but a new major version of postgres has a much longer release
> note of changes :).
> Once released, there is obviously no way to include new non-backwards
> compatible code in future minor versions.
>
> I guess it boils down to if the project can agree on if there are any
> significant *important* changes worth doing that are *not* possible or
> feasible to implement in plpgsql.
>
> I see two possible approaches of a plpgsql2 project, both aiming to
> require minimal/no changes of most existing best-practice plpgsql
> code:
> a) fork plpgsql code base and implement changes with as few lines of
> code as possible, making it easier to understand the changes, verify
> their correctness and apply future patches of the plpgsql code.
> b) fork plpgsql code and remove as much code as possible thanks to the
> reduced complexity possible thanks to the stricter behaviour achieved
> by removing settings and enforcing a stricter coding convention and
> killing obsolete quirks.
>
>
I don't like a idea so we will have plpgsql 2x

without significant redesign you don't throw too much lines. If you really
need to design new language, then redesign engine first.

> Given plpgsql2 is a one-shot, the time window to gather input of what
> non-compatible changes to include probably needs to be at least a
> year.
> During that period, the mostly-compatible changes discussed could be
> implemented, which are the ones I'm personally most interested in
> anyway, but if we are creating a new language, then naturally we
> should take the chance to include all important changes we wish we
> could do but cannot with plpgsql.
>

Joel Jacobson <joel(at)trustly(dot)com> writes:
> I see two possible approaches of a plpgsql2 project, both aiming to
> require minimal/no changes of most existing best-practice plpgsql
> code:
> a) fork plpgsql code base and implement changes with as few lines of
> code as possible, making it easier to understand the changes, verify
> their correctness and apply future patches of the plpgsql code.
> b) fork plpgsql code and remove as much code as possible thanks to the
> reduced complexity possible thanks to the stricter behaviour achieved
> by removing settings and enforcing a stricter coding convention and
> killing obsolete quirks.

You lost me at "fork".

Reality is that plpgsql development is not going to stop; wherever
possible we are going to want to put new features into both languages,
and bug fixes even more so. Moreover, the size of the changes you've
suggested hardly merits a fork in the first place, and the notion that
we're going to be able to radically simplify the code base after making
them is laughable. What I would think about is

c) plpgsql and plpgsql2 are the same code base, with a small number
of places that act differently depending on the language version.

We could alternatively get the result by inventing a bunch of pragma
declarations, or some similar notation, that control the behavioral
changes one-at-a-time. That might even be worth doing anyway, in
case somebody likes some of the ideas and others not so much. But
I'd see the language version as a convenient shorthand for enabling a
specified collection of pretty-localized incompatible behavior changes.
If they're not pretty localized, there's going to be a barrier to
uptake, very comparable to the python3 analogy mentioned upthread.

regards, tom lane

=?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= <aht(at)nosys(dot)es> writes:
> What I can add is that, if Postgres is to devote resources to a new
> language, I would plan it with a broader scope. What would attract most
> users? Would it bring non postgres users to Postgres? What could be one
> of the killer features of any next version? My trivial answer to most of
> these questions is: PL/SQL.

By that I suppose you mean "I wish it would act just like Oracle".
The problem with such a wish is that a lot of the incompatibilities
with Oracle are functions of the core SQL engine, not of the PL.
plpgsql already is about as close to PL/SQL as it's possible to get
without changing core Postgres behavior --- or at least, that was
the original design desire, and I don't think that it's failed in
any large degree.

regards, tom lane

On 01/09/14 20:42, Tom Lane wrote:
> =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= <aht(at)nosys(dot)es> writes:
>> What I can add is that, if Postgres is to devote resources to a new
>> language, I would plan it with a broader scope. What would attract most
>> users? Would it bring non postgres users to Postgres? What could be one
>> of the killer features of any next version? My trivial answer to most of
>> these questions is: PL/SQL.
> By that I suppose you mean "I wish it would act just like Oracle".
> The problem with such a wish is that a lot of the incompatibilities
> with Oracle are functions of the core SQL engine, not of the PL.
> plpgsql already is about as close to PL/SQL as it's possible to get
> without changing core Postgres behavior --- or at least, that was
> the original design desire, and I don't think that it's failed in
> any large degree.
>
> regards, tom lane

It's true that some of the incompatibilities are the core engine,
internal functions and so on, and that the plpgsql design goal was to
achieve "similarity". But similarity is not code compatibility, and
afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code
compatibility, if possible, is a very well first step, only followed by
the core functionalities you mention.

If postgres were going for a new language, why not implement one
which, having the other suggested functionality, also has 1:1 PL/SQL
code compatibility? I'm sure it's no trivial task, but one highly desirable.

Regards,

Álvaro

2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es>:

>
> On 01/09/14 20:42, Tom Lane wrote:
>
>> =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= <aht(at)nosys(dot)es> writes:
>>
>>> What I can add is that, if Postgres is to devote resources to a new
>>> language, I would plan it with a broader scope. What would attract most
>>> users? Would it bring non postgres users to Postgres? What could be one
>>> of the killer features of any next version? My trivial answer to most of
>>> these questions is: PL/SQL.
>>>
>> By that I suppose you mean "I wish it would act just like Oracle".
>> The problem with such a wish is that a lot of the incompatibilities
>> with Oracle are functions of the core SQL engine, not of the PL.
>> plpgsql already is about as close to PL/SQL as it's possible to get
>> without changing core Postgres behavior --- or at least, that was
>> the original design desire, and I don't think that it's failed in
>> any large degree.
>>
>> regards, tom lane
>>
>
> It's true that some of the incompatibilities are the core engine,
> internal functions and so on, and that the plpgsql design goal was to
> achieve "similarity". But similarity is not code compatibility, and afaik,
> plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility,
> if possible, is a very well first step, only followed by the core
> functionalities you mention.
>
> If postgres were going for a new language, why not implement one
> which, having the other suggested functionality, also has 1:1 PL/SQL code
> compatibility? I'm sure it's no trivial task, but one highly desirable.
>

It is false expectation - language is only one part .. and plpgsql isn't to
far. There are different system of modules, different system of custom
aggregates, mainly with PL/SQL is very complex library dbms_xxxx. This
library is maybe more complex than current Postgres base.

It is task for commercial project --- not all Postgres users need a Oracle
compatibility layer. Next, I am sure, so it is in contradiction to Joel
proposal.

Regards

Pavel

>
> Regards,
>
> Álvaro
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Mon, Sep 1, 2014 at 8:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> c) plpgsql and plpgsql2 are the same code base, with a small number
> of places that act differently depending on the language version.

+1 to the idea

On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa <aht(at)nosys(dot)es> wrote:
> What I can add is that, if Postgres is to devote resources to a new
> language, I would plan it with a broader scope. What would attract most
> users? Would it bring non postgres users to Postgres? What could be one of
> the killer features of any next version? My trivial answer to most of these
> questions is: PL/SQL. I don't know with detail how complex this is to get in
> Postgres (well, EDB probably knows), but if I had to chose a new language,
> this is it. So my questions would rather be:

Interesting visionary ideas.

For me personally, I have no Oracle functions to port to Postgres, so
all differences
between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work
in the process of porting existing code into a new language, be it
PL/SQL or PL/pgSQL 2.

That said, if PL/SQL wouldn't suffer from the problems I have with
PL/pgSQL today,
I wouldn't see a problem writing new code in the language, but then I
would probably
never manage to port all existing code to PL/SQL and I would be stuck
with a mix of code
in two languages instead of one. With PL/pgSQL 2 on the other hand, it
would be feasible
to eventually port all my existing code, as most of it would be
compatible without changes
and the rest would easy to make compatible.

I guess it's a question of if it's more important to recruit Oracle users,
or if it's more important to improve the life of existing PL/pgSQL
Postgres users.

On 01/09/14 21:08, Pavel Stehule wrote:
>
>
>
> 2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es
> <mailto:aht(at)nosys(dot)es>>:
>
>
> On 01/09/14 20:42, Tom Lane wrote:
>
> =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= <aht(at)nosys(dot)es
> <mailto:aht(at)nosys(dot)es>> writes:
>
> What I can add is that, if Postgres is to devote
> resources to a new
> language, I would plan it with a broader scope. What would
> attract most
> users? Would it bring non postgres users to Postgres? What
> could be one
> of the killer features of any next version? My trivial
> answer to most of
> these questions is: PL/SQL.
>
> By that I suppose you mean "I wish it would act just like Oracle".
> The problem with such a wish is that a lot of the
> incompatibilities
> with Oracle are functions of the core SQL engine, not of the PL.
> plpgsql already is about as close to PL/SQL as it's possible
> to get
> without changing core Postgres behavior --- or at least, that was
> the original design desire, and I don't think that it's failed in
> any large degree.
>
> regards, tom lane
>
>
> It's true that some of the incompatibilities are the core
> engine, internal functions and so on, and that the plpgsql design
> goal was to achieve "similarity". But similarity is not code
> compatibility, and afaik, plpgsql is not code compatible with
> PL/SQL. Having 1:1 code compatibility, if possible, is a very well
> first step, only followed by the core functionalities you mention.
>
> If postgres were going for a new language, why not implement
> one which, having the other suggested functionality, also has 1:1
> PL/SQL code compatibility? I'm sure it's no trivial task, but one
> highly desirable.
>
>
> It is false expectation - language is only one part .. and plpgsql
> isn't to far. There are different system of modules, different system
> of custom aggregates, mainly with PL/SQL is very complex library
> dbms_xxxx. This library is maybe more complex than current Postgres base.

OK. Understood. Full compatibility may be a longer-term goal. But
why it's bad to have the same syntax at a language -not library- level?

>
> It is task for commercial project --- not all Postgres users need a
> Oracle compatibility layer.

Certainly not all users need that layer. But I'm sure few would
complain to have it.

Besides that, why do you say it is meant for a commercial project?
If it is because postgres should not listen to users willing to migrate
from Oracle --then we're screwed, losing the biggest opportunity (of
attracting a large crowd of users) of recent times. If it is because
it's too complex... well, I don't think the postgres community (as a
whole) have less resources than commercial projects.

> Next, I am sure, so it is in contradiction to Joel proposal.

That's not my business ;P

No, really: if there is a new version of a "language", which
modifies the current syntax of plpgsql; if plpgsql is already very
similar to PL/SQL: why not rather than coming up with a new syntax use
an already existing one? One that many, many more users than plpgsql,
already know?

Regards,

Álvaro

On 01/09/14 21:52, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa <aht(at)nosys(dot)es> wrote:
>> What I can add is that, if Postgres is to devote resources to a new
>> language, I would plan it with a broader scope. What would attract most
>> users? Would it bring non postgres users to Postgres? What could be one of
>> the killer features of any next version? My trivial answer to most of these
>> questions is: PL/SQL. I don't know with detail how complex this is to get in
>> Postgres (well, EDB probably knows), but if I had to chose a new language,
>> this is it. So my questions would rather be:
> Interesting visionary ideas.
>
> For me personally, I have no Oracle functions to port to Postgres, so
> all differences
> between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work
> in the process of porting existing code into a new language, be it
> PL/SQL or PL/pgSQL 2.
>
> That said, if PL/SQL wouldn't suffer from the problems I have with
> PL/pgSQL today,
> I wouldn't see a problem writing new code in the language, but then I
> would probably
> never manage to port all existing code to PL/SQL and I would be stuck
> with a mix of code
> in two languages instead of one. With PL/pgSQL 2 on the other hand, it
> would be feasible
> to eventually port all my existing code, as most of it would be
> compatible without changes
> and the rest would easy to make compatible.
>
> I guess it's a question of if it's more important to recruit Oracle users,
> or if it's more important to improve the life of existing PL/pgSQL
> Postgres users.

I agree that for you, unfortunately, plpgsql2 would be better than
PL/SQL. However, I believe as a whole a bigger majority of users would
be benefited from this.

If anyone is willing to look at the market numbers, the number of
PL/SQL users compared to those of plpgsql is really huge. That would
surely attract many more users to postgres, which would ultimately
greatly help us all (in detriment of you and a few others, sure, but on
a way that I'm sure it's manageable).

Of course, I'd +1 any attempt to build a super-set of PL/SQL that
would try to implement, also, the request that you and others may have
about this PL, should they make sense.

Regards,

Álvaro

On 2014-09-01 11:11 PM, Álvaro Hernández Tortosa wrote:
> No, really: if there is a new version of a "language", which
> modifies the current syntax of plpgsql; if plpgsql is already very
> similar to PL/SQL: why not rather than coming up with a new syntax use
> an already existing one? One that many, many more users than plpgsql,
> already know?

The point isn't to create a new language just for the sake of creating a
new one. It's to fix the problems PL/PgSQL has. If we're just going to
trade the problems in PL/PgSQL with another set of problems implemented
by PL/SQL, we're just worse off in the end.

.marko

Álvaro Hernández Tortosa wrote
> On 01/09/14 21:52, Joel Jacobson wrote:
>> On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa &lt;

> aht@

> &gt; wrote:
>>> What I can add is that, if Postgres is to devote resources to a new
>>> language, I would plan it with a broader scope. What would attract most
>>> users? Would it bring non postgres users to Postgres? What could be one
>>> of
>>> the killer features of any next version? My trivial answer to most of
>>> these
>>> questions is: PL/SQL. I don't know with detail how complex this is to
>>> get in
>>> Postgres (well, EDB probably knows), but if I had to chose a new
>>> language,
>>> this is it. So my questions would rather be:
>> Interesting visionary ideas.
>>
>> For me personally, I have no Oracle functions to port to Postgres, so
>> all differences
>> between PL/SQL and PL/pgSQL would just be unnecessary extra amount of
>> work
>> in the process of porting existing code into a new language, be it
>> PL/SQL or PL/pgSQL 2.
>>
>> That said, if PL/SQL wouldn't suffer from the problems I have with
>> PL/pgSQL today,
>> I wouldn't see a problem writing new code in the language, but then I
>> would probably
>> never manage to port all existing code to PL/SQL and I would be stuck
>> with a mix of code
>> in two languages instead of one. With PL/pgSQL 2 on the other hand, it
>> would be feasible
>> to eventually port all my existing code, as most of it would be
>> compatible without changes
>> and the rest would easy to make compatible.
>>
>> I guess it's a question of if it's more important to recruit Oracle
>> users,
>> or if it's more important to improve the life of existing PL/pgSQL
>> Postgres users.
>
> I agree that for you, unfortunately, plpgsql2 would be better than
> PL/SQL. However, I believe as a whole a bigger majority of users would
> be benefited from this.

Is it even legal for us to create PL/SQL?

Beyond that in all likelihood having both a version two of the pl/pgsql
language and the pl/SQL language would be a desireable outcome for, say, a
10.0 release.

The former simply because languages by their very nature are evolutionary
and at some point the lost productivity of suppressing such evolution in the
name of backward compatibility will be deemed undesirable. It may be
desirable to simply call the new language pl/elephant instead pl/pgsql2 but
the fundamental reason for evolving a language in order to incorporate newly
acquired knowledge is unarguable. Though in this case the entire
language/extension mechanism should be considered and not just the specific
procedural-SQL language we are dealing with here.

The goal of adding PL/SQL would be to increase the user base of the project
and hopefully attract new blood to the development team in order to maximize
long-term survivability and increase the pace of innovation. We would be
unable to introduce substantial evolution to this language for that very
reason and so a different language is likely to be needed - eventually.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817251.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 01/09/14 23:31, Marko Tiikkaja wrote:
> On 2014-09-01 11:11 PM, Álvaro Hernández Tortosa wrote:
>> No, really: if there is a new version of a "language", which
>> modifies the current syntax of plpgsql; if plpgsql is already very
>> similar to PL/SQL: why not rather than coming up with a new syntax use
>> an already existing one? One that many, many more users than plpgsql,
>> already know?
>
> The point isn't to create a new language just for the sake of creating
> a new one. It's to fix the problems PL/PgSQL has. If we're just
> going to trade the problems in PL/PgSQL with another set of problems
> implemented by PL/SQL, we're just worse off in the end.
>
Agreed. But if we can solve them --only if we could-- by leveraging
a "syntax" that happens to be:

- Similar to that of plpgsql (exactly the same as plpgsql2 would be
"similar" to plpgsql)
- Already known by a large, very large, group of users

we would be way better off. If there are unresolved problems in the
PL/SQL current implementation, doing a superset of it may make sense.

Regards,

Álvaro

On 01/09/14 23:46, David G Johnston wrote:
> Álvaro Hernández Tortosa wrote
>> On 01/09/14 21:52, Joel Jacobson wrote:
>>> On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa &lt;
>> aht@
>> &gt; wrote:
>>>> What I can add is that, if Postgres is to devote resources to a new
>>>> language, I would plan it with a broader scope. What would attract most
>>>> users? Would it bring non postgres users to Postgres? What could be one
>>>> of
>>>> the killer features of any next version? My trivial answer to most of
>>>> these
>>>> questions is: PL/SQL. I don't know with detail how complex this is to
>>>> get in
>>>> Postgres (well, EDB probably knows), but if I had to chose a new
>>>> language,
>>>> this is it. So my questions would rather be:
>>> Interesting visionary ideas.
>>>
>>> For me personally, I have no Oracle functions to port to Postgres, so
>>> all differences
>>> between PL/SQL and PL/pgSQL would just be unnecessary extra amount of
>>> work
>>> in the process of porting existing code into a new language, be it
>>> PL/SQL or PL/pgSQL 2.
>>>
>>> That said, if PL/SQL wouldn't suffer from the problems I have with
>>> PL/pgSQL today,
>>> I wouldn't see a problem writing new code in the language, but then I
>>> would probably
>>> never manage to port all existing code to PL/SQL and I would be stuck
>>> with a mix of code
>>> in two languages instead of one. With PL/pgSQL 2 on the other hand, it
>>> would be feasible
>>> to eventually port all my existing code, as most of it would be
>>> compatible without changes
>>> and the rest would easy to make compatible.
>>>
>>> I guess it's a question of if it's more important to recruit Oracle
>>> users,
>>> or if it's more important to improve the life of existing PL/pgSQL
>>> Postgres users.
>> I agree that for you, unfortunately, plpgsql2 would be better than
>> PL/SQL. However, I believe as a whole a bigger majority of users would
>> be benefited from this.
> Is it even legal for us to create PL/SQL?
Honestly, I don't know. But I don't think so. And if it is, be it.
Let's have Oracle sue us (who?)
>
> Beyond that in all likelihood having both a version two of the pl/pgsql
> language and the pl/SQL language would be a desireable outcome for, say, a
> 10.0 release.
If 10.0 is to follow 9.5, I'd agree. Althouth I'd also agree for
any earlier, if that would be possible.
>
> The former simply because languages by their very nature are evolutionary
> and at some point the lost productivity of suppressing such evolution in the
> name of backward compatibility will be deemed undesirable. It may be
> desirable to simply call the new language pl/elephant instead pl/pgsql2 but
> the fundamental reason for evolving a language in order to incorporate newly
> acquired knowledge is unarguable. Though in this case the entire
> language/extension mechanism should be considered and not just the specific
> procedural-SQL language we are dealing with here.
>
> The goal of adding PL/SQL would be to increase the user base of the project
> and hopefully attract new blood to the development team in order to maximize
> long-term survivability and increase the pace of innovation. We would be
> unable to introduce substantial evolution to this language for that very
> reason and so a different language is likely to be needed - eventually.

That's what I think. Increasing the user base, in a moment where
for many reasons we all know many want to migrate off of Oracle... let's
bring them here before they move to something else, with also piles of
money and great marketing capabilities....

Álvaro

On Sep 1, 2014, at 10:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> What is actually being proposed, AFAICS, is a one-shot fix for a bunch
> of unfortunate choices. That might be worth doing, but let's not fool
> ourselves about whether it’s one-shot or not.

Well, one shot every 18 years is not so bad.

I am only a casual user and as such probably do not merit much consideration from the experts here. I only work with PL/pgSQL occasionally, but never go weeks without doing it and sometimes it is all I do for weeks.

That said and this being the internet, IMO working in PL/pgSQL is only slightly better than stabbing myself in the leg with a knife compared to other non-PL/pgSQL languages I work in. Mostly my hate is directed at the silly quoting. But it has lots of other quirks that are not all that obvious unless you work with it all day every day.

Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL.

Really, this is from the docs

a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ‘'''''; end if;'';

That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL

For example:

DECLARE
declarations
BEGIN
statements
END

This looks a lot like COBOL or Pascal, and today is mostly unnecessary.

So my opinion would be to keep PL/pgSQL and adopt a new PL language that is fully supported, fast, and modern, that will over time replace PL/pgSQL.

Neil

On 09/01/2014 08:09 PM, Neil Tiffin wrote:
> On Sep 1, 2014, at 10:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> What is actually being proposed, AFAICS, is a one-shot fix for a bunch
>> of unfortunate choices. That might be worth doing, but let's not fool
>> ourselves about whether it’s one-shot or not.
> Well, one shot every 18 years is not so bad.
>
> I am only a casual user and as such probably do not merit much consideration from the experts here. I only work with PL/pgSQL occasionally, but never go weeks without doing it and sometimes it is all I do for weeks.
>
> That said and this being the internet, IMO working in PL/pgSQL is only slightly better than stabbing myself in the leg with a knife compared to other non-PL/pgSQL languages I work in. Mostly my hate is directed at the silly quoting. But it has lots of other quirks that are not all that obvious unless you work with it all day every day.
>
> Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL.
>
> Really, this is from the docs
>
> a_output := a_output || '' if v_'' ||
> referrer_keys.kind || '' like ''''''''''
> || referrer_keys.key_string || ''''''''''
> then return '''''' || referrer_keys.referrer_type
> || ‘'''''; end if;'';

The docs also tell you how to avoid having to do this, using dollar quoting.

>
> That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL
>
> For example:
>
> DECLARE
> declarations
> BEGIN
> statements
> END
>
> This looks a lot like COBOL or Pascal, and today is mostly unnecessary.

It looks like Ada, and that's not an accident. (Nor is it a bad thing.)

The very last thing we should be doing is to invent a new language.
There are already plenty to choose from.

cheers

andrew

On Mon, Sep 1, 2014 at 9:07 PM, Andrew Dunstan [via PostgreSQL] <
ml-node+s1045698n5817265h39(at)n5(dot)nabble(dot)com> wrote:

>
> On 09/01/2014 08:09 PM, Neil Tiffin wrote:
> >
> > That should be enough alone to suggest postgreSQL start working on a
> modern, in core, fast, fully supported language. Of course PL/pgSQL works,
> but so did one-line 5k perl programs that nobody likes today. Everything
> can be done in assembler, but no one suggests that today. Today, it is all
> about programmer productivity. PL/pgSQL has a lot of unnecessary stuff
> that sucks the life out of programmer productivity. And this should be
> very much a concern of the professionals that support PostgreSQL
> >
> > For example:
> >
> > DECLARE
> > declarations
> > BEGIN
> > statements
> > END
> >
> > This looks a lot like COBOL or Pascal, and today is mostly unnecessary.
>
> It looks like Ada, and that's not an accident. (Nor is it a bad thing.)
>
>
> The very last thing we should be doing is to invent a new language.
> There are already plenty to choose from.
>
> cheers
>
> andrew
>
>
​The extent of "plenty" narrows considerably if you factor in a requirement
for SQL to be treated as a first-class construct...

I would welcome the chance to evaluate an unencumbered language designed to
mesh with PostgreSQL​ specifically and that would greatly ease the effort
needed to write applications driven largely via in-database functions. Put
differently - how much effort do we want to making PostgreSQL an
irresistible platform that is difficult to leave? Now, of course, and
salient to the point Andrew made, I'm not sure we actually have anyone with
the talent AND desire to actually create such a language - we haven't need
for a specialist language writer for a while now and I think you'd want a
specialist if you were to try and write a language from scratch (over even
adapt an existing language like what was apparently done with Ada).

The question here is whether the resources are available, if it was deemed
desirable, to even superficially overhaul pl/pgsql?

Random thought as I wrote that: how about considering how pl/pgsql
functionality can be generalize so that it is a database API that another
language can call? In that way the server would drive the core
functionality and the language would simply be an interpreter that enforces
its specific notion of acceptable syntax.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817267.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 09/02/2014 09:40 AM, David G Johnston wrote:
> Random thought as I wrote that: how about considering how pl/pgsql
> functionality can be generalize so that it is a database API that
> another language can call? In that way the server would drive the core
> functionality and the language would simply be an interpreter that
> enforces its specific notion of acceptable syntax.

That's pretty much what we already have with the SPI and procedural
language handler infrastructure. PL/Perl, PL/Python, etc exist because
we have this.

What do you see as missing from the current infrastructure? What can't
be done that should be able to be done in those languages?

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/02/2014 08:09 AM, Neil Tiffin wrote:
> Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL.

PL/Perl is plenty fast, FWIW.

I agree that it is unfortunate that we don't have an in-core trusted
"real language" PL other than PL/Perl. I am personally hoping that PL/V8
will be in a position to be adopted as "PL/JavaScript" soon, as that
would be an excellent fit with how the language fashion world is
currently moving - JSON and JavaScript abound.

More seriously, JavaScript is also a good fit for a trusted PL. I've
long favoured Lua because of the excellent embeddable runtime and
security-friendly design, but it's never really got the uptake required
to make it a serious contender.

I'd be quite happy to see PL/JavaScript in-core.

(The other obvious candidate would be PL/Ruby, but it doesn't have an
untrusted variant, and AFAIK Ruby is no better than Python when it comes
to supporting a secure runtime: hopeless.)

> That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language.

I couldn't disagree more.

If we were to implement anything, it'd be PL/PSM
(http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
quirky as anything else the SQL committee has brought forth, but it's at
least a standard(ish) language.

Creating a new language when there are already many existing contenders
is absolutely nonsensical. Other than PL/PSM the only thing that'd make
any sense would be to *pick a suitable existing language* like Lua or
JavaScript and bless it as a supported, always-available, in-core
language runtime that's compiled in by default.

> Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL

PL/PgSQL is how it is in part because of PL/SQL
(http://en.wikipedia.org/wiki/PL/SQL) which in turn owes its heritage to
Ada and Pascal.

It serves an important role. I'm not going to pretend it's pretty, but

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/02/2014 05:46 AM, David G Johnston wrote:
> The goal of adding PL/SQL would be to increase the user base of the project
> and hopefully attract new blood to the development team in order to maximize
> long-term survivability and increase the pace of innovation. We would be
> unable to introduce substantial evolution to this language for that very
> reason and so a different language is likely to be needed - eventually.

Sure - but it can be done out of core, with core patches only as
required for making things that aren't currently possible work.

You'd need to solve some really hard problems though. Autonomous
transactions and top-level true "procedures" being the big ones. You'd
also need to handle multiple result sets, TABLE-typed variables (backed
with a tuplestore, maybe?), and lots more.

Most of the differences between PL/SQL and PL/PgSQL stem from underlying
differences in PostgresSQL and Oracle, as Tom has already pointed out.
Often PostgreSQL limitations that don't exist in Oracle. So you'd have a
lot of work to do in core to make a usefully-better-than-PL/PgSQL PL/SQL
implementation possible.

If someone came up with a convincing PL/SQL compatibility layer then
it'd be worth considering adopting - when it was ready. But of course,
anyone who does the work for that is quite likely to want to sell it to
cashed-up Oracle users looking to save a few hundred grand on per-CPU
licensing.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Sep 1, 2014 at 11:12 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 09/02/2014 09:40 AM, David G Johnston wrote:
> > Random thought as I wrote that: how about considering how pl/pgsql
> > functionality can be generalize so that it is a database API that
> > another language can call? In that way the server would drive the core
> > functionality and the language would simply be an interpreter that
> > enforces its specific notion of acceptable syntax.
>
> That's pretty much what we already have with the SPI and procedural
> language handler infrastructure. PL/Perl, PL/Python, etc exist because
> we have this.
>
> What do you see as missing from the current infrastructure? What can't
> be done that should be able to be done in those languages?
>
>
​Yet pl/pgsql does not have to use SPI-interface type calls to interact
with PostgreSQL at the SQL level...

​I don't have an answer to your questions but the one I'm asking is whether
a particular language could hide all of the SPI stuff behind some custom
syntax so that it in effect looks similar to what pl/pgsql does today? Or,
more to the point, does pl/pgsql use the same SPI interface behind the
scenes as PL/Perl or does it have its own special interface?

David J.

On 09/01/2014 11:19 PM, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> On 09/01/2014 10:41 PM, Joel Jacobson wrote:
>>> This is exactly why we need a new language.
>>> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
>>> plpgsql2, with the most beautiful syntax we can come up with.
>>>
>>> I guess it's a question if we want to support things like this. If we
>>> want to, then we also want a new language.
>>
>> Given how much bike shedding occurs around trivial features, can you
>> imagine how long that'd take?
>
> I wasn't aware of the expression "bike shedding" so I had to look it up.
> It apparently means "spend the majority of its time on relatively
> unimportant but easy-to-grasp issues".
> If you feel the development of plpgsql falls into this category, that
> most time is spent on the smaller unimportant things, isn't that a
> clear sign we need plpgsql2, for there to be any hope of progress on
> the important things?

Er, no.

My point is that weeks can be spent just arguing about whether you
should have a variable-delimiter ($variable) or not, how syntax should
look, etc. Imagine how long it'd take to get a new language syntax
agreed upon?

You jumped in to say that you thought that:

EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;

was "is exactly why we need a new language" and that "All the clumsy
stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with
the most beautiful syntax we can come up with." But you haven't said HOW
you propose to fix this one case.

Show me. How do you want this to look? The user requirement is "Execute
a SELECT against a table whose name is provided at runtime, selecting a
column or set of columns whose names are provided at runtime, with
literals substituted as placement parameters."

The above is ugly. Fine, not arguing. Show me what you want instead.

You're happy to say how much you dislike PL/PgSQL, but I haven't seen a
concrete proposal on how you want something new to look. That would be a
useful and constructive start, as we could then examine, point-by-point,
how/if those needs can be met in PL/PgSQL. If they can't then you'd have
a more convincing argument for a new version than "PL/PgSQL sucks".

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 09/02/2014 11:42 AM, David Johnston wrote:
>
> ​Yet pl/pgsql does not have to use SPI-interface type calls to interact
> with PostgreSQL at the SQL level...

That's right.

> ​I don't have an answer to your questions but the one I'm asking is
> whether a particular language could hide all of the SPI stuff behind
> some custom syntax so that it in effect looks similar to what pl/pgsql
> does today?

Sure, it's possible. Have you seen Microsoft's "LINQ" for C# / .NET? One
of the few really awesome things they've done in a long time.

Do you see many other languages with good seamless SQL support, though?
The only one I can think of off the top of my head is PROGRESS/4GL, and
like PL/PgSQL it's designed around the idea of being a
database-integrated language.

The hard problem to solve here isn't "how do I make the language talk to
the database server backend". That's easy. The hard problem is "how
should the integrated SQL support look and work so it fits in with the
language while remaining as powerful and expressive as SQL its self".

It's *the same* problem as if you were using the same language via a
network connection to the database, instead of embedding it. How do you
make SQL syntax interoperate with your language's syntax, or produce a
clean-ish adapter layer?

Lots of people have tried. Most have failed dismally. ActiveRecord
becomes gruesomely ugly hodgepodge of text snippets and code as soon as
you try to do anything interesting/nontrivial with it. Hibernate
Criteria Query - ever wanted to write 100 lines of code for a simple
query? You'll love it. JOOQ? Django ORM? SQLAlchemy? iBatis/MyBatis?
QueryDSL? They're all ugly in various ways.

The underlying problem is that SQL, syntactically speaking, is a weird,
clunky way to do things that sticks out like a sore thumb when you put
it together with a language designed after 1975.

Also, most of the "modern" languages people want to use are to some
degree OO, not just procedural. That brings the OO/relational mismatch
into play, and there's no pretty and easy solution for that.

http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch

PL/PgSQL gets away with it by not being OO - it fits its model around
that of SQL.

If you can solve that problem - create a smooth, seamless
language-integrated way to use SQL in any language of your choice - then
please, please come talk about it here.

> Or, more to the point, does pl/pgsql use the same SPI
> interface behind the scenes as PL/Perl or does it have its own special
> interface?

Take a look.

cd src/pl/pgsql/src

You'll see pl_handler.c, the PL/PgSQL procedural language handler.
There's also pl_exec.c, containing the guts of the language runtime. SPI
use is heavy throughout.

PL/PgSQL uses SPI to execute queries and fetch results. See, for
example, exec_stmt_execsql(...) in pl_exec.c .

Any other language that can run embedded into the PostgreSQL backend can
do the same.

PL/Python does it:

http://www.postgresql.org/docs/current/static/plpython-database.html

(though unfortunately the PL/Python interface for SQL does't follow the
Python DB-API).

PL/V8, PL/Lua, PL/Ruby, they all use the SPI. Same as PL/PgSQL.

The challenge isn't dispatching the SQL, the challenge is fitting SQL
into the language seamlessly.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 02/09/14 15:46, Craig Ringer wrote:

> was "is exactly why we need a new language" and that "All the clumsy
> stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with
> the most beautiful syntax we can come up with." But you haven't said HOW
> you propose to fix this one case.

Unfortunately, there is likely to be a (large) variance of opinion
concerning the details. In particular 'beautiful/elegant...'. Err -
these things are mostly in the eye of the beholder. E.g: I might want
this new shiny syntax to be lisp like, as that is beautiful (heh,
kidding - but you should get the idea).

Cheers

Mark

On 14/09/02 12:24, Craig Ringer wrote:
> On 09/02/2014 08:09 AM, Neil Tiffin wrote:
(...)

>> That should be enough alone to suggest postgreSQL start working on a modern,
>> in core, fast, fully supported language.
>
> I couldn't disagree more.
>
> If we were to implement anything, it'd be PL/PSM
> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
> quirky as anything else the SQL committee has brought forth, but it's at
> least a standard(ish) language.

For reference, and without wading into the general debate, there is an
existing, albeit outdated and dormant PL/PSM implementation:

http://pgfoundry.org/frs/?group_id=1000238
http://postgres.cz/wiki/SQL/PSM_Manual

From my (limited) experience with the MySQL variant, it makes PL/pgSQL
look positively concise and elegant. Though that's just my subjective
opinion (possibly coloured by the particular implementation) and not
necessarily a pro/contra argument ;).

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> If someone came up with a convincing PL/SQL compatibility layer then
> it'd be worth considering adopting - when it was ready. But of course,
> anyone who does the work for that is quite likely to want to sell it to
> cashed-up Oracle users looking to save a few hundred grand on per-CPU
> licensing.

As a case in point, EDB have spent quite a few man-years on their Oracle
compatibility layer; and it's still not a terribly exact match, according
to my colleagues who have looked at it. So that is a tarbaby I don't
personally care to touch ... even ignoring the fact that cutting off
EDB's air supply wouldn't be a good thing for the community to do.

regards, tom lane

On 09/02/2014 12:40 PM, Tom Lane wrote:
> As a case in point, EDB have spent quite a few man-years on their Oracle
> compatibility layer; and it's still not a terribly exact match, according
> to my colleagues who have looked at it. So that is a tarbaby I don't
> personally care to touch ... even ignoring the fact that cutting off
> EDB's air supply wouldn't be a good thing for the community to do.

Yep. Especially as PL/SQL is not a lovely language to work with anyway;
if the goal was "a better built-in PL" then PL/SQL wouldn't be my first
choice by any stretch.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Sep 2, 2014 at 5:46 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> My point is that weeks can be spent just arguing about whether you
> should have a variable-delimiter ($variable) or not, how syntax should
> look, etc. Imagine how long it'd take to get a new language syntax
> agreed upon?

I would guess about a year.

> You jumped in to say that you thought that:
>
> EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>
> was "is exactly why we need a new language" and that "All the clumsy
> stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with
> the most beautiful syntax we can come up with." But you haven't said HOW
> you propose to fix this one case.
>
> Show me. How do you want this to look? The user requirement is "Execute
> a SELECT against a table whose name is provided at runtime, selecting a
> column or set of columns whose names are provided at runtime, with
> literals substituted as placement parameters."
>
> The above is ugly. Fine, not arguing. Show me what you want instead.
>
>
> You're happy to say how much you dislike PL/PgSQL, but I haven't seen a
> concrete proposal on how you want something new to look. That would be a
> useful and constructive start, as we could then examine, point-by-point,
> how/if those needs can be met in PL/PgSQL. If they can't then you'd have
> a more convincing argument for a new version than "PL/PgSQL sucks".

I've *never* said PL/pgSQL sucks.
I *love* PL/pgSQL, seriously.
I write code for many hours a day in the language.
I don't even want to change much.
My wishlist consists mostly of things which makes the language more secure.
Currently it's a pain to verify your data operations do exactly what
you requested.
I would guess most novice developers don't understand this, and by
mistake write insecure code.
I don't want any OO. I don't want PL/SQL or PL/PSM. I'm a happy camper
with PL/pgSQL.

That said, *if* we now have a one-shot opportunity of possibly
breaking a bit of compatibility for a minority of current code,
motivated by the introduction of new important features not possible
without plpgsql2, *then* let's make the best of that opportunity.

I don't find myself selecting from a table which table name I don't
know the name when writing the code,
so I'm not pariticulary interested in prodiving a syntax for that use case,
but I'm not against the feature if others need it, even if it would
possibly increase the lines of code of existing plpgsql code which
needs to be modified to remain compatible by X %.

Given the needed diff between plpgsql and plpgsql2 for the changes I'm
mostly interested in would probably be quite small,
I'm in favour of Tom's suggestion of:
>c) plpgsql and plpgsql2 are the same code base, with a small number
>of places that act differently depending on the language version.

That fits perfectly for my needs, as I don't want to change much.

But even if we find we want to make larger mostly-compatible changes,
maybe that also can be implemented using the same approach.

For me, the most important is to not break *most* of existing plpgsql
code, but it's OK to break *some*.
And when breaking it, it should be trivial to rewrite it to become compatible.

On 09/02/2014 09:06 AM, Joel Jacobson wrote:
> Given the needed diff between plpgsql and plpgsql2 for the changes I'm
> mostly interested in would probably be quite small,
> I'm in favour of Tom's suggestion of:
>> c) plpgsql and plpgsql2 are the same code base, with a small number
>> of places that act differently depending on the language version.
>
> That fits perfectly for my needs, as I don't want to change much.
>
> But even if we find we want to make larger mostly-compatible changes,
> maybe that also can be implemented using the same approach.
>
> For me, the most important is to not break *most* of existing plpgsql
> code, but it's OK to break *some*.
> And when breaking it, it should be trivial to rewrite it to become compatible.

I think the next step would be to list all the things you don't like
with current PL/pgSQL, and write down how you would want them to work if
you were starting with a clean slate. Let's see how wide the consensus
is that the new syntax/behavior is better than what we have now. We can
then start thinking how to best adapt them to the current PL/pgSQL
syntax and codebase. Maybe with pragmas, or new commands, or deprecating
the old behavior; the best approach depends on the details, and how
widely desired the new behavior is, so we need to see that first.

I'd suggest collecting the ideas on a wiki page, and once you have some
concrete set of features and syntax there, start a new thread to discuss
them. Others will probably have other features they want, like the
simpler "DROP TABLE ?" thing.

- Heikki

On Tue, Sep 2, 2014 at 8:26 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> On 09/02/2014 09:06 AM, Joel Jacobson wrote:
>> For me, the most important is to not break *most* of existing plpgsql
>> code, but it's OK to break *some*.
>> And when breaking it, it should be trivial to rewrite it to become
>> compatible.
>
>
> I think the next step would be to list all the things you don't like with
> current PL/pgSQL, and write down how you would want them to work if you were
> starting with a clean slate. Let's see how wide the consensus is that the
> new syntax/behavior is better than what we have now. We can then start
> thinking how to best adapt them to the current PL/pgSQL syntax and codebase.
> Maybe with pragmas, or new commands, or deprecating the old behavior; the
> best approach depends on the details, and how widely desired the new
> behavior is, so we need to see that first.
>
> I'd suggest collecting the ideas on a wiki page, and once you have some
> concrete set of features and syntax there, start a new thread to discuss
> them. Others will probably have other features they want, like the simpler
> "DROP TABLE ?" thing.

Excellent idea, I'm on it!

2014-09-01 11:04 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> Hi,
>
> For those of you who use PL/pgSQL every day, I'm quite certain you all
> feel there are a number of things you would like to change in the language,
> but realize it cannot be achieved without possibly breaking compatibility,
> at least in theory. Even though you own code would survive the change,
> there might be code somewhere in the world which would break. This is of
> course not acceptable and that's why we have the current status quo of
> development, or at least not far away from a status quo.
>
> So instead of continue to adding optional settings to the config file,
> and instead of killing discussions around what can be done by bringing up
> the backwards-compatibility argument, let's instead fork the language and
> call it plpgsql2. Since no code is yet written in plpgsql2, we can start of
> from a clean sheet, and no good ideas need to be killed due to
> backwards-compatibility concerns.
>
> The interest for such a project is probably limited to a small number of
> companies/people around the world, as most users are probably perfectly
> happy with the current version of plpgsql, as they only use it
> occasionally and not every day like we do at my company.
>
> Just like with plpgsql, once released, plpgsql2 cannot break
> compatibility with future versions, so we only have one chance to carefully
> think though what we would like to change in the language.
>
> From the top of my head, these are Things I personally would want to see
> in plpgsql2:
> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
> row, as that's the most common use-case, and provide alternative syntax to
> modify multiple or zero rows.
> + Make SELECT .. INTO .. throw an error if it selects more than 1 row.
> INTO STRICT only works if no rows should be an error, but there is
> currently no nice way if no rows OR exactly 1 row should be found by the
> query.
> + Change all warnings into errors
>

last paragraph is some what I dislike on your proposal. Why:

plpgsql is relative good mix of simplified ADA -- there are no too complex
statement, no too much keywords, it is language that is simple to learn.
Second part of mix is PostgreSQL SQL. It does same things what does in plan
SQL.

Your proposal change it. It is not good idea.

Exactly clean solution is possible now

DELETE FROM tab WHERE xx = somevar;
GET DIAGNOSTICS rc = ROW_COUNT;
IF rc <> 1 THEN
RAISE EXCEPTION
END IF;

It is absolutely clean, absolutely readable. But it is verbose - yes,
agree, maybe too much. But verbosity is basic stone of ADA and plpgsql too.
It is what I like on plpgsql.

What we can do better?

1. we can implement a conditional RAISE

DELETE FROM tab WHERE xx = somevar;
GET DIAGNOSTICS rc = ROW_COUNT;
RAISE EXCEPTION 'some' WHEN rc <> 0;

It is relatively natural and we use similar construct in CONTINUE statement.

2. What can be next? We can implement some idiom (shortcut) for GET
DIAGNOSTICS

DELETE FROM tab WHERE xx = somevar;
RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;

3. What next? Maybe some notations -

-- ** ensure_exact_one_row
DELETE FROM tab WHERE xx = somevar;

But default will be same as in plain SQL.

Regards

Pavel

p.s. I dislike some flags to SQL statements .. like STRICT it increase a
complexity of PL parser, and it increase a distance between SQL and PLPGSQL
SQL.

>
> These are small changes, probably possible with just a few hundred lines
> of code in total, which also should be the ambition, as larger changes
> would never survive during time as it would require too much efforts to
> keep up with the main project. Secondly, I trust plpgsql mainly because
> it's being used by a lot of people in a lot of production systems, the same
> would not hold true for plpgsql2 for the first years of existence, so we
> who would use it in production systems must understand every single line of
> code changed and feel the risk of possible bugs and their impact are within
> acceptable boundaries.
>
> I can probably think of a few more things, but these are the major
> annoyances.
>
> Please share your wish list of things you would want in plpgsql2 which are
> not possible to implement in plpgsql because they could possibly break
> compatibility.
>
> Regards, Joel
>
>

On 02/09/14 05:24, Craig Ringer wrote:
> I couldn't disagree more.
>
> If we were to implement anything, it'd be PL/PSM
> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
> quirky as anything else the SQL committee has brought forth, but it's at
> least a standard(ish) language.
So we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^

>
> Creating a new language when there are already many existing contenders
> is absolutely nonsensical. Other than PL/PSM the only thing that'd make
> any sense would be to *pick a suitable existing language* like Lua or
> JavaScript and bless it as a supported, always-available, in-core
> language runtime that's compiled in by default.

That is in my opinion a way more sensible choice. To bless
PL/JavaScript as an in-core language would be a very wise choice.

Álvaro

2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es>:

>
> On 02/09/14 05:24, Craig Ringer wrote:
>
>> I couldn't disagree more.
>>
>> If we were to implement anything, it'd be PL/PSM
>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>> quirky as anything else the SQL committee has brought forth, but it's at
>> least a standard(ish) language.
>>
> So we'd choose a bizarre and quirky language instead of anything
> better just because it's standard. I'm sure current and prospective users
> will surely prefer a bizarre and quirky language that is standard approved,
> rather than a modern, comfortable, easy-to-use, that is not embodied by the
> ISO. No doubt ^_^
>
>
SQL/PSM is used in >>>DB2<<<, >>>Sybase Anywhere<<<, MySQL,

>
>
>> Creating a new language when there are already many existing contenders
>> is absolutely nonsensical. Other than PL/PSM the only thing that'd make
>> any sense would be to *pick a suitable existing language* like Lua or
>> JavaScript and bless it as a supported, always-available, in-core
>> language runtime that's compiled in by default.
>>
>
> That is in my opinion a way more sensible choice. To bless
> PL/JavaScript as an in-core language would be a very wise choice.
>
> Álvaro
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 9/2/14 11:04 AM, Pavel Stehule wrote:
> It is relatively natural and we use similar construct in CONTINUE statement.
>
> 2. What can be next? We can implement some idiom (shortcut) for GET
> DIAGNOSTICS
>
> DELETE FROM tab WHERE xx = somevar;
> RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;

Yes, a special variable would be closer to how I would prefer to access
the row count.

> 3. What next? Maybe some notations -
>
> -- ** ensure_exact_one_row
> DELETE FROM tab WHERE xx = somevar;

I really, really don't like the idea of turning regular SQL statements
into something slightly different based on comments around (or "inside")
the query.

.marko

On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
>
> On 02/09/14 05:24, Craig Ringer wrote:
>> I couldn't disagree more.
>>
>> If we were to implement anything, it'd be PL/PSM
>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>> quirky as anything else the SQL committee has brought forth, but it's at
>> least a standard(ish) language.
> So we'd choose a bizarre and quirky language instead of anything
> better just because it's standard. I'm sure current and prospective
> users will surely prefer a bizarre and quirky language that is standard
> approved, rather than a modern, comfortable, easy-to-use, that is not
> embodied by the ISO. No doubt ^_^
>

Well there is the risk that by randomly adding new syntax to PL/pgSQL we
turn it in a bizarre and quirky *non standard* language. Part of the
attraction of PL/pgsql is that it is Ada like - if we break that too
much then...well...that would be bad. So I think a careful balance is
needed, to add new features that keep the spirit of the original language.

Regards

Mark

On 02/09/14 06:40, Tom Lane wrote:
> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>> If someone came up with a convincing PL/SQL compatibility layer then
>> it'd be worth considering adopting - when it was ready. But of course,
>> anyone who does the work for that is quite likely to want to sell it to
>> cashed-up Oracle users looking to save a few hundred grand on per-CPU
>> licensing.
> As a case in point, EDB have spent quite a few man-years on their Oracle
> compatibility layer; and it's still not a terribly exact match, according
> to my colleagues who have looked at it. So that is a tarbaby I don't
> personally care to touch ... even ignoring the fact that cutting off
> EDB's air supply wouldn't be a good thing for the community to do.
>
> regards, tom lane
>
>

OK, so this compatibility layer is tough. Knew that already ;) But
on the other side, the syntax is similar to plpgsql, right? So what
about just having a compatible syntax? It would be the first step to
that compatibility layer, which could -or could not- be a long-term goal
for postgres (having the whole layer).

I don't buy that having that would cut EDB's air supply. They're
doing great, and they know how to take care of themselves, I'm sure ;)
Besides that, "competition" is always positive, and I'm sure they'd be
more benefited than harmed by postgres having that layer.

If we are to have another plpgsql-like language (like plpgsql2) and
we could design it so it would attract many many users (let's not forget
that Oracle may have around two orders of magnitude more users than pg),
that would benefit us all greatly. Even if not perfect. Even if it is a
longer project which spans more than one release. But just having the
syntax (or most of it, maybe avoiding some complex unimplemented
postgres features, if that required a huge effort) is a big win.

For 9.4, we have the media already saying "Postgres has NoSQL
capabilities" (which is only partially true). For x.y we could have the
media saying "Postgres adds Oracle compatibility" (which would be only
partially true). But that brings a lot of users to postgres, and that
helps us all.

And also.... it could serve as a motivation point to implement
those in-core missing features, too, that Oracle has.

If on the other hand we resign from attracting Oracle users, in a
moment where non-Oracle databases are fighting for them..... and we lose
here.... well, let's at least have a very compelling, attractive,
in-core, blessed, language. Even disliking it myself, PL/JavaScript
would be my #1 candidate there.

My 4 (already) cents,

Álvaro

2014-09-02 11:34 GMT+02:00 Marko Tiikkaja <marko(at)joh(dot)to>:

> On 9/2/14 11:04 AM, Pavel Stehule wrote:
>
>> It is relatively natural and we use similar construct in CONTINUE
>> statement.
>>
>> 2. What can be next? We can implement some idiom (shortcut) for GET
>> DIAGNOSTICS
>>
>> DELETE FROM tab WHERE xx = somevar;
>> RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;
>>
>
> Yes, a special variable would be closer to how I would prefer to access
> the row count.

I am not against. We have FOUND, we can have "AFFECTED_ROW_COUNT" or
something else. "ROW_COUNT" is probably wide used as variable.

This "style" can be simply implemented.

Pavel

>
>
> 3. What next? Maybe some notations -
>>
>> -- ** ensure_exact_one_row
>> DELETE FROM tab WHERE xx = somevar;
>>
>
> I really, really don't like the idea of turning regular SQL statements
> into something slightly different based on comments around (or "inside")
> the query.
>

it can be something else than comment. For me, it is really futuristic, but
it has more potential than using some specialized keywords inside SQL
statement. More, we can mix it with #option - be global for function.

>
>
> .marko
>

On 02/09/14 11:34, Mark Kirkwood wrote:
> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
>>
>> On 02/09/14 05:24, Craig Ringer wrote:
>>> I couldn't disagree more.
>>>
>>> If we were to implement anything, it'd be PL/PSM
>>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>>> quirky as anything else the SQL committee has brought forth, but
>>> it's at
>>> least a standard(ish) language.
>> So we'd choose a bizarre and quirky language instead of anything
>> better just because it's standard. I'm sure current and prospective
>> users will surely prefer a bizarre and quirky language that is standard
>> approved, rather than a modern, comfortable, easy-to-use, that is not
>> embodied by the ISO. No doubt ^_^
>>
>
> Well there is the risk that by randomly adding new syntax to PL/pgSQL
> we turn it in a bizarre and quirky *non standard* language. Part of
> the attraction of PL/pgsql is that it is Ada like - if we break that
> too much then...well...that would be bad. So I think a careful balance
> is needed, to add new features that keep the spirit of the original
> language.
>

I agree. I think I haven't suggested adding new syntax to pl/pgsql.
But having its syntax similar to ADA is IMHO not something good. I'm
sure few prospective postgres users would be compelled to that. They are
compelled about JavaScript, python, Scala or Ruby, to name a few, but
definitely not ADA.

Regards,

Álvaro

2014-09-02 11:40 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es>:

>
> On 02/09/14 06:40, Tom Lane wrote:
>
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>>
>>> If someone came up with a convincing PL/SQL compatibility layer then
>>> it'd be worth considering adopting - when it was ready. But of course,
>>> anyone who does the work for that is quite likely to want to sell it to
>>> cashed-up Oracle users looking to save a few hundred grand on per-CPU
>>> licensing.
>>>
>> As a case in point, EDB have spent quite a few man-years on their Oracle
>> compatibility layer; and it's still not a terribly exact match, according
>> to my colleagues who have looked at it. So that is a tarbaby I don't
>> personally care to touch ... even ignoring the fact that cutting off
>> EDB's air supply wouldn't be a good thing for the community to do.
>>
>> regards, tom lane
>>
>>
>>
> OK, so this compatibility layer is tough. Knew that already ;) But on
> the other side, the syntax is similar to plpgsql, right? So what about just
> having a compatible syntax? It would be the first step to that
> compatibility layer, which could -or could not- be a long-term goal for
> postgres (having the whole layer).
>
> I don't buy that having that would cut EDB's air supply. They're doing
> great, and they know how to take care of themselves, I'm sure ;) Besides
> that, "competition" is always positive, and I'm sure they'd be more
> benefited than harmed by postgres having that layer.
>
> If we are to have another plpgsql-like language (like plpgsql2) and we
> could design it so it would attract many many users (let's not forget that
> Oracle may have around two orders of magnitude more users than pg), that
> would benefit us all greatly. Even if not perfect. Even if it is a longer
> project which spans more than one release. But just having the syntax (or
> most of it, maybe avoiding some complex unimplemented postgres features, if
> that required a huge effort) is a big win.
>
> For 9.4, we have the media already saying "Postgres has NoSQL
> capabilities" (which is only partially true). For x.y we could have the
> media saying "Postgres adds Oracle compatibility" (which would be only
> partially true). But that brings a lot of users to postgres, and that helps
> us all.
>

Partial true can enforce so lot of people will hate postgres too. False
promises are wrong

>
> And also.... it could serve as a motivation point to implement those
> in-core missing features, too, that Oracle has.
>
> If on the other hand we resign from attracting Oracle users, in a
> moment where non-Oracle databases are fighting for them..... and we lose
> here.... well, let's at least have a very compelling, attractive, in-core,
> blessed, language. Even disliking it myself, PL/JavaScript would be my #1
> candidate there.
>
> My 4 (already) cents,
>
> Álvaro
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 02/09/14 11:31, Pavel Stehule wrote:
>
>
>
> 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es
> <mailto:aht(at)nosys(dot)es>>:
>
>
> On 02/09/14 05:24, Craig Ringer wrote:
>
> I couldn't disagree more.
>
> If we were to implement anything, it'd be PL/PSM
> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as
> bizarre and
> quirky as anything else the SQL committee has brought forth,
> but it's at
> least a standard(ish) language.
>
> So we'd choose a bizarre and quirky language instead of
> anything better just because it's standard. I'm sure current and
> prospective users will surely prefer a bizarre and quirky language
> that is standard approved, rather than a modern, comfortable,
> easy-to-use, that is not embodied by the ISO. No doubt ^_^
>
>
> SQL/PSM is used in >>>DB2<<<, >>>Sybase Anywhere<<<, MySQL,

That's a way better argument that it's standard :)))

Still, I think postgres is in the position of attracting more
Oracle than DB2+Sybase+MySQL users

Álvaro

2014-09-02 11:44 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es>:

>
> On 02/09/14 11:34, Mark Kirkwood wrote:
>
>> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
>>
>>>
>>> On 02/09/14 05:24, Craig Ringer wrote:
>>>
>>>> I couldn't disagree more.
>>>>
>>>> If we were to implement anything, it'd be PL/PSM
>>>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>>>> quirky as anything else the SQL committee has brought forth, but it's at
>>>> least a standard(ish) language.
>>>>
>>> So we'd choose a bizarre and quirky language instead of anything
>>> better just because it's standard. I'm sure current and prospective
>>> users will surely prefer a bizarre and quirky language that is standard
>>> approved, rather than a modern, comfortable, easy-to-use, that is not
>>> embodied by the ISO. No doubt ^_^
>>>
>>>
>> Well there is the risk that by randomly adding new syntax to PL/pgSQL we
>> turn it in a bizarre and quirky *non standard* language. Part of the
>> attraction of PL/pgsql is that it is Ada like - if we break that too much
>> then...well...that would be bad. So I think a careful balance is needed, to
>> add new features that keep the spirit of the original language.
>>
>>
> I agree. I think I haven't suggested adding new syntax to pl/pgsql.
> But having its syntax similar to ADA is IMHO not something good. I'm sure
> few prospective postgres users would be compelled to that. They are
> compelled about JavaScript, python, Scala or Ruby, to name a few, but
> definitely not ADA.
>

SQL/PSM is mix near Modula -- like Lua

But integrated JavaScript can be good idea

And Lua too - it is faster than Javascript with less overhead, but with
significantly less community.

Pavel

>
> Regards,
>
> Álvaro
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 02/09/14 11:44, Pavel Stehule wrote:
>
>
>
>
>
> For 9.4, we have the media already saying "Postgres has NoSQL
> capabilities" (which is only partially true). For x.y we could
> have the media saying "Postgres adds Oracle compatibility" (which
> would be only partially true). But that brings a lot of users to
> postgres, and that helps us all.
>
>
> Partial true can enforce so lot of people will hate postgres too.
> False promises are wrong

Then let's stop talking about postgres being NoSQL. NoSQL is
basically "schema-less" (really bad name) plus "infinite scalability"
(which basically means transparent sharding). We fail to provide the
latter very clearly...

Álvaro

2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es>:

>
> On 02/09/14 11:31, Pavel Stehule wrote:
>
>
>
>
> 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es>:
>
>>
>> On 02/09/14 05:24, Craig Ringer wrote:
>>
>>> I couldn't disagree more.
>>>
>>> If we were to implement anything, it'd be PL/PSM
>>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>>> quirky as anything else the SQL committee has brought forth, but it's at
>>> least a standard(ish) language.
>>>
>> So we'd choose a bizarre and quirky language instead of anything
>> better just because it's standard. I'm sure current and prospective users
>> will surely prefer a bizarre and quirky language that is standard approved,
>> rather than a modern, comfortable, easy-to-use, that is not embodied by the
>> ISO. No doubt ^_^
>>
>>
> SQL/PSM is used in >>>DB2<<<, >>>Sybase Anywhere<<<, MySQL,
>
>
> That's a way better argument that it's standard :)))
>
> Still, I think postgres is in the position of attracting more Oracle
> than DB2+Sybase+MySQL users
>

Not all can be happy :)

We can implement SQL/PSM in conformity with ANSI SQL. But we cannot to
implement PL/SQL be in 20% compatible with oracle - Aggegates, pipe
functions, collections, without rewriting lot code.

I remember lot of projects that promises compatibility with Oracle based on
Firebird -- all are dead. Now situation is little bit different - there are
big press for migration from Oracle, but Oracle is too big monster.

Pavel

>
> Álvaro
>
>

On 02/09/14 11:56, Pavel Stehule wrote:
>
>
>
> 2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es
> <mailto:aht(at)nosys(dot)es>>:
>
>
> On 02/09/14 11:31, Pavel Stehule wrote:
>>
>>
>>
>> 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa <aht(at)nosys(dot)es
>> <mailto:aht(at)nosys(dot)es>>:
>>
>>
>> On 02/09/14 05:24, Craig Ringer wrote:
>>
>> I couldn't disagree more.
>>
>> If we were to implement anything, it'd be PL/PSM
>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as
>> bizarre and
>> quirky as anything else the SQL committee has brought
>> forth, but it's at
>> least a standard(ish) language.
>>
>> So we'd choose a bizarre and quirky language instead of
>> anything better just because it's standard. I'm sure current
>> and prospective users will surely prefer a bizarre and quirky
>> language that is standard approved, rather than a modern,
>> comfortable, easy-to-use, that is not embodied by the ISO. No
>> doubt ^_^
>>
>>
>> SQL/PSM is used in >>>DB2<<<, >>>Sybase Anywhere<<<, MySQL,
>
> That's a way better argument that it's standard :)))
>
> Still, I think postgres is in the position of attracting more
> Oracle than DB2+Sybase+MySQL users
>
>
> Not all can be happy :)
>
> We can implement SQL/PSM in conformity with ANSI SQL. But we cannot to
> implement PL/SQL be in 20% compatible with oracle - Aggegates, pipe
> functions, collections, without rewriting lot code.
>
> I remember lot of projects that promises compatibility with Oracle
> based on Firebird -- all are dead. Now situation is little bit
> different - there are big press for migration from Oracle, but Oracle
> is too big monster.

OK. Thanks for all the info I was missing about this complexity, I
see that it goes well beyond the syntax thing.

However, I'd insist that this should be IMHO a big priority, and
I'd set it as a long-term goal. Even better if it could have a phased
approach, that would make a lot of people happier (targeting the most
used functionality). I'm sure pushing us to implement those missing
features would also be really good, too.

In the meantime, having another language (probably not plpgsql2)
that is modern and appealing to many users would be a very nice win.

Regards,

Álvaro

On 9/2/14 11:40 AM, Álvaro Hernández Tortosa wrote:
> If we are to have another plpgsql-like language (like plpgsql2) and
> we could design it so it would attract many many users (let's not forget
> that Oracle may have around two orders of magnitude more users than pg),
> that would benefit us all greatly. Even if not perfect. Even if it is a
> longer project which spans more than one release. But just having the
> syntax (or most of it, maybe avoiding some complex unimplemented
> postgres features, if that required a huge effort) is a big win.

Have you looked at
http://www.postgresql.org/docs/9.3/static/plpgsql-porting.html already?
As far as I can tell, that already *is* the case as far as the
language goes. It seems to me that most of the stuff that's different
between the two are things that are out of the control of the language
(no autonomous transactions, function source code in a literal etc.)

> For 9.4, we have the media already saying "Postgres has NoSQL
> capabilities" (which is only partially true). For x.y we could have the
> media saying "Postgres adds Oracle compatibility" (which would be only
> partially true). But that brings a lot of users to postgres, and that
> helps us all.

This would be a horrible, horrible lie.

> If on the other hand we resign from attracting Oracle users, in a
> moment where non-Oracle databases are fighting for them..... and we lose
> here.... well, let's at least have a very compelling, attractive,
> in-core, blessed, language. Even disliking it myself, PL/JavaScript
> would be my #1 candidate there.

The best part about PL/PgSQL is the seamless integration with SQL. You
can put an SQL expression pretty much anywhere. How well would that
work if the "framework" was Javascript instead of the ADA-like body that
both PL/SQL and PL/PgSQL implement?

.marko

On 02/09/14 12:46, Marko Tiikkaja wrote:
> On 9/2/14 11:40 AM, Álvaro Hernández Tortosa wrote:
>> If we are to have another plpgsql-like language (like plpgsql2)
>> and
>> we could design it so it would attract many many users (let's not forget
>> that Oracle may have around two orders of magnitude more users than pg),
>> that would benefit us all greatly. Even if not perfect. Even if it is a
>> longer project which spans more than one release. But just having the
>> syntax (or most of it, maybe avoiding some complex unimplemented
>> postgres features, if that required a huge effort) is a big win.
>
> Have you looked at
> http://www.postgresql.org/docs/9.3/static/plpgsql-porting.html already?

Precisely this page shows some indications of examples of things
that could be done at a language level that would make it way easier to
port from PL/SQL (if you don't use that unsupported stuff). At least for
that, if the syntax is exactly the same, it could make things much more
comfortable (I'm not aiming for a 0-effort port, at least in first
place, but to get the 80% or 60% easier than now).

> As far as I can tell, that already *is* the case as far as the
> language goes. It seems to me that most of the stuff that's different
> between the two are things that are out of the control of the language
> (no autonomous transactions, function source code in a literal etc.)

Maybe it would be interesting to analyze:

- What it's impossible to have right now in postgres
- What can be implemented in a different way, but that would work in
postgres
- What could be somehow emulated

And adapt the syntax as much as possible to aim for the biggest
compatibility possible.

>
>> For 9.4, we have the media already saying "Postgres has NoSQL
>> capabilities" (which is only partially true). For x.y we could have the
>> media saying "Postgres adds Oracle compatibility" (which would be only
>> partially true). But that brings a lot of users to postgres, and that
>> helps us all.
>
> This would be a horrible, horrible lie.

Certainly not more horrible than today's "PostgreSQL has NoSQL".
Despite that, I'm not saying I'd lie. I'd say what the media would say,
which is completely different.

>
>> If on the other hand we resign from attracting Oracle users, in a
>> moment where non-Oracle databases are fighting for them..... and we lose
>> here.... well, let's at least have a very compelling, attractive,
>> in-core, blessed, language. Even disliking it myself, PL/JavaScript
>> would be my #1 candidate there.
>
> The best part about PL/PgSQL is the seamless integration with SQL.
> You can put an SQL expression pretty much anywhere. How well would
> that work if the "framework" was Javascript instead of the ADA-like
> body that both PL/SQL and PL/PgSQL implement?

SQL integration is a must in a PL/* language, that's for sure. But
leveraging a well known language, tooling, and, specially, external
libraries/ecosystem is a much bigger win. Specially if all the languages
that I know of are capable (with more or less effort) to integrate SQL.
So maybe JavaScript with a way of integrating SQL would be preferable IMO.

Regards,

Álvaro

On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> What we can do better?
>
> 1. we can implement a conditional RAISE
>
> DELETE FROM tab WHERE xx = somevar;
> GET DIAGNOSTICS rc = ROW_COUNT;
> RAISE EXCEPTION 'some' WHEN rc <> 0;
>
> It is relatively natural and we use similar construct in CONTINUE statement.
>
> 2. What can be next? We can implement some idiom (shortcut) for GET
> DIAGNOSTICS
>
> DELETE FROM tab WHERE xx = somevar;
> RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;
>
> 3. What next? Maybe some notations -
>
> -- ** ensure_exact_one_row
> DELETE FROM tab WHERE xx = somevar;
>
> But default will be same as in plain SQL.

All three suggestions are either too verbose, ugly or hackish.
I write too much code every day in PL/pgSQL to find any other solution
than the cleanest and simplest to be acceptable.
I reckon there are those who mostly use the language to create
aggregated reports or to run some kind of batch jobs.
But I use it almost exlusively for OLTP, and then you most often
update a single row, and if 0 or >1 rows are affected, it's an error.
Therefore, I wish the syntax for the most common use case to be as
clean as possible, and there is nothing cleaner than plain UPDATE.

Also, when showing a beginner the power of PL/pgSQL, it cannot be
acceptable to have to write two rows to do something as simple as an
update. All the suggestions above range between 2-3 rows (for DELETE,
but I guess the syntax would be the same for UPDATE).

For an in-depth discussion on this subject, please see
http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/

I have no good ideas though on what the syntax would look like to
allow zero rows or multiple rows for an UPDATE though.

It's much harder to come up with things to *add* to a syntax than what
obvious ugliness you want to *remove*.

If I had to guess though, I would think something in the end of the
UPDATE command like a new keyword, could work. It wouldn't mess up the
syntax too much, and wouldn't require an extra line of code.

I strongly feel we should give a plain UPDATE without any extra lines
of code or special syntax a default behaviour, which is different from
"accept any number of affected rows".
My definitive vote is to throw an error if not exactly 1 row was
affected, and to provide a nice syntax to allow the other use cases.
Right now it's the other way around, we never throw an error, and
*always* have to check how many rows were affected. That means we
*always* get both more lines of code and also uglier code in our
applications, than we would if we optimized for the most common use
case.

On 09/02/2014 03:16 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> What we can do better?
>>
>> 1. we can implement a conditional RAISE
>>
>> DELETE FROM tab WHERE xx = somevar;
>> GET DIAGNOSTICS rc = ROW_COUNT;
>> RAISE EXCEPTION 'some' WHEN rc <> 0;
>>
>> It is relatively natural and we use similar construct in CONTINUE statement.
>>
>> 2. What can be next? We can implement some idiom (shortcut) for GET
>> DIAGNOSTICS
>>
>> DELETE FROM tab WHERE xx = somevar;
>> RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;
>>
>> 3. What next? Maybe some notations -
>>
>> -- ** ensure_exact_one_row
>> DELETE FROM tab WHERE xx = somevar;
>>
>> But default will be same as in plain SQL.
>
> All three suggestions are either too verbose, ugly or hackish.
> I write too much code every day in PL/pgSQL to find any other solution
> than the cleanest and simplest to be acceptable.
> I reckon there are those who mostly use the language to create
> aggregated reports or to run some kind of batch jobs.
> But I use it almost exlusively for OLTP, and then you most often
> update a single row, and if 0 or >1 rows are affected, it's an error.
> Therefore, I wish the syntax for the most common use case to be as
> clean as possible, and there is nothing cleaner than plain UPDATE.
>
> Also, when showing a beginner the power of PL/pgSQL, it cannot be
> acceptable to have to write two rows to do something as simple as an
> update. All the suggestions above range between 2-3 rows (for DELETE,
> but I guess the syntax would be the same for UPDATE).
>
> For an in-depth discussion on this subject, please see
> http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/

In the mailing list thread that you linked there, Tom suggested using
"STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error
(http://www.postgresql.org/message-id/16397.1356106923@sss.pgh.pa.us).
What happened to that proposal?

- Heikki

2014-09-02 14:16 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > What we can do better?
> >
> > 1. we can implement a conditional RAISE
> >
> > DELETE FROM tab WHERE xx = somevar;
> > GET DIAGNOSTICS rc = ROW_COUNT;
> > RAISE EXCEPTION 'some' WHEN rc <> 0;
> >
> > It is relatively natural and we use similar construct in CONTINUE
> statement.
> >
> > 2. What can be next? We can implement some idiom (shortcut) for GET
> > DIAGNOSTICS
> >
> > DELETE FROM tab WHERE xx = somevar;
> > RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;
> >
> > 3. What next? Maybe some notations -
> >
> > -- ** ensure_exact_one_row
> > DELETE FROM tab WHERE xx = somevar;
> >
> > But default will be same as in plain SQL.
>
> All three suggestions are either too verbose, ugly or hackish.
>

It is main problem for me. I am thinking so verbosity is important. If it
is ugly, cannot to say. It is subjective.

> I write too much code every day in PL/pgSQL to find any other solution
> than the cleanest and simplest to be acceptable.
> I reckon there are those who mostly use the language to create
> aggregated reports or to run some kind of batch jobs.
> But I use it almost exlusively for OLTP, and then you most often
> update a single row, and if 0 or >1 rows are affected, it's an error.
>

It is valid only for UPDATE, not for DELETE. You can delete with FK and it
is common operation.

> Therefore, I wish the syntax for the most common use case to be as
> clean as possible, and there is nothing cleaner than plain UPDATE.
>
> Also, when showing a beginner the power of PL/pgSQL, it cannot be
> acceptable to have to write two rows to do something as simple as an
> update. All the suggestions above range between 2-3 rows (for DELETE,
> but I guess the syntax would be the same for UPDATE).
>
> For an in-depth discussion on this subject, please see
> http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/
>

It is way how to do COBOL from plpgsql. I am against it. Start to develop
new language what will support fast development, but it is wrong way for
plpgsql - and It is out my interest

>
> I have no good ideas though on what the syntax would look like to
> allow zero rows or multiple rows for an UPDATE though.
>
> It's much harder to come up with things to *add* to a syntax than what
> obvious ugliness you want to *remove*.
>
> If I had to guess though, I would think something in the end of the
> UPDATE command like a new keyword, could work. It wouldn't mess up the
> syntax too much, and wouldn't require an extra line of code.
>
> I strongly feel we should give a plain UPDATE without any extra lines
> of code or special syntax a default behaviour, which is different from
> "accept any number of affected rows".
> My definitive vote is to throw an error if not exactly 1 row was
> affected, and to provide a nice syntax to allow the other use cases.
> Right now it's the other way around, we never throw an error, and
> *always* have to check how many rows were affected. That means we
> *always* get both more lines of code and also uglier code in our
> applications, than we would if we optimized for the most common use
> case.
>

On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
>
> On 02/09/14 11:34, Mark Kirkwood wrote:
>> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
>>>
>>> On 02/09/14 05:24, Craig Ringer wrote:
>>>> I couldn't disagree more.
>>>>
>>>> If we were to implement anything, it'd be PL/PSM
>>>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>>>> quirky as anything else the SQL committee has brought forth, but
>>>> it's at
>>>> least a standard(ish) language.
>>> So we'd choose a bizarre and quirky language instead of anything
>>> better just because it's standard. I'm sure current and prospective
>>> users will surely prefer a bizarre and quirky language that is standard
>>> approved, rather than a modern, comfortable, easy-to-use, that is not
>>> embodied by the ISO. No doubt ^_^
>>>
>>
>> Well there is the risk that by randomly adding new syntax to PL/pgSQL
>> we turn it in a bizarre and quirky *non standard* language. Part of
>> the attraction of PL/pgsql is that it is Ada like - if we break that
>> too much then...well...that would be bad. So I think a careful
>> balance is needed, to add new features that keep the spirit of the
>> original language.
>>
>
> I agree. I think I haven't suggested adding new syntax to
> pl/pgsql. But having its syntax similar to ADA is IMHO not something
> good. I'm sure few prospective postgres users would be compelled to
> that. They are compelled about JavaScript, python, Scala or Ruby, to
> name a few, but definitely not ADA.

Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably
the world's first programmer. If you're not familiar with modern Ada,
let me recommend the newly published "Programming in Ada 2012" by John
Barnes. But I digress.

JavaScript would actually be quite a good alternative. However, using it
involves something others have objected to, namely calling SQL via a
function call. It's true that plpgsql lets you call SQL commands without
explicitly invoking SPI. OTOH, it actually relies on SPI under the hood
a lot more that other PLs, which I have little doubt is responsible for
timings like this:

andrew=# do $$ declare x int = 1; i int = 1; begin while i <
10000000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 459999955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i < 10000000) { i += 1;
x += 46; } plv8.elog(NOTICE, "x = " + x); $$ language plv8;
NOTICE: x = 459999955
DO
Time: 27.976 ms

But I'm not suggesting we should implement a Javascript PL in core either.

Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a
GUC could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.

cheers

andrew

On 9/2/14 2:29 PM, Heikki Linnakangas wrote:
> In the mailing list thread that you linked there, Tom suggested using
> "STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error
> (http://www.postgresql.org/message-id/16397.1356106923@sss.pgh.pa.us).
> What happened to that proposal?

http://www.postgresql.org/message-id/27477.1361916222@sss.pgh.pa.us

I can't find Peter's email right now, but basically nobody liked the
suggestion in the end.

.marko

If PL/Javascript is a serious consideration, how will int64 and numeric be
handled?

Thanks,

Ryan Pedela
Datalanche CEO, co-founder
www.datalanche.com
rpedela(at)datalanche(dot)com
513-571-6837

On Tue, Sep 2, 2014 at 6:38 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
>
>>
>> On 02/09/14 11:34, Mark Kirkwood wrote:
>>
>>> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
>>>
>>>>
>>>> On 02/09/14 05:24, Craig Ringer wrote:
>>>>
>>>>> I couldn't disagree more.
>>>>>
>>>>> If we were to implement anything, it'd be PL/PSM
>>>>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>>>>> quirky as anything else the SQL committee has brought forth, but it's
>>>>> at
>>>>> least a standard(ish) language.
>>>>>
>>>> So we'd choose a bizarre and quirky language instead of anything
>>>> better just because it's standard. I'm sure current and prospective
>>>> users will surely prefer a bizarre and quirky language that is standard
>>>> approved, rather than a modern, comfortable, easy-to-use, that is not
>>>> embodied by the ISO. No doubt ^_^
>>>>
>>>>
>>> Well there is the risk that by randomly adding new syntax to PL/pgSQL we
>>> turn it in a bizarre and quirky *non standard* language. Part of the
>>> attraction of PL/pgsql is that it is Ada like - if we break that too much
>>> then...well...that would be bad. So I think a careful balance is needed, to
>>> add new features that keep the spirit of the original language.
>>>
>>>
>> I agree. I think I haven't suggested adding new syntax to pl/pgsql.
>> But having its syntax similar to ADA is IMHO not something good. I'm sure
>> few prospective postgres users would be compelled to that. They are
>> compelled about JavaScript, python, Scala or Ruby, to name a few, but
>> definitely not ADA.
>>
>
>
> Just as a small nit pick - the name of the language is not ADA, but Ada.
> It isn't an acronym. The language is named after Ada Lovelace, arguably the
> world's first programmer. If you're not familiar with modern Ada, let me
> recommend the newly published "Programming in Ada 2012" by John Barnes. But
> I digress.
>
> JavaScript would actually be quite a good alternative. However, using it
> involves something others have objected to, namely calling SQL via a
> function call. It's true that plpgsql lets you call SQL commands without
> explicitly invoking SPI. OTOH, it actually relies on SPI under the hood a
> lot more that other PLs, which I have little doubt is responsible for
> timings like this:
>
> andrew=# do $$ declare x int = 1; i int = 1; begin while i <
> 10000000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
> %',x; end; $$;
> NOTICE: x = 459999955
> DO
> Time: 13222.195 ms
> andrew=# do $$ var x = 1; var i = 1; while (i < 10000000) { i += 1;
> x += 46; } plv8.elog(NOTICE, "x = " + x); $$ language plv8;
> NOTICE: x = 459999955
> DO
> Time: 27.976 ms
>
> But I'm not suggesting we should implement a Javascript PL in core either.
>
> Finally, +1 to Tom's suggestion upthread that we implement different
> behaviours via pragmas rather than some new offshoot language. Maybe a GUC
> could specify a default set of such pragmas, so you wouldn't need to
> decorate every function with them.
>
> cheers
>
> andrew
>
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 09/02/2014 08:41 AM, Ryan Pedela wrote:
> If PL/Javascript is a serious consideration, how will int64 and
> numeric be handled?
>
>

Please don't top-post on the PostgreSQL lists. See
<http://idallen.com/topposting.html>

Unfortunately, I think the short answer is "not very well". In theory we
cauld add in new types to a Javascript interpreter to handle them, but
that would still leave you scrambling to handle user defined types.

One of the advantages of plpgsql is that it can handle any Postgres data
type without having to do anything special.

The truth is that different PLs meet different needs and have different
strengths and weaknesses.

cheers

andrew

2014-09-02 14:38 GMT+02:00 Andrew Dunstan <andrew(at)dunslane(dot)net>:

>
> On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
>
>>
>> On 02/09/14 11:34, Mark Kirkwood wrote:
>>
>>> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
>>>
>>>>
>>>> On 02/09/14 05:24, Craig Ringer wrote:
>>>>
>>>>> I couldn't disagree more.
>>>>>
>>>>> If we were to implement anything, it'd be PL/PSM
>>>>> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
>>>>> quirky as anything else the SQL committee has brought forth, but it's
>>>>> at
>>>>> least a standard(ish) language.
>>>>>
>>>> So we'd choose a bizarre and quirky language instead of anything
>>>> better just because it's standard. I'm sure current and prospective
>>>> users will surely prefer a bizarre and quirky language that is standard
>>>> approved, rather than a modern, comfortable, easy-to-use, that is not
>>>> embodied by the ISO. No doubt ^_^
>>>>
>>>>
>>> Well there is the risk that by randomly adding new syntax to PL/pgSQL we
>>> turn it in a bizarre and quirky *non standard* language. Part of the
>>> attraction of PL/pgsql is that it is Ada like - if we break that too much
>>> then...well...that would be bad. So I think a careful balance is needed, to
>>> add new features that keep the spirit of the original language.
>>>
>>>
>> I agree. I think I haven't suggested adding new syntax to pl/pgsql.
>> But having its syntax similar to ADA is IMHO not something good. I'm sure
>> few prospective postgres users would be compelled to that. They are
>> compelled about JavaScript, python, Scala or Ruby, to name a few, but
>> definitely not ADA.
>>
>
>
> Just as a small nit pick - the name of the language is not ADA, but Ada.
> It isn't an acronym. The language is named after Ada Lovelace, arguably the
> world's first programmer. If you're not familiar with modern Ada, let me
> recommend the newly published "Programming in Ada 2012" by John Barnes. But
> I digress.
>
> JavaScript would actually be quite a good alternative. However, using it
> involves something others have objected to, namely calling SQL via a
> function call. It's true that plpgsql lets you call SQL commands without
> explicitly invoking SPI. OTOH, it actually relies on SPI under the hood a
> lot more that other PLs, which I have little doubt is responsible for
> timings like this:
>
> andrew=# do $$ declare x int = 1; i int = 1; begin while i <
> 10000000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
> %',x; end; $$;
> NOTICE: x = 459999955
> DO
> Time: 13222.195 ms
> andrew=# do $$ var x = 1; var i = 1; while (i < 10000000) { i += 1;
> x += 46; } plv8.elog(NOTICE, "x = " + x); $$ language plv8;
> NOTICE: x = 459999955
> DO
> Time: 27.976 ms
>

this test is unfair to plpgsql, and you know it well :)

any operations over native types will be faster than in plpgsql, although
this difference is maybe too much. Doesn't use --enable-cassert ?

>
> But I'm not suggesting we should implement a Javascript PL in core either.
>
> Finally, +1 to Tom's suggestion upthread that we implement different
> behaviours via pragmas rather than some new offshoot language. Maybe a GUC
> could specify a default set of such pragmas, so you wouldn't need to
> decorate every function with them.
>
> cheers
>
> andrew
>
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 2014-09-02 14:41:03 +0200, Marko Tiikkaja wrote:
> On 9/2/14 2:29 PM, Heikki Linnakangas wrote:
> >In the mailing list thread that you linked there, Tom suggested using
> >"STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error
> >(http://www.postgresql.org/message-id/16397.1356106923@sss.pgh.pa.us).
> >What happened to that proposal?
>
> http://www.postgresql.org/message-id/27477.1361916222@sss.pgh.pa.us
>
> I can't find Peter's email right now, but basically nobody liked the
> suggestion in the end.

Perhaps we need the ONE ROW operatation ;)

ONE ROW UPDATE ...;

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Joel Jacobson <joel(at)trustly(dot)com> wrote:

> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify
> exactly 1 row, as that's the most common use-case, and provide
> alternative syntax to modify multiple or zero rows.

I just embarked on wading through the 99 messages (so far) on this
thread, so my apologies if this has already been addressed -- but I
wanted to register a strong objection to making this the default in
any rewrite.  If we want to support a setting or a statement option
for it, fine; but in my personal experience in a production
environment with thousands of plpgsql functions, most functions
written to deal with one row at a time were orders of magnitude
slower than they needed to be -- I spent a lot of my time rewriting
them to use set logic so that they could benefit from the
optimizer's attention.  Getting people to write things in a
declarative style in the first place was difficult because so many
of the programmers were so attached to the imperative style of
coding; making it more difficult for people to Do The Right Thing
is a bad idea IMO.

As a side note, of the many times I rewrote long functions which
looped through individual rows, I would estimate that 80% of them
had subtle bugs which were fixed by changing them to set logic.
Sure, some of those would have caused run-time errors rather than
plausible-but-incorrect results with the change you suggest, but
far from all of them.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 09/02/2014 09:08 AM, Pavel Stehule wrote:
>
>
> JavaScript would actually be quite a good alternative. However,
> using it involves something others have objected to, namely
> calling SQL via a function call. It's true that plpgsql lets you
> call SQL commands without explicitly invoking SPI. OTOH, it
> actually relies on SPI under the hood a lot more that other PLs,
> which I have little doubt is responsible for timings like this:
>
> andrew=# do $$ declare x int = 1; i int = 1; begin while i <
> 10000000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
> %',x; end; $$;
> NOTICE: x = 459999955
> DO
> Time: 13222.195 ms
> andrew=# do $$ var x = 1; var i = 1; while (i < 10000000) { i += 1;
> x += 46; } plv8.elog(NOTICE, "x = " + x); $$ language plv8;
> NOTICE: x = 459999955
> DO
> Time: 27.976 ms
>
>
> this test is unfair to plpgsql, and you know it well :)
>
> any operations over native types will be faster than in plpgsql,
> although this difference is maybe too much. Doesn't use
> --enable-cassert ?

It's not unfair, and no it isn't using cassert. This was from a
production grade server.

PLV8 has its own issues (see discussion elsewhere in this thread re
int64 and numeric). It's just that speed isn't one of them :-)

Please note that I'm not unhappy with plpgsql. I have my own small list
of things that I would like improved, but there isn't very much that
bugs me about it.