Thread: PL/pgSQL 2

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@2ndquadrant.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
providealternative 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 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 Mon, Sep 1, 2014 at 12:32 PM, Hannu Krosing <hannu@2ndquadrant.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 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

On Mon, Sep 1, 2014 at 12:55 PM, Andres Freund <andres@2ndquadrant.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.

On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel.stehule@gmail.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.

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@trustly.com>:
>
>> On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule <pavel.stehule@gmail.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.stehule@gmail.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-codeLooking 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.stehule@gmail.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

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

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

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@2ndquadrant.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.stehule@gmail.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@2ndquadrant.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

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@2ndquadrant.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.stehule@gmail.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@2ndquadrant.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@2ndquadrant.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

On Mon, Sep 1, 2014 at 4:41 PM, Andres Freund <andres@2ndquadrant.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@2ndquadrant.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 <

> craig@

> > 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@2ndquadrant.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.stehule@gmail.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@sss.pgh.pa.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.stehule@gmail.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

Joel Jacobson <joel@trustly.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

Álvaro Hernández Tortosa <aht@nosys.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:
> Álvaro Hernández Tortosa <aht@nosys.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

On Mon, Sep 1, 2014 at 8:38 PM, Tom Lane <tgl@sss.pgh.pa.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@nosys.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.

2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa <aht@nosys.es>:

On 01/09/14 20:42, Tom Lane wrote:

Álvaro Hernández Tortosa <aht@nosys.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.

On 01/09/14 21:52, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa <aht@nosys.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 <

> aht@

> > 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 <
>> aht@
>> > 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@sss.pgh.pa.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/pgSQLoccasionally, 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
witha knife compared to other non-PL/pgSQL languages I work in.  Mostly my hate is directed at the silly quoting.  But
ithas 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
wantto learn as no one else seems to use it (in my world anyway).  perl is the only possibility left and again, no one
inmy world is using Perl and it’s not clear if there is a performance penalty.  The docs say the best language for
performanceis 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.
Ofcourse 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
unnecessarystuff that sucks the life out of programmer productivity.  And this should be very much a concern of the
professionalsthat 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
willover 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@sss.pgh.pa.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/pgSQLoccasionally, 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
legwith a knife compared to other non-PL/pgSQL languages I work in.  Mostly my hate is directed at the silly quoting.
Butit 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
classlanguage, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and
don’twant to learn as no one else seems to use it (in my world anyway).  perl is the only possibility left and again,
noone in my world is using Perl and it’s not clear if there is a performance penalty.  The docs say the best language
forperformance 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
unnecessarystuff that sucks the life out of programmer productivity.  And this should be very much a concern of the
professionalsthat 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 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
classlanguage, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and
don’twant to learn as no one else seems to use it (in my world anyway).  perl is the only possibility left and again,
noone in my world is using Perl and it’s not clear if there is a performance penalty.  The docs say the best language
forperformance 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
unnecessarystuff that sucks the life out of programmer productivity.  And this should be very much a concern of the
professionalsthat 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 09/01/2014 11:19 PM, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer <craig@2ndquadrant.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@2ndquadrant.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@2ndquadrant.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@vmware.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!

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

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@2ndquadrant.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

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:25 GMT+02:00 Álvaro Hernández Tortosa <aht@nosys.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,

    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

2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa <aht@nosys.es>:

On 02/09/14 11:31, Pavel Stehule wrote:

2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa <aht@nosys.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.

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
caseas 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.stehule@gmail.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.stehule@gmail.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

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;
raisenotice ' 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

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

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

A few years ago I was largely instrumental in building an entire billing 
system, including some very complex tax rating, for a small Telco, using 
plpgsql plus a tiny bit of plperlu glue where we needed unsafe 
operations. It was quite fast enough - see my talk at pgopen a few years 
back.


cheers

andrew

On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Joel Jacobson <joel@trustly.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.

The common use-case I have in mind is when you have a function which
takes some kind of ID as an input param, which maps to a primary key
in some table, which you want to update.
If the where-clause would be incorrect and the update would update all
rows in the table, that would be a disaster, which is what I want to
prevent.
I think the benefit of a secure and convenient way of updating exactly
1 row outweights the reduced convenience of updating multiple rows
when you really want to update multiple rows.

Compare this to the normal psql prompt. How many million dollars would
you say the total cost would be for mistakes where someone forgets the
WHERE-clause of an UPDATE or a DELETE? :-)
It's the same type of mistake I want to prevent from in a convenient
way, and there is nothing more convenient than the default behavour.
That also means *all* users will get that behaviour even if they don't
explicitly request it, which is a good thing, because then they are
protected against the danger of not knowing how to make sure it
updated/deleted only one row.

On Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> 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?

From the STRICT mail thread, this was the last post:

>"Marko Tiikkaja" <marko@joh.to> writes:
>> If I'm counting correctly, we have four votes for this patch and two votes
>> against it.
>> Any other opinions?
>
>FWIW, I share Peter's poor opinion of this syntax.  I can see the
>appeal of not having to write an explicit check of the rowcount
>afterwards, but that appeal is greatly weakened by the strange syntax.
>(IOW, if you were counting me as a + vote, that was only a vote for
>the concept --- on reflection I don't much like this implementation.)
>regards, tom lane

I think it's much better to make it the default behaviour in plpgsql2
than to add a new syntax to plpgsql,
because then we don't have to argue what to call the keyword or where to put it.

On Tue, Sep 2, 2014 at 2:30 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 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

Are you saying COBOL by default update's one row and throws an error otherwise?
In what way could *not* changing the syntax of a standard UPDATE
command, but changing the *behaviour*, in plpgsql2, be deemed to be a
step in the COBOL direction?

I don't want a new language, I love plpgsql, I just want to love it a
bit more, I don't think I have to clarify on that any more.

On 09/02/2014 04:32 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> 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?
>
>  From the STRICT mail thread, this was the last post:
>
>> "Marko Tiikkaja" <marko@joh.to> writes:
>>> If I'm counting correctly, we have four votes for this patch and two votes
>>> against it.
>>> Any other opinions?
>>
>> FWIW, I share Peter's poor opinion of this syntax.  I can see the
>> appeal of not having to write an explicit check of the rowcount
>> afterwards, but that appeal is greatly weakened by the strange syntax.
>> (IOW, if you were counting me as a + vote, that was only a vote for
>> the concept --- on reflection I don't much like this implementation.)
>> regards, tom lane
>
> I think it's much better to make it the default behaviour in plpgsql2
> than to add a new syntax to plpgsql,
> because then we don't have to argue what to call the keyword or where to put it.

Then you'll have to argue what the *other* syntax should look like. And 
not everyone agrees on the default either, see Kevin's email. Designing 
a new language is going to be an uphill battle, even more so than 
enhancing current plpgsql.

- Heikki

On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 09/02/2014 04:32 PM, Joel Jacobson wrote:
>> I think it's much better to make it the default behaviour in plpgsql2
>> than to add a new syntax to plpgsql,
>> because then we don't have to argue what to call the keyword or where to
>> put it.
>
>
> Then you'll have to argue what the *other* syntax should look like. And not
> everyone agrees on the default either, see Kevin's email. Designing a new
> language is going to be an uphill battle, even more so than enhancing
> current plpgsql.

Any ideas on what the *other* syntax could look like?

On 09/02/2014 04:52 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 09/02/2014 04:32 PM, Joel Jacobson wrote:
>>> I think it's much better to make it the default behaviour in plpgsql2
>>> than to add a new syntax to plpgsql,
>>> because then we don't have to argue what to call the keyword or where to
>>> put it.
>>
>>
>> Then you'll have to argue what the *other* syntax should look like. And not
>> everyone agrees on the default either, see Kevin's email. Designing a new
>> language is going to be an uphill battle, even more so than enhancing
>> current plpgsql.
>
> Any ideas on what the *other* syntax could look like?

Well, I'm in the camp that the current default is fine...

- Heikki

On 9/2/14 3:52 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 09/02/2014 04:32 PM, Joel Jacobson wrote:
>>> I think it's much better to make it the default behaviour in plpgsql2
>>> than to add a new syntax to plpgsql,
>>> because then we don't have to argue what to call the keyword or where to
>>> put it.
>>
>>
>> Then you'll have to argue what the *other* syntax should look like. And not
>> everyone agrees on the default either, see Kevin's email. Designing a new
>> language is going to be an uphill battle, even more so than enhancing
>> current plpgsql.
>
> Any ideas on what the *other* syntax could look like?

When I've played around with the idea of fixing PL/PgSQL in my head, 
what I had in mind is that UPDATE and DELETE not affecting exactly one 
row raises an exception, unless PERFORM is used.  PERFORM would set a 
special variable (e.g. ROW_COUNT) which can be consulted after the 
operation.

For example:

UPDATE foo WHERE bar = 1;  -- must affect exactly one row
PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows
IF row_count > 1 THEN  RAISE EXCEPTION 'oh no';
END IF;

This, obviously, requires us to get rid of the requirement for PERFORM 
today, which I see as a win as well.


.marko

On Tue, Sep 2, 2014 at 3:58 PM, Marko Tiikkaja <marko@joh.to> wrote:
> When I've played around with the idea of fixing PL/PgSQL in my head, what I
> had in mind is that UPDATE and DELETE not affecting exactly one row raises
> an exception, unless PERFORM is used.  PERFORM would set a special variable
> (e.g. ROW_COUNT) which can be consulted after the operation.
>
> For example:
>
> UPDATE foo WHERE bar = 1;  -- must affect exactly one row
> PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows
> IF row_count > 1 THEN
>   RAISE EXCEPTION 'oh no';
> END IF;
>
> This, obviously, requires us to get rid of the requirement for PERFORM
> today, which I see as a win as well.

I don't like rebranding the PERFORM command, as that would require all
existing code with PERFORM commands to be changed.

That also still requires 4 rows for some all other use-cases than 1
row affected, if all you want is a general error in case your
expectations of rows affected were not met.

I think with a single line of UPDATE command, you should be forced to
indicate you want something else than 1 row affected, and if your
expectations are not met, you should get the error on the UPDATE
command, not having to check a variable on the next line of code.

I therefore think, since we don't have to be 100% backwards
compatible, it's OK and a good thing to introduce some new keyword to
UPDATE (and DELETE). I have no ideas on what keyword(s) though.

Marko Tiikkaja <marko@joh.to> writes:
> For example:

> UPDATE foo WHERE bar = 1;  -- must affect exactly one row
> PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows

FWIW, I agree with the position that this would be a completely wrong
thing to do.  UPDATE should work like it does in plain SQL.  If you want
a restriction to "exactly one row", that needs to be a modifier.

I take no position on how the modifier should be spelled, though.
        regards, tom lane

On 2014-09-02 10:21:50 -0400, Tom Lane wrote:
> Marko Tiikkaja <marko@joh.to> writes:
> > For example:
> 
> > UPDATE foo WHERE bar = 1;  -- must affect exactly one row
> > PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows
> 
> FWIW, I agree with the position that this would be a completely wrong
> thing to do.  UPDATE should work like it does in plain SQL.  If you want
> a restriction to "exactly one row", that needs to be a modifier.
> 
> I take no position on how the modifier should be spelled, though.

Personally I think 
ONE ROW UPDATE ...
reads nicely and SQL-ish. But it's not very expandable to other numbers.

Greetings,

Andres Freund

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

Joel Jacobson <joel@trustly.com> wrote:
> On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Joel Jacobson <joel@trustly.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.

>> 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.
>
> The common use-case I have in mind is when you have a function
> which takes some kind of ID as an input param, which maps to a
> primary key in some table, which you want to update.

In that case FOUND works just fine.  A primary key value can't have
more than one matching row.

> If the where-clause would be incorrect and the update would
> update all rows in the table, that would be a disaster, which is
> what I want to prevent.

By the time you find out that the number of rows affected is every
row in the table, you have horribly bloated the table and all its
indexes.  Causing a DML statement to abort when it sees a second
row is a completely different issue than what I (and I suspect most
others on the list) thought we were talking about, and would need
to affect far more than the PL.

> I think the benefit of a secure and convenient way of updating
> exactly 1 row outweights the reduced convenience of updating
> multiple rows when you really want to update multiple rows.

I don't.

> Compare this to the normal psql prompt. How many million dollars
> would you say the total cost would be for mistakes where someone
> forgets the WHERE-clause of an UPDATE or a DELETE? :-)

Dunno, but that also tends to suggest a solution that isn't limited
to a PL would be beneficial.

> It's the same type of mistake I want to prevent from in a
> convenient way, and there is nothing more convenient than the
> default behavour.  That also means *all* users will get that
> behaviour even if they don't explicitly request it, which is a
> good thing, because then they are protected against the danger of
> not knowing how to make sure it updated/deleted only one row.

I think that changing the default behavior of SQL from set oriented
to something else is a horrible idea.  I absolutely, unequivocally
oppose that at the SQL or plpgsql level as harmful.  I understand
the need to check for this in various cases, and in fact the
application framework I designed at my previous job had Java
methods for doing DML with such a check included, named
InsertOneRow(), UpdateOneRow(), and DeleteOneRow().  Very useful.
If we can agree on a way to allow users to do the same in plpgsql,
fine -- but certainly not as the default default (word
intentionally repeated).

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

On 9/2/14 4:15 PM, Joel Jacobson wrote:
> I don't like rebranding the PERFORM command, as that would require all
> existing code with PERFORM commands to be changed.

I'm not saying the suggested syntax is perfect, but PERFORM should be 
euthanized anyway.  Or at least the need for it; perhaps there's no need 
to break all the current uses of PERFORM.


.marko

On 9/2/14 4:26 PM, Kevin Grittner wrote:
> Joel Jacobson <joel@trustly.com> wrote:
>> The common use-case I have in mind is when you have a function
>> which takes some kind of ID as an input param, which maps to a
>> primary key in some table, which you want to update.
>
> In that case FOUND works just fine.  A primary key value can't have
> more than one matching row.

No, but your code can have a bug.  INTO rejecting any queries returning 
more than one row helps, though, but having to write  RETURNING TRUE 
INTO _OK;  is not pretty either.

>> If the where-clause would be incorrect and the update would
>> update all rows in the table, that would be a disaster, which is
>> what I want to prevent.
>
> By the time you find out that the number of rows affected is every
> row in the table, you have horribly bloated the table and all its
> indexes.  Causing a DML statement to abort when it sees a second
> row is a completely different issue than what I (and I suspect most
> others on the list) thought we were talking about, and would need
> to affect far more than the PL.

Updating even two rows instead of one can have catastrophic effects.

>> It's the same type of mistake I want to prevent from in a
>> convenient way, and there is nothing more convenient than the
>> default behavour.  That also means *all* users will get that
>> behaviour even if they don't explicitly request it, which is a
>> good thing, because then they are protected against the danger of
>> not knowing how to make sure it updated/deleted only one row.
>
> I think that changing the default behavior of SQL from set oriented
> to something else is a horrible idea.  I absolutely, unequivocally
> oppose that at the SQL or plpgsql level as harmful.  I understand
> the need to check for this in various cases, and in fact the
> application framework I designed at my previous job had Java
> methods for doing DML with such a check included, named
> InsertOneRow(), UpdateOneRow(), and DeleteOneRow().  Very useful.
> If we can agree on a way to allow users to do the same in plpgsql,
> fine -- but certainly not as the default default (word
> intentionally repeated).

Yeah, it doesn't necessarily need to be the default default (and I see a 
lot of people saying it shouldn't be).  Even having a per-query modifier 
would be better than the current behaviour.


.marko

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

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

Marko Tiikkaja <marko@joh.to> wrote:
> On 9/2/14 4:26 PM, Kevin Grittner wrote:
>> Joel Jacobson <joel@trustly.com> wrote:
>>> The common use-case I have in mind is when you have a function
>>> which takes some kind of ID as an input param, which maps to a
>>> primary key in some table, which you want to update.
>>
>> In that case FOUND works just fine.  A primary key value can't have
>> more than one matching row.
>
> No, but your code can have a bug.

So the main use case is to allow buggy functions which are deployed
to production without adequate testing to be detected?  Bugs like
not getting the primary key column(s) right?  I think it would be
great to have some way to generate an error if a given statement
doesn't affect exactly one row, but the above is a pretty weak
argument for making it a default behavior.

> INTO rejecting any queries returning more than one row helps,
> though, but having to write  RETURNING TRUE INTO _OK;  is not
> pretty either.

No, that sure would not be.

>>> If the where-clause would be incorrect and the update would
>>> update all rows in the table, that would be a disaster, which is
>>> what I want to prevent.
>>
>> By the time you find out that the number of rows affected is every
>> row in the table, you have horribly bloated the table and all its
>> indexes.  Causing a DML statement to abort when it sees a second
>> row is a completely different issue than what I (and I suspect most
>> others on the list) thought we were talking about, and would need
>> to affect far more than the PL.
>
> Updating even two rows instead of one can have catastrophic effects.

That's a different problem than Joel just said was his main
concern.  I was pointing out that the solution he was proposing was
a very poor solution to the problem he said he was trying to solve.
Can you imagine the damage if a function that updated every row in
a table whenever anyone tried to update a single row by primary key
made it past testing and staging phases into production?  Depending
on the table, it might not need to run more than a few times before
the bloat ate all disk space and your production environment was
totally hosed to the point of needing to delete everything from
$PGDATA and restore from your last known good backup.

Accidentally updating a single unintended row is a whole different
class of problem, with potentially completely different solutions.
We can talk about both, but let's not conflate them.  The proposed
new behavior seems like it would only detect a small percentage of
ways you can accidentally update unintended rows, but I agree it
would catch enough of them to be a potentially useful option.  If
it were a new option on the DML statement syntax, once could
certainly have code review or some sort of "lint" software to look
for omissions.  If you don't have a code review process before
things hit production, well, mechanical solutions like this can
only be expected to catch a small percentage of the damage from
application bugs deployed to production.

>>> It's the same type of mistake I want to prevent from in a
>>> convenient way, and there is nothing more convenient than the
>>> default behavour.  That also means *all* users will get that
>>> behaviour even if they don't explicitly request it, which is a
>>> good thing, because then they are protected against the danger of
>>> not knowing how to make sure it updated/deleted only one row.
>>
>> I think that changing the default behavior of SQL from set oriented
>> to something else is a horrible idea.  I absolutely, unequivocally
>> oppose that at the SQL or plpgsql level as harmful.  I understand
>> the need to check for this in various cases, and in fact the
>> application framework I designed at my previous job had Java
>> methods for doing DML with such a check included, named
>> InsertOneRow(), UpdateOneRow(), and DeleteOneRow().  Very useful.
>> If we can agree on a way to allow users to do the same in plpgsql,
>> fine -- but certainly not as the default default (word
>> intentionally repeated).
>
> Yeah, it doesn't necessarily need to be the default default (and I see a
> lot of people saying it shouldn't be).  Even having a per-query modifier
> would be better than the current behaviour.

There we seem to agree.  I definitely think it is a useful option
if we can sort out a good way to allow it.

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

On Sep 1, 2014, at 10:24 PM, Craig Ringer <craig@2ndQuadrant.com> wrote:

> 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
classlanguage, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and
don’twant to learn as no one else seems to use it (in my world anyway).  perl is the only possibility left and again,
noone in my world is using Perl and it’s not clear if there is a performance penalty.  The docs say the best language
forperformance is PL/pgSQL after pure SQL. 
>
> PL/Perl is plenty fast, FWIW.
>

Good to know.  I used to do a lot of perl and will revisit the language.

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

I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6 or
10level quotes to make it work.  So instead of (from the docs, 40.6.4. Looping Through Query Results) 
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);       EXECUTE 'INSERT INTO '                  ||
quote_ident(mviews.mv_name)|| ' '                  || mviews.mv_query; 

should be something like:EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’;       EXECUTE ‘INSERT INTO $$mviews.mv_name
$$mviews.mv_query’;

Wow, so after I wrote the above, I went back to review the docs and lo and behold the format function was added in 9.1
(Ithink). 

It turns out it can already be written as (not tested)EXECUTE format( ‘TRUNCATE TABLE %I’, mviews.mv_name);EXECUTE
format(‘INSERT INTO %I %L’, mviews.mv_name, mviews.mv_query); 

That’s not so bad and very similar to how it would have to be done in many other languages.  However the first three
examplesin the docs for PL/pgSQL for dynamic queries and many, many other places don’t show this approach.  And the
formatsyntax is only listed 4 lines from the bottom of the section as a ‘you can also do this’.  From the position and
wordingI would interpret that something must be wrong with using the format function to construct dynamic queries, but,
whoknew, I never scrolled down that far in the docs.  Thank you to whomever added the format() function. 

So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended?  And
theformat function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks) 

Neil

On 9/2/14 5:08 PM, Kevin Grittner wrote:
> Marko Tiikkaja <marko@joh.to> wrote:
>> On 9/2/14 4:26 PM, Kevin Grittner wrote:
>>> Joel Jacobson <joel@trustly.com> wrote:
>>>> The common use-case I have in mind is when you have a function
>>>> which takes some kind of ID as an input param, which maps to a
>>>> primary key in some table, which you want to update.
>>>
>>> In that case FOUND works just fine.  A primary key value can't have
>>> more than one matching row.
>>
>> No, but your code can have a bug.
>
> So the main use case is to allow buggy functions which are deployed
> to production without adequate testing to be detected?  Bugs like
> not getting the primary key column(s) right?

The main use case is making it more clear *during testing* that the code 
is broken.  It doesn't hurt that it would also not trash your data if 
someone deployed bad code into production, but I think it's more 
important to have good tools for testing your code.

> I think it would be
> great to have some way to generate an error if a given statement
> doesn't affect exactly one row, but the above is a pretty weak
> argument for making it a default behavior.

Perhaps.


.marko

On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Marko Tiikkaja <marko@joh.to> wrote:
>> No, but your code can have a bug.
>
> So the main use case is to allow buggy functions which are deployed
> to production without adequate testing to be detected?  Bugs like
> not getting the primary key column(s) right?  I think it would be
> great to have some way to generate an error if a given statement
> doesn't affect exactly one row, but the above is a pretty weak
> argument for making it a default behavior.

Instead of writing unit tests for such trivial things as updating one row
and testing if it got updated, it's better to make such unit tests
asserts instead,
which is exactly what we achieve if we provide a syntax to throw an error if
not exactly 1 row was affected.

>> Updating even two rows instead of one can have catastrophic effects.
>
> That's a different problem than Joel just said was his main
> concern.  I was pointing out that the solution he was proposing was
> a very poor solution to the problem he said he was trying to solve.
> Can you imagine the damage if a function that updated every row in
> a table whenever anyone tried to update a single row by primary key
> made it past testing and staging phases into production?  Depending
> on the table, it might not need to run more than a few times before
> the bloat ate all disk space and your production environment was
> totally hosed to the point of needing to delete everything from
> $PGDATA and restore from your last known good backup.

Sorry for being unclear, I didn't mean to suggest the main concern is
updating *all* rows.
The main concern is when you have a rather complex UPDATE WHERE clause,
aiming to update exactly one row. Some of the expressions might be
assertions, to just double-verify the values and to make it stand-out
you are checking
those expressions.

On 09/02/2014 11:10 PM, Neil Tiffin wrote:

> I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6
or10 level quotes to make it work.  So instead of (from the docs, 40.6.4. Looping Through Query Results)
 
> 
>     EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
>         EXECUTE 'INSERT INTO '
>                    || quote_ident(mviews.mv_name) || ' '
>                    || mviews.mv_query;
> 
> should be something like:
>     EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’;
>         EXECUTE ‘INSERT INTO $$mviews.mv_name $$mviews.mv_query’;

I think we need to remove those sections entirely from the docs, in
favour of using only format(...) with EXECUTE ... USING .

Too many people seem to see that, and not format(...).

> So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended?

Historical, really.

> And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks)

That's a real oversight that needs fixing. Thanks.

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

On 09/02/2014 06:44 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Marko Tiikkaja <marko@joh.to> wrote:
>>> No, but your code can have a bug.
>>
>> So the main use case is to allow buggy functions which are deployed
>> to production without adequate testing to be detected?  Bugs like
>> not getting the primary key column(s) right?  I think it would be
>> great to have some way to generate an error if a given statement
>> doesn't affect exactly one row, but the above is a pretty weak
>> argument for making it a default behavior.
>
> Instead of writing unit tests for such trivial things as updating one row
> and testing if it got updated, it's better to make such unit tests
> asserts instead,
> which is exactly what we achieve if we provide a syntax to throw an error if
> not exactly 1 row was affected.

Marko posted a patch to add assertions to PL/pgSQL last year, see 
http://www.postgresql.org/message-id/5234AF3F.4000409@joh.to. It was a 
long thread, but in the end I think everyone was more or less OK with 
the syntax "ASSERT <condition>;". I also think that syntax is fine, and 
it would be a nice feature, assuming we can avoid reserving the ASSERT 
keyword.

I think that would actually be a good way to enforce the rule that an 
UPDATE only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after 
the update.

- Heikki

Joel Jacobson <joel@trustly.com> wrote:

> The common use-case I have in mind is when you have a function which
> takes some kind of ID as an input param, which maps to a primary key
> in some table, which you want to update.
> If the where-clause would be incorrect and the update would update all
> rows in the table, that would be a disaster, which is what I want to
> prevent.


Joel Jacobson <joel@trustly.com> wrote:

> Sorry for being unclear, I didn't mean to suggest the main concern is
> updating *all* rows.
> The main concern is when you have a rather complex UPDATE WHERE clause,
> aiming to update exactly one row. Some of the expressions might be
> assertions, to just double-verify the values and to make it stand-out
> you are checking those expressions.


These are two different problems which probably need two different
solutions.  Making the default behavior of a set-based command that
it throw an error if the resulting set is not exactly one row
doesn't seem like the right solution to either one of them.

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

On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote:

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

Have you ever tried any of the "real" NoSQL products version of "infinite scalability" ?

We are no worse than most if you use just the unstructured part (which is what the NoSQL crowd provides) and something like pl/proxy for scaling.

On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> I think that would actually be a good way to enforce the rule that an UPDATE
> only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after the
> update.

So instead of one line of code, I would need to write two lines of
code at almost *all* places where a currently have an UPDATE. :-(
In that case, I think "RETURNING TRUE INTO STRICT _OK" is less ugly.

I think the problem with my perspective is my ambitions. I use
PL/pgSQL not as a secondary language, but it's my primary language for
developing applications.
For me, updating a row, is like setting a variable in a normal language.
No normal language would require two rows to set a variable.
It would be like having to do:  my $var = 10;  die unless $var == 10;
in Perl to set a variable.

On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Joel Jacobson <joel@trustly.com> wrote:
>
>> Sorry for being unclear, I didn't mean to suggest the main concern is
>> updating *all* rows.
>> The main concern is when you have a rather complex UPDATE WHERE clause,
>> aiming to update exactly one row. Some of the expressions might be
>> assertions, to just double-verify the values and to make it stand-out
>> you are checking those expressions.
>
>
> These are two different problems which probably need two different
> solutions.  Making the default behavior of a set-based command that
> it throw an error if the resulting set is not exactly one row
> doesn't seem like the right solution to either one of them.

I see your point.
Basically, we have two types of applications where PL/pgSQL is commonly used.
a) OLTP applications where you typically operate on one row for each
UPDATE command.
b) Data warehouseing applications where you process multiple rows in
each UPDATE command.

Both have different desired default behaviours of the different
set-based commands used in PL/pgSQL.
I think both are important enough to motivate a nice syntax for both use-cases.
If we cannot change the default behaviour of UPDATE, then I vote for
the eariler proposed STRICT UPDATE syntax.
That would not protect novice users (like myself a couple of years
ago) who falsly thinks an UPDATE which updated 0 rows would fail.
But at least it would provide them a quite nice syntax to fix that
when shit hits the fan due to their failure.

On 09/02/2014 12:12 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> I think that would actually be a good way to enforce the rule that an UPDATE
>> only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after the
>> update.
> So instead of one line of code, I would need to write two lines of
> code at almost *all* places where a currently have an UPDATE. :-(
> In that case, I think "RETURNING TRUE INTO STRICT _OK" is less ugly.
>
> I think the problem with my perspective is my ambitions. I use
> PL/pgSQL not as a secondary language, but it's my primary language for
> developing applications.
> For me, updating a row, is like setting a variable in a normal language.
> No normal language would require two rows to set a variable.
> It would be like having to do:
>     my $var = 10;
>     die unless $var == 10;
> in Perl to set a variable.
>
>



That's really a problem with your perspective. UPDATE is inherently set 
oriented. It's emphatically NOT like setting a single variable.

I must have written tens, possibly hundreds of thousands of lines of 
plpgsql, and this have never ever been a problem for me.

I'd be very opposed to adding some special new plpgsql-only syntax to 
have UPDATE or DELETE error out if they affected more than a single row. 
And as you and others have observed, you can do that now with the 
"RETURNING true INTO STRICT ok" trick.

cheers

andrew

On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa <aht@nosys.es> wrote:
>     We are definitely worse. This is the problem, we only look to our own
> belly bottom (if this expression exists in English). All NoSQL scale
> *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
> they don't suck at many many other things, or that some of them may be worse
> solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
> far away from what we need to compete agains NoSQL in these regards.

So the discussion started out with a desire to improve PL/pgSQL. Now
somehow NoSQL and JSON is discussed in the same thread. Interesting.
Godwin's Law never fails :-)
http://en.wikipedia.org/wiki/Godwin's_law

On 02/09/14 18:20, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Joel Jacobson <joel@trustly.com> wrote:
>>
>>> Sorry for being unclear, I didn't mean to suggest the main concern is
>>> updating *all* rows.
>>> The main concern is when you have a rather complex UPDATE WHERE clause,
>>> aiming to update exactly one row. Some of the expressions might be
>>> assertions, to just double-verify the values and to make it stand-out
>>> you are checking those expressions.
>>
>> These are two different problems which probably need two different
>> solutions.  Making the default behavior of a set-based command that
>> it throw an error if the resulting set is not exactly one row
>> doesn't seem like the right solution to either one of them.
> I see your point.
> Basically, we have two types of applications where PL/pgSQL is commonly used.
> a) OLTP applications where you typically operate on one row for each
> UPDATE command.
> b) Data warehouseing applications where you process multiple rows in
> each UPDATE command.
>
> Both have different desired default behaviours of the different
> set-based commands used in PL/pgSQL.
> I think both are important enough to motivate a nice syntax for both use-cases.
> If we cannot change the default behaviour of UPDATE, then I vote for
> the eariler proposed STRICT UPDATE syntax.
    I see both use cases, but I think the SQL default are set 
operations. For this particular point, if there would be just a syntax 
change rather than a new language, in the line of the "ONE ROW UPDATE" 
syntax, I'd say "UNIQUE UPDATE". This {ONE ROW, UNIQUE} syntax may also 
enforce having a WHERE clause. I find both better than the STRICT, which 
is not doing what -you may think- it does.
    Regards,
    Álvaro

On 09/02/2014 07:12 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> I think that would actually be a good way to enforce the rule that an UPDATE
>> only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after the
>> update.
>
> So instead of one line of code, I would need to write two lines of
> code at almost *all* places where a currently have an UPDATE. :-(

Right. Doesn't really seem that bad, to be honest. You can put it on the 
same line if you wish.

> I think the problem with my perspective is my ambitions. I use
> PL/pgSQL not as a secondary language, but it's my primary language for
> developing applications.

Sure, a lot of people do that.

> For me, updating a row, is like setting a variable in a normal language.
> No normal language would require two rows to set a variable.
> It would be like having to do:
>     my $var = 10;
>     die unless $var == 10;
> in Perl to set a variable.

I don't think most applications are like that. See Kevin's comments 
about doing things in a set-oriented way instead of row-by-row. I know 
I've changed several procedures from the row-oriented style, looping 
over rows with a FOR loop, updating each one individually, to 
set-oriented style with a single UPDATE for a bunch of rows. It makes 
for more concise code, and performs better. I'm sure there are 
counter-examples, and I've also written many UPDATE statements that are 
expected to update exactly one row, but I find an ASSERT would be 
adequate for that.

- Heikki

On 09/02/2014 06:27 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa <aht@nosys.es> wrote:
>>     We are definitely worse. This is the problem, we only look to our own
>> belly bottom (if this expression exists in English). All NoSQL scale
>> *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
>> they don't suck at many many other things, or that some of them may be worse
>> solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
>> far away from what we need to compete agains NoSQL in these regards.
> So the discussion started out with a desire to improve PL/pgSQL. Now
> somehow NoSQL and JSON is discussed in the same thread. Interesting.
> Godwin's Law never fails :-)
> http://en.wikipedia.org/wiki/Godwin's_law
Not to mention completely unsubstantiated claims about *all* NoSQL
scaling *easily* and *transparently* beyond one node :)

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

On 02/09/14 18:33, Hannu Krosing wrote:
> On 09/02/2014 06:27 PM, Joel Jacobson wrote:
>> On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa <aht@nosys.es> wrote:
>>>      We are definitely worse. This is the problem, we only look to our own
>>> belly bottom (if this expression exists in English). All NoSQL scale
>>> *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
>>> they don't suck at many many other things, or that some of them may be worse
>>> solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
>>> far away from what we need to compete agains NoSQL in these regards.
>> So the discussion started out with a desire to improve PL/pgSQL. Now
>> somehow NoSQL and JSON is discussed in the same thread. Interesting.
>> Godwin's Law never fails :-)
>> http://en.wikipedia.org/wiki/Godwin's_law
> Not to mention completely unsubstantiated claims about *all* NoSQL
> scaling *easily* and *transparently* beyond one node :)
>
    Honestly, this is off-topic and we can argue forever, but 
regardless all do or not, what's sure is that Postgres doesn't have 
horizontal scalability. Period.
    And this is what we should look at. And we can't claim we're NoSQL 
until we have (easy, transparent) horizontal scalability.
    Best,
    Álvaro

On Tue, Sep  2, 2014 at 04:24:11PM +0200, Andres Freund wrote:
> On 2014-09-02 10:21:50 -0400, Tom Lane wrote:
> > Marko Tiikkaja <marko@joh.to> writes:
> > > For example:
> > 
> > > UPDATE foo WHERE bar = 1;  -- must affect exactly one row
> > > PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows
> > 
> > FWIW, I agree with the position that this would be a completely wrong
> > thing to do.  UPDATE should work like it does in plain SQL.  If you want
> > a restriction to "exactly one row", that needs to be a modifier.
> > 
> > I take no position on how the modifier should be spelled, though.
> 
> Personally I think 
> ONE ROW UPDATE ...
> reads nicely and SQL-ish. But it's not very expandable to other numbers.

SINGLETON UPDATE ...?


--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Tue, Sep  2, 2014 at 12:40:14AM -0400, Tom Lane wrote:
> Craig Ringer <craig@2ndquadrant.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.

FYI, the docs of what EDB has done are online:

Server:http://www.enterprisedb.com/docs/en/9.3/eeguide/Table%2520of%2520Contents.htm

Server packages, e.g.
DBMS_:http://www.enterprisedb.com/docs/en/9.3/eeguide/Postgres_Plus_Enterprise_Edition_Guide-52.htm#P14240_790554

Oracle Compatibility Guide:http://www.enterprisedb.com/docs/en/9.3/oracompat/Table%2520of%2520Contents.htm

PL/SQL, called Stored Procedure
Language:http://www.enterprisedb.com/docs/en/9.3/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-78.htm#P6933_375311

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On 9/2/14 6:03 PM, Heikki Linnakangas wrote:
> Marko posted a patch to add assertions to PL/pgSQL last year, see
> http://www.postgresql.org/message-id/5234AF3F.4000409@joh.to. It was a
> long thread, but in the end I think everyone was more or less OK with
> the syntax "ASSERT <condition>;". I also think that syntax is fine, and
> it would be a nice feature, assuming we can avoid reserving the ASSERT
> keyword.

Did you really mean to say "more or less OK"?  I didn't wade through the 
thread, but my recollection is that I was the only one truly OK with it, 
some people expressed concerns but appeared undecided, and the rest of 
the participants were completely against it.

> I think that would actually be a good way to enforce the rule that an
> UPDATE only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after
> the update.

I agree with Joel here; I think a shorter syntax is necessary.


.marko

On Tue, Sep 2, 2014 at 6:31 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> I don't think most applications are like that. See Kevin's comments about
> doing things in a set-oriented way instead of row-by-row. I know I've
> changed several procedures from the row-oriented style, looping over rows
> with a FOR loop, updating each one individually, to set-oriented style with
> a single UPDATE for a bunch of rows. It makes for more concise code, and
> performs better. I'm sure there are counter-examples, and I've also written
> many UPDATE statements that are expected to update exactly one row, but I
> find an ASSERT would be adequate for that.

I'm *not* doing FOR-loops with UPDATE of single rows. I typically have
functions which have an input variable, which maps to a primary key in
a table, and the UPDATE is made on that single row. This is a
simplificaiton, but the main point is that the typical use case is
*not* FOR-loops.

On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian <bruce@momjian.us> wrote:
> SINGLETON UPDATE ...?

Does it come with built-in spell check? :-) It's a bit long to write.
I like STRICT, that maps good to what we already have with SELECT ...
INTO STRICT.

On Tue, Sep  2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > SINGLETON UPDATE ...?
> 
> Does it come with built-in spell check? :-) It's a bit long to write.
> I like STRICT, that maps good to what we already have with SELECT ...
> INTO STRICT.

Spell checker coming in plpgsql3!  ;-)

Anyway, as you have seen, the problem is not creating plpgsql2 --- you
could do that yourself on your own and distribute it.  What you want is
for the community to develop/maintain it, and as you have also seen,
everyone uses plpgsql slightly differently, so if you want the community
to maintain it, you are only going to get some of the things you want.

Of course, the idea of upgrading plpgsql is long overdue, so it is very
good we are all talking about it.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Tue, Sep 2, 2014 at 7:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, Sep  2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote:
>> On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> > SINGLETON UPDATE ...?
>>
>> Does it come with built-in spell check? :-) It's a bit long to write.
>> I like STRICT, that maps good to what we already have with SELECT ...
>> INTO STRICT.
>
> Spell checker coming in plpgsql3!  ;-)
>
> Anyway, as you have seen, the problem is not creating plpgsql2 --- you
> could do that yourself on your own and distribute it.  What you want is
> for the community to develop/maintain it, and as you have also seen,
> everyone uses plpgsql slightly differently, so if you want the community
> to maintain it, you are only going to get some of the things you want.

Very true, I totally agree.

But what do you think about,
STRICT UPDATE ...?

> Of course, the idea of upgrading plpgsql is long overdue, so it is very
> good we are all talking about it.

I'm very glad you share that opinion. :-)

On 9/2/14 6:31 PM, Heikki Linnakangas wrote:
> On 09/02/2014 07:12 PM, Joel Jacobson wrote:
>> For me, updating a row, is like setting a variable in a normal language.
>> No normal language would require two rows to set a variable.
>> It would be like having to do:
>>      my $var = 10;
>>      die unless $var == 10;
>> in Perl to set a variable.
>
> I don't think most applications are like that. See Kevin's comments
> about doing things in a set-oriented way instead of row-by-row. I know
> I've changed several procedures from the row-oriented style, looping
> over rows with a FOR loop, updating each one individually, to
> set-oriented style with a single UPDATE for a bunch of rows. It makes
> for more concise code, and performs better. I'm sure there are
> counter-examples, and I've also written many UPDATE statements that are
> expected to update exactly one row, but I find an ASSERT would be
> adequate for that.

Well, just off the top of my head a normal function invocation could be: 
one worker working on a single "order" started by a single end user to 
transfer money from one account to another.  And we have *a lot* of code 
like this where there isn't a way to write the code in "set-oriented 
style" without inventing a time machine.  Which just might be out of the 
scope of plpgsql2 (or perhaps as a GUC).


.marko

Marko Tiikkaja <marko@joh.to> wrote:

> Well, just off the top of my head a normal function invocation could be:
> one worker working on a single "order" started by a single end user to
> transfer money from one account to another.  And we have *a lot* of code
> like this where there isn't a way to write the code in "set-oriented
> style" without inventing a time machine.  Which just might be out of the
> scope of plpgsql2 (or perhaps as a GUC).

That's almost exactly a situation I was going to use to illustrate
where I *did* want set-oriented behavior.  One hard rule in the
shop in question was that an application is *never* allowed to
leave a database transaction pending while waiting for user input.
Another was that a financial transaction must be committed to the
database as one database transaction, with ID numbers that were
assigned in commit sequence, with no gaps, to satisfy the auditors.
Just to complete the scope of the issue, each time a database
transaction was run, it got a random connection from a connection
pool, so temporary tables could not be used across transactions.

Think about that for a minute.

What we did was to create permanent work tables with a temporary ID
for a financial transaction in process as part of the primary key.
(The rest of the primary key matched the corresponding "normal"
table.)  The user takes however much time it takes to populate the
financial transaction, which typically affects many tables
including at least two (and sometimes hundreds of) rows in the
TransactionDetail table.  If they cancel out of the entry process
we delete all of the affected rows with one DELETE statement per
table.  If they OK the financial transaction we copy the data from
the work tables to the normal tables with one INSERT and one DELETE
per table -- all in one database transaction.  (The auditor
requirements were satisfied by some explicit locking and SEQUENCE
objects in that final database transaction.)  Fast, clean, and
effective.

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

On 09/02/2014 07:51 PM, Marko Tiikkaja wrote:
> On 9/2/14 6:03 PM, Heikki Linnakangas wrote:
>> Marko posted a patch to add assertions to PL/pgSQL last year, see
>> http://www.postgresql.org/message-id/5234AF3F.4000409@joh.to. It was a
>> long thread, but in the end I think everyone was more or less OK with
>> the syntax "ASSERT <condition>;". I also think that syntax is fine, and
>> it would be a nice feature, assuming we can avoid reserving the ASSERT
>> keyword.
>
> Did you really mean to say "more or less OK"?  I didn't wade through the
> thread, but my recollection is that I was the only one truly OK with it,
> some people expressed concerns but appeared undecided, and the rest of
> the participants were completely against it.

I didn't pay attention back then, but I just scanned through the thread 
in the archives. Peter Eisentraut was fine with the syntax [1]. Pavel 
Stehule was OK with "ASSERT ...", although he wanted to be able to 
specify a level [2]. Jaime Casanova was OK with it [3]. And you can 
count me in too. I didn't see anyone strongly opposed to it.

There was worry about reserving the keyword, and Pavel wanted the 
level-feature, and there was discussion on the details of whether it can 
be caught with EXCEPTION WHEN OTHERS. Those are important details, but 
there was a pretty good consensus on the basic ASSERT syntax by my count.

[1] 
http://www.postgresql.org/message-id/1385527300.28256.15.camel@vanquo.pezone.net)
[2] 
http://www.postgresql.org/message-id/CAFj8pRAoO=JmS+D30BK6j1PrVR04oaXDHYa9PO7PQY7zsbuSTA@mail.gmail.com
[3] 
http://www.postgresql.org/message-id/CAJKUy5g-VCMF65JDuCnxdSYWLr5SJEROqvtuGd_+eiE+2dDnbg@mail.gmail.com

- Heikki

On 2014-09-02 19:33, Kevin Grittner wrote:
> Marko Tiikkaja <marko@joh.to> wrote:
>
>> Well, just off the top of my head a normal function invocation could be:
>> one worker working on a single "order" started by a single end user to
>> transfer money from one account to another.  And we have *a lot* of code
>> like this where there isn't a way to write the code in "set-oriented
>> style" without inventing a time machine.  Which just might be out of the
>> scope of plpgsql2 (or perhaps as a GUC).
>
> That's almost exactly a situation I was going to use to illustrate
> where I *did* want set-oriented behavior.  One hard rule in the
> shop in question was that an application is *never* allowed to
> leave a database transaction pending while waiting for user input.

Of course the transaction isn't open while waiting for user input.  But 
for the lifetime of the "order", almost all of the code is nearly always 
dealing with one of a number of things, and almost never with more than 
one of anything.

> Another was that a financial transaction must be committed to the
> database as one database transaction, with ID numbers that were
> assigned in commit sequence, with no gaps, to satisfy the auditors.
> Just to complete the scope of the issue, each time a database
> transaction was run, it got a random connection from a connection
> pool, so temporary tables could not be used across transactions.
>
> Think about that for a minute.
>
> What we did was to create permanent work tables with a temporary ID
> for a financial transaction in process as part of the primary key.
> (The rest of the primary key matched the corresponding "normal"
> table.)  The user takes however much time it takes to populate the
> financial transaction, which typically affects many tables
> including at least two (and sometimes hundreds of) rows in the
> TransactionDetail table.  If they cancel out of the entry process
> we delete all of the affected rows with one DELETE statement per
> table.  If they OK the financial transaction we copy the data from
> the work tables to the normal tables with one INSERT and one DELETE
> per table -- all in one database transaction.  (The auditor
> requirements were satisfied by some explicit locking and SEQUENCE
> objects in that final database transaction.)  Fast, clean, and
> effective.

Sounds like in this case you'd only use set-oriented programming at the 
end of the transaction, no?   Or was there a separate application 
gathering the details about the transaction, which then just sent all of 
that information in a batch into the database?


.marko

Marko Tiikkaja <marko@joh.to> wrote:

> Sounds like in this case you'd only use set-oriented programming
> at the end of the transaction, no?

I guess -- more properly I would say "in the final database
transaction for that financial transaction."  And no, that never
made me wish that plpgsql functions defaulted to throwing errors
for DML statements that affected more than one row.

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

On Tue, Sep  2, 2014 at 07:06:02PM +0200, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 7:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > On Tue, Sep  2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote:
> >> On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >> > SINGLETON UPDATE ...?
> >>
> >> Does it come with built-in spell check? :-) It's a bit long to write.
> >> I like STRICT, that maps good to what we already have with SELECT ...
> >> INTO STRICT.
> >
> > Spell checker coming in plpgsql3!  ;-)
> >
> > Anyway, as you have seen, the problem is not creating plpgsql2 --- you
> > could do that yourself on your own and distribute it.  What you want is
> > for the community to develop/maintain it, and as you have also seen,
> > everyone uses plpgsql slightly differently, so if you want the community
> > to maintain it, you are only going to get some of the things you want.
> 
> Very true, I totally agree.
> 
> But what do you think about,
> STRICT UPDATE ...?

Seems fine to me.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On 2014-09-02 19:06:02 +0200, Joel Jacobson wrote:
> But what do you think about,
> STRICT UPDATE ...?

I quite dislike it. An UPDATE isn't less 'strict' (whatever that means)
if updates more than one row. There's some sense in the way it's used
for INTO because it's referring to the INTO. And it's much more obvious
what it could mean there.

Greetings,

Andres Freund

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

On Mon, Sep 1, 2014 at 4:04 AM, Joel Jacobson <joel@trustly.com> wrote:
> 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.

What is the reasoning for breaking compatibiilty?  Why not improve the
language that's there?

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

This is the exact wrong thing to do.  It may be the most common use
case for *you*, but it's certainly not for me and is objectively a
step backards; set based processing is generally faster and less error
prone.  Although we have to write loops sometimes (say, for complex
iteration or error handling), I tend to only do so as a last resort.
Windows functions + custom aggregate functions have removed most cases
where I would have had to so for computation reasons.  If you *must*
process things row by row, why not use the facility in the language
that handle that: "WHERE CURRENT OF".

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

I see the point here, but this is not a fundamental problem with the
language IMO.

> + Change all warnings into errors

This is an ok idea, but not sure why you have to re-invent pl/pgsql to do it.

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

Here are the headaches I see:

* performance: plpsql can be slow for many types of iterative
processing.  everybody wants the language to run faster but rewriting
from scratch doesn't seem a good way to do that unless the current
language structure has some critical performance blocking shortcoming.

* lack of non-table data structures (like hashmap, etc).  At present
pl/plgsql only has arrays to manage temporary non-table data.  this is
where plpgsql is bad but many of the other languages like pl/v8 etc
are good.

* not enough function-time validation (I think we agree on this).  the
situation has gotten better over the years but it'd be nice to have
stronger checks  raised for runtime code (although, I prefer them as
warnings, not errors).

* can't manage transaction state -- only can create sub transactions

* can't execute until after mvcc snapshot occurs (for example, you
can't change transaction isolation and/or lock tables to prevent
serialization errors)

* weak or non-existent debugging tools

None of the above points except for the transaction management issue
seem to be solved easier in a new language vs an old one.  Non sql
based languages pls (I figure plv8 is the best choice out of many)
would fix the performance issue but you'd have to give up first class
SQL statements to get that.

merlin

On 09/02/2014 09:48 AM, Bruce Momjian 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.

What any commercial entity and the Community do are mutually exclusive 
and we can not and should not determine what features we will support 
based on any commercial endeavor.

JD


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

Without having read the entire thread ...

On 09/01/2014 05:04 AM, Joel Jacobson wrote:
>  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.

I think this is a completely flawed proposal "by definition". SQL itself 
does not make such assumption and as a SET oriented language, never 
should. A SET is zero or more tuples. Would you also suggest that the 
PostgreSQL backend throw an ERROR if an UPDATE/INSERT/DELETE doesn't 
modify exactly 1 row? If not, explain why there should be a difference 
between SQL executed from the frontend and SQL executed by a PL/pgSQL 
function.

-1 from me.


> + Change all warnings into errors

I suggest being slightly more selective on that one.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On Tue, Sep 2, 2014 at 10:16 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2014-09-02 19:06:02 +0200, Joel Jacobson wrote:
>> But what do you think about,
>> STRICT UPDATE ...?
>
> I quite dislike it. An UPDATE isn't less 'strict' (whatever that means)
> if updates more than one row. There's some sense in the way it's used
> for INTO because it's referring to the INTO. And it's much more obvious
> what it could mean there.

For those who are familiar with the "INTO STRICT" syntax for SELECT,
I think they will also understand the meaning with UPDATE,
and maybe it could also be considered a plus not having to invent new keywords,
if we can use the ones we already have for other commands.

On 09/01/2014 09:06 PM, Andrew Dunstan wrote:
>
> On 09/01/2014 08:09 PM, Neil Tiffin wrote:
> 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
donein assembler, but no one suggests that today.  Today, it is all about programmer productivity.  PL/pgSQL has a lot
ofunnecessary stuff that sucks the life out of programmer productivity.  And this should be very much a concern of the
professionalsthat 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.)

First of all it is

[DECLARE
declarations]
BEGIN
statements
END;

Second "statements" includes the whole definition above as a "statement" 
and controls identifier visibility and such compatible to PL/SQL.

You want to lose that? Not such a great idea, IMHO.


Jan


-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On Tue, Sep 2, 2014 at 10:27 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> What is the reasoning for breaking compatibiilty?  Why not improve the
> language that's there?

Because many suggested improvement are not possible without
breaking compatibility, at least in theory. See previous posts in the thread.

> where I would have had to so for computation reasons.  If you *must*
> process things row by row, why not use the facility in the language
> that handle that: "WHERE CURRENT OF".

Again, because I use PL/pgSQL functions for *all* data access,
which means I don't have any prepared statements updating single rows
from my application code outside the database, as my PL/pgSQL code
*is* my application and I don't have any direct table access from outside
the database and my PL/pgSQL functions.

That means even something as simple as updating some column(s) in a table
matching the primary key, is done inside a PL/pgSQL function.

>
>> + 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.
>
> I see the point here, but this is not a fundamental problem with the
> language IMO.

Yes it is a language problem. Show me how to do it in a nice way with PL/pgSQL?
It cannot be done.

>> + Change all warnings into errors
>
> This is an ok idea, but not sure why you have to re-invent pl/pgsql to do it.

> Here are the headaches I see:
>
> * performance: plpsql can be slow for many types of iterative
> processing.  everybody wants the language to run faster but rewriting
> from scratch doesn't seem a good way to do that unless the current
> language structure has some critical performance blocking shortcoming.

Performance is always nice, but that won't require a new language though.

> * lack of non-table data structures (like hashmap, etc).  At present
> pl/plgsql only has arrays to manage temporary non-table data.  this is
> where plpgsql is bad but many of the other languages like pl/v8 etc
> are good.

+1. I've missed hashmaps sometimes. I usually resort to temp tables.

On 09/02/2014 12:20 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Joel Jacobson <joel@trustly.com> wrote:
>>
>>> Sorry for being unclear, I didn't mean to suggest the main concern is
>>> updating *all* rows.
>>> The main concern is when you have a rather complex UPDATE WHERE clause,
>>> aiming to update exactly one row. Some of the expressions might be
>>> assertions, to just double-verify the values and to make it stand-out
>>> you are checking those expressions.
>>
>>
>> These are two different problems which probably need two different
>> solutions.  Making the default behavior of a set-based command that
>> it throw an error if the resulting set is not exactly one row
>> doesn't seem like the right solution to either one of them.
>
> I see your point.
> Basically, we have two types of applications where PL/pgSQL is commonly used.
> a) OLTP applications where you typically operate on one row for each
> UPDATE command.

Your idea of what an OLTP application is seems flawed.

> b) Data warehouseing applications where you process multiple rows in
> each UPDATE command.

Ditto.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On 09/01/2014 10:41 AM, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer <craig@2ndquadrant.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.

You know that you're running into problems with the SPI subsystem on 
that one, no?

Identifiers cannot be parameters in SPI_prepare(). So how do you propose 
to make that "pretty" and "performant"?

Because the moment, your "pretty" language is out there, be sure users 
will kick your behind that whenever they use that "pretty" stuff on 
anything but a toy setup, it spirals their servers into a DOS attack state.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 09/02/2014 09:48 AM, Bruce Momjian 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.

What any commercial entity and the Community do are mutually exclusive and we can not and should not determine what features we will support based on any commercial endeavor.

​From where I sit the "mutually exclusive" argument doesn't seem to be true - and in fact is something I think would be bad if it were.  We shouldn't be afraid to add features to core that vendors are offering but at the same time the fact that the Oracle compatibility aspects are commercial instead of in-core is a plus to help ensure that there are people making a decent living off PostgreSQL and thus are invested in its future

On 09/02/2014 02:11 PM, David Johnston wrote:
> On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake <jd@commandprompt.com
> <mailto:jd@commandprompt.com>>wrote:
>
>
>     On 09/02/2014 09:48 AM, Bruce Momjian 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.
>
>
>     What any commercial entity and the Community do are mutually
>     exclusive and we can not and should not determine what features we
>     will support based on any commercial endeavor.
>
>
> ​From where I sit the "mutually exclusive" argument doesn't seem to be
> true - and in fact is something I think would be bad if it were.  We
> shouldn't be afraid to add features to core that vendors are offering
> but at the same time the fact that the Oracle compatibility aspects are
> commercial instead of in-core is a plus to help ensure that there are
> people making a decent living off PostgreSQL and thus are invested in

Far more people make a very good living off of PostgreSQL than *any* 
commercial variant. I stand by what I said. It is not the responsibility 
or the care of the community what a commercial vendor does or does not 
do with their fork except, possibly to implement the open source 
equivalent where it makes sense or where licensing may not be followed. 
(Read: I don't care about oracle compatibility)


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

On 09/02/2014 09:32 AM, Joel Jacobson wrote:

> I think it's much better to make it the default behaviour in plpgsql2
> than to add a new syntax to plpgsql,
> because then we don't have to argue what to call the keyword or where to put it.

This should in NO CASE be any new syntax to plpgsql or plpgsql2. If you 
cannot get that same syntax to apply to default client queries, then the 
whole idea is bogus because it will confuse developers more than it 
helps you with your particular way of thinking.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On 02/09/14 23:34, Joshua D. Drake wrote:
>
> On 09/02/2014 02:11 PM, David Johnston wrote:
>> On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake <jd@commandprompt.com
>> <mailto:jd@commandprompt.com>>wrote:
>>
>>
>>     On 09/02/2014 09:48 AM, Bruce Momjian 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.
>>
>>
>>     What any commercial entity and the Community do are mutually
>>     exclusive and we can not and should not determine what features we
>>     will support based on any commercial endeavor.
>>
>>
>> ​From where I sit the "mutually exclusive" argument doesn't seem to be
>> true - and in fact is something I think would be bad if it were.  We
>> shouldn't be afraid to add features to core that vendors are offering
>> but at the same time the fact that the Oracle compatibility aspects are
>> commercial instead of in-core is a plus to help ensure that there are
>> people making a decent living off PostgreSQL and thus are invested in
>
> Far more people make a very good living off of PostgreSQL than *any* 
> commercial variant. I stand by what I said. It is not the 
> responsibility or the care of the community what a commercial vendor 
> does or does not do with their fork except, possibly to implement the 
> open source equivalent where it makes sense or where licensing may not 
> be followed. (Read: I don't care about oracle compatibility)

    Yeah, we differ there. I think having an Oracle compatibility layer 
in PostgreSQL would be the-next-big-thing we could have. Oracle is has 
orders of magnitude bigger user base than postgres has; and having the 
ability to attract them would bring us many many more users which, in 
turn, would benefit us all very significantly.
    It would be my #1 priority to do in postgres (but yes, I know 
-guess- how hard and what resources that would require). But dreaming is 
free :)
    Álvaro

On 09/02/2014 04:16 PM, Andres Freund wrote:
> On 2014-09-02 19:06:02 +0200, Joel Jacobson wrote:
>> But what do you think about,
>> STRICT UPDATE ...?
>
> I quite dislike it. An UPDATE isn't less 'strict' (whatever that means)
> if updates more than one row. There's some sense in the way it's used
> for INTO because it's referring to the INTO. And it's much more obvious
> what it could mean there.

For once I completely agree with Andres.


Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On 09/01/2014 02:04 AM, Joel Jacobson wrote:
> 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.

Well, if I were designing a new procedural SQL extension language, I
wouldn't base it on the bastard child of ADA and SQL89.  I would come up
with something new.  One of the critical features such a new language
would have would be the ability to dynamically generate queries
*without* using string manipulation and EXECUTE.

Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
makeover.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 09/02/2014 03:19 PM, Josh Berkus wrote:
> Well, if I were designing a new procedural SQL extension language, I
> wouldn't base it on the bastard child of ADA and SQL89.  I would come up

Ada, that is.  One is a programming language, the other is the bane of
architects.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:

>      Yeah, we differ there. I think having an Oracle compatibility layer
> in PostgreSQL would be the-next-big-thing we could have. Oracle is has
> orders of magnitude bigger user base than postgres has; and having the
> ability to attract them would bring us many many more users which, in
> turn, would benefit us all very significantly.
>
>      It would be my #1 priority to do in postgres (but yes, I know
> -guess- how hard and what resources that would require). But dreaming is
> free :)

Oracle compatibility certainly has merit, I just don't see it as useful 
for core. I would be far more interested in MSSQL compatibility 
honestly. That said, Postgres itself is a rockstar and I think we can 
make our own case without having to copy others.

JD


>
>      Álvaro
>
>
>


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

On 09/02/2014 03:19 PM, Josh Berkus wrote:
>
> On 09/01/2014 02:04 AM, Joel Jacobson wrote:
>> 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.
>
> Well, if I were designing a new procedural SQL extension language, I
> wouldn't base it on the bastard child of ADA and SQL89.  I would come up
> with something new.  One of the critical features such a new language
> would have would be the ability to dynamically generate queries
> *without* using string manipulation and EXECUTE.
>
> Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
> makeover.
>

+1

JD

-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

On 09/02/2014 06:41 PM, Joshua D. Drake wrote:
>
> On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:
>
>>      Yeah, we differ there. I think having an Oracle compatibility layer
>> in PostgreSQL would be the-next-big-thing we could have. Oracle is has
>> orders of magnitude bigger user base than postgres has; and having the
>> ability to attract them would bring us many many more users which, in
>> turn, would benefit us all very significantly.
>>
>>      It would be my #1 priority to do in postgres (but yes, I know
>> -guess- how hard and what resources that would require). But dreaming is
>> free :)
>
> Oracle compatibility certainly has merit, I just don't see it as useful
> for core. I would be far more interested in MSSQL compatibility
> honestly. That said, Postgres itself is a rockstar and I think we can
> make our own case without having to copy others.

PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a Ada/COBOL 
lookalike.

Instead of trying to mimic what it was or a T-SQL thing instead ... 
maybe it is time to come up with a true PostgreSQL specific PL for a change?

Just for the sake of being something new, and not a copy of some old 
opossum, that's rotting like road kill on the side of the highway for a 
decade already.


Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On 09/02/2014 06:50 PM, Jan Wieck wrote:
> On 09/02/2014 06:41 PM, Joshua D. Drake wrote:
>>
>> On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:
>>
>>>      Yeah, we differ there. I think having an Oracle compatibility
>>> layer
>>> in PostgreSQL would be the-next-big-thing we could have. Oracle is has
>>> orders of magnitude bigger user base than postgres has; and having the
>>> ability to attract them would bring us many many more users which, in
>>> turn, would benefit us all very significantly.
>>>
>>>      It would be my #1 priority to do in postgres (but yes, I know
>>> -guess- how hard and what resources that would require). But
>>> dreaming is
>>> free :)
>>
>> Oracle compatibility certainly has merit, I just don't see it as useful
>> for core. I would be far more interested in MSSQL compatibility
>> honestly. That said, Postgres itself is a rockstar and I think we can
>> make our own case without having to copy others.
>
> PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a
> Ada/COBOL lookalike.

Ada yes, COBOL no.

>
> Instead of trying to mimic what it was or a T-SQL thing instead ...
> maybe it is time to come up with a true PostgreSQL specific PL for a
> change?
>
> Just for the sake of being something new, and not a copy of some old
> opossum, that's rotting like road kill on the side of the highway for
> a decade already.
>
>
>



People are free to do what they want, but to my mind that would be a
massive waste of resources, and probably imposing a substantial extra
maintenance burden on the core committers.

cheers

andrew

On 03/09/14 00:41, Joshua D. Drake wrote:
>
> On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:
>
>>      Yeah, we differ there. I think having an Oracle compatibility layer
>> in PostgreSQL would be the-next-big-thing we could have. Oracle is has
>> orders of magnitude bigger user base than postgres has; and having the
>> ability to attract them would bring us many many more users which, in
>> turn, would benefit us all very significantly.
>>
>>      It would be my #1 priority to do in postgres (but yes, I know
>> -guess- how hard and what resources that would require). But dreaming is
>> free :)
>
> Oracle compatibility certainly has merit, I just don't see it as 
> useful for core. I would be far more interested in MSSQL compatibility 
> honestly. That said, Postgres itself is a rockstar and I think we can 
> make our own case without having to copy others.
    It's not copying. It's easying a path for others to migrate and 
come to Postgres.
    I'm interested why you are more interested in MSSQL. My reasons for 
being interested in Oracle are:

- It has more users (biggest and above all, the main reason: we could 
attract more)
- Postgres is perceived as "similar" to Oracle (so migration is likely 
to be easier)
    That's all I want. Grow postgres userbase, attracting Oracle users :)
    Álvaro

On 09/02/2014 06:56 PM, Andrew Dunstan wrote:
> People are free to do what they want, but to my mind that would be a
> massive waste of resources, and probably imposing a substantial extra
> maintenance burden on the core committers.

I hear you and agree to some degree.

But at the same time I remember that one of the strengths of Postgres 
used to be to be able to incorporate "new" ideas.

This seems to be one of those cases.

Instead of "fork" plpgsql2, what about designing a completely new 
PL/postgres from scratch? It will only take 3-10 years, but I bet it 
will be worth it after all. And I mean that. No sarcasm.


Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On Wed, Sep 3, 2014 at 12:19 AM, Josh Berkus <josh@agliodbs.com> wrote:
> On 09/01/2014 02:04 AM, Joel Jacobson wrote:
>> 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.
>
> Well, if I were designing a new procedural SQL extension language, I
> wouldn't base it on the bastard child of ADA and SQL89.  I would come up
> with something new.  One of the critical features such a new language
> would have would be the ability to dynamically generate queries
> *without* using string manipulation and EXECUTE.
>
> Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
> makeover.

That's like if I would say "I want to repaint my house", you would
reply "You should build a new house instead". :-)

Though, I think I can understand your point of view here:

1. For a new developer who is starting out a new project from scratch,
and is looking for a nice PL for PostgreSQL, such a language you
are describing would be a perfect fit.

2. For all developers who already have large projects written in PL/pgSQL, and
- don't have that many problems with the language,
- are extremely productive in the language,
- love the syntax,
- trust the language,
- would never want to get a divorce from the language,
- but are very keen on *improving* the existing language,
all such developers would be very interested in PL/pgSQL 2,
but not so interested in any completely new PL.

I fall into the second category. But I understand you are more interested in
writing completely new projects than improving on your existing code,
and that's a very valid argument for all such users.

The main benefits I see with making PL/pgSQL 2 almost-compatible with PL/pgSQL,
and by developing it inside the same code base as PL/pgSQL are the following:

* Some PL/pgSQL code would compile and run in PL/pgSQL 2 without any
modifications
* Most PL/pgSQL code would compile and run in PL/pgSQL 2 with minor
modifications
* Most PL/pgSQL users would quickly be productive in the new language
after reading the "Changes" doc.
* The existing PL/pgSQL codebase is stable and trusted. If PL/pgSQL 2
is based on it, we will only have to understand and test the changes.
* PL/pgSQL was released16 years ago. It has survived time and is still
The PL for PostgreSQL. In those 16 years we have a learned a lot by
using the language. It's time for a new version of the language.

On Wed, Sep 3, 2014 at 2:46 AM, Christopher Browne <cbbrowne@gmail.com> wrote:
> The notion of hacking features onto plpgsql2 that mostly seem like SQL
> enhancements is a waste of time.

New versions of languages who change too much in a new version are
doomed to fail. There are many such examples in history.
Completely new languages are however not doomed to fail.

So when we are talking about a plpgsql2, the changes should come from
all the collective wisdome we have gained during the 16 years of its
existence.
When we are talking about a completely new language, then of course we
should start from scratch we don't need to base it on any single
language if we don't want to.

On Wed, Sep 3, 2014 at 7:17 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> yes, but there is minimal agreement of direction of movement. I am not alone
> who are thinking so your proposal is not good for general usage.

Minimal agreement? That's not true. The other group of users have been
discussing
a completely new language, which is a different discussion than the
one on PL/pgSQL 2.

Just because you think a new language is what we need, doesn't mean
you automatically
would think it's not a good idea to improve PL/pgSQL and create PL/pgSQL 2.

<div dir="ltr"><div class="gmail_extra"><br /><div class="gmail_quote">On 3 September 2014 01:08, Jan Wieck <span
dir="ltr"><<ahref="mailto:jan@wi3ck.info" target="_blank">jan@wi3ck.info</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">On 09/02/2014
06:56PM, Andrew Dunstan wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">People are free to do what they want, but to my mind that would be a<br /> massive waste of
resources,and probably imposing a substantial extra<br /> maintenance burden on the core committers.<br
/></blockquote><br/></div> I hear you and agree to some degree.<br /><br /> But at the same time I remember that one of
thestrengths of Postgres used to be to be able to incorporate "new" ideas.<br /><br /> This seems to be one of those
cases.<br/><br /> Instead of "fork" plpgsql2, what about designing a completely new PL/postgres from scratch? It will
onlytake 3-10 years, but I bet it will be worth it after all. And I mean that. No sarcasm.<div class="im HOEnZb"><br
/></div></blockquote></div><br/></div><div class="gmail_extra">And how it would be better then already existing
plperl/plpython?<br/><br /></div><div class="gmail_extra">- Szymon<br /></div></div> 

On Wed, Sep 3, 2014 at 7:54 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> I am not against to improve a PL/pgSQL. And I repeat, what can be done and
> can be done early:
>
> a) ASSERT clause -- with some other modification to allow better static
> analyze of DML statements, and enforces checks in runtime.
>
> b) #option or PRAGMA clause with GUC with function scope that enforce check
> on processed rows after any DML statement
>
> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> DIAGNOSTICS rc = ROW_COUNT
>
> If you need more, and some users would more, then it job for new language
> really.

You fail to illustrate *why* it's a job for a new language.
All improvements suggested above are possible with plpgsql, and *should*
be improved in plpgsql, that I agree with.

But the 100% backwards-compatibiity ambition puts hard limits on
what's possible,
and if we can accept (100%-X) backwards compatibility where X is a small number,
then so much more ideas are possible, and that's why plpgsql2 is a good idea.

Hopefully, most of the plpgsql2 changes can be turned on/off in
plpgsql with PRAGMA clause with GUC,
but will be more messy than a good decent default behaviour.

I'm in favour of Tom's idea. To merely make the plpgsql2 "language" a
way of explicitly saying you want
a specific exact combination of features/beaviour/settings which we
can implemented in plpgsql's existing codebase.

Since it was about 100 posts since Tom's post, maybe it's worth
repeating for those who missed it:

> 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

I fully agree on this approach. It's maintainable and it will be
useful from day 1.

On Wed, Sep 3, 2014 at 10:07 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> When you use name plpgsql2 you say, so plpgsql2 is successor plpgsql. It is
> very hard to accept it.  So any other name is not problem for me - like
> plpgsql-safe-subset or something else

plpgsql2 *is* the successor of plpgsql, that's why it has a "2" in the name.
Anything which is very different from plpgsql should instead get a
different name.

For all new users, having a convenient shorthand (plpgsql2) for
enabling what ever the
project think is the best-practice collection of pragmas, is a simple
and efficient way
of helping new users to get the best possible behaviour of the
language, when starting
from scratch.
It also simplifies communication among developers, when they talk
about code written in plpgsql2,
they will all eventually know what they mean, instead of having to
describe what collection of pragmas
they use in their code. That also simplifies code examples, but most
importantly, one does not have
to declare all the pragmas for each function, or worry about the
pragmas in the config file will ever change.

Once we have agreed upon plpgsql2, then it will be a constant, and
never break compatibility,
and that's a good thing. Then we can all write new code according to
the updated specs and
hopefully we will not need a plpgsql3 until year 2030.

On 09/02/2014 03:50 PM, Jan Wieck wrote:

> PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a Ada/COBOL
> lookalike.
>
> Instead of trying to mimic what it was or a T-SQL thing instead ...
> maybe it is time to come up with a true PostgreSQL specific PL for a
> change?
>
> Just for the sake of being something new, and not a copy of some old
> opossum, that's rotting like road kill on the side of the highway for a
> decade already.
>

Well, I don't think PostgreSQL needs its own PL. I mean we already have 
several (what other database has pl/javascript or pl/python?)

Besides, the idea of this community trying to build its own programming 
language... oh lord ;)

JD


>
> Jan
>


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

On 09/02/2014 04:01 PM, Álvaro Hernández Tortosa wrote:

>      It's not copying. It's easying a path for others to migrate and
> come to Postgres.
>
>      I'm interested why you are more interested in MSSQL. My reasons for
> being interested in Oracle are:
>
> - It has more users (biggest and above all, the main reason: we could
> attract more)
> - Postgres is perceived as "similar" to Oracle (so migration is likely
> to be easier)
>
>      That's all I want. Grow postgres userbase, attracting Oracle users :)

I find that we have more opportunity to replace MSSQL than Oracle. 
Obviously it depends on a lot of things but my goal is as yours, just 
with a different database.

JD


>
>      Álvaro
>
>


-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

On Wed, Sep 3, 2014 at 3:17 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Well, I don't think PostgreSQL needs its own PL. I mean we already have
> several (what other database has pl/javascript or pl/python?)

PostgreSQL already *have* it's own PL, it's called PL/pgSQL.

> Besides, the idea of this community trying to build its own programming
> language... oh lord ;)

I would agree it's too much of a challenge to invent a brand new
programming language,
I agree that's unrealistic, that's why I'm opting to do as much as
possible in the existing
language, and carefully think about what non-compatible important
changes we simply
cannot make to PL/pgSQL, as they by definition would break
compatibility (which we all
agree is not acceptable), but that *would* be possible with PL/pgSQL 2.

On 09/03/2014 03:14 AM, Joel Jacobson wrote:
> I'm in favour of Tom's idea. To merely make the plpgsql2 "language" a
> way of explicitly saying you want
> a specific exact combination of features/beaviour/settings which we
> can implemented in plpgsql's existing codebase.
>
> Since it was about 100 posts since Tom's post, maybe it's worth
> repeating for those who missed it:
>
>> 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
>
> I fully agree on this approach. It's maintainable and it will be
> useful from day 1.

One can take that approach to another, more generic level. Like GUCs can 
be set on a ROLE base with ALTER USER or ALTER ROLE, PL specific GUCs 
could be set via "ALTER LANGUAGE foo SET ...".

The possibility to CREATE LANGUAGE mybetterpl, pointing to the same PL 
handler function, exists already. And the same mechanism could be used 
by other languages, like PL/Python (for whatever such language might 
need such settings).

This way an application can define the language settings, it needs, by 
simply creating its own language, based on all the possible permutations 
of those PRAGMA/GUC settings.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On Tue, Sep  2, 2014 at 08:46:36PM -0400, Christopher Browne wrote:
> 3.  Is there anything to be learned from Tutorial D?  That is, Date & Darwen's
> would-be alternative to SQL of their Third Manifesto?

What would a set-oriented-language PL look like, such as APL?  I guess
Perl has arrays, so it is kind-of set-oriented.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
> I am not against to improve a PL/pgSQL. And I repeat, what can be done and can
> be done early:
> 
> a) ASSERT clause -- with some other modification to allow better static analyze
> of DML statements, and enforces checks in runtime.
> 
> b) #option or PRAGMA clause with GUC with function scope that enforce check on
> processed rows after any DML statement
> 
> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> DIAGNOSTICS rc = ROW_COUNT

All these ideas are being captured somewhere, right?  Where?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On 9/3/14 5:05 PM, Bruce Momjian wrote:
> On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
>> I am not against to improve a PL/pgSQL. And I repeat, what can be done and can
>> be done early:
>>
>> a) ASSERT clause -- with some other modification to allow better static analyze
>> of DML statements, and enforces checks in runtime.
>>
>> b) #option or PRAGMA clause with GUC with function scope that enforce check on
>> processed rows after any DML statement
>>
>> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
>> DIAGNOSTICS rc = ROW_COUNT
>
> All these ideas are being captured somewhere, right?  Where?

I'm working on a wiki page with all these ideas.  Some of them break 
backwards compatibility somewhat blatantly, some of them could be added 
into PL/PgSQL if we're okay with reserving a keyword for the feature. 
All of them we think are necessary.


.marko

On 03/09/14 15:24, Joshua D. Drake wrote:
>
> On 09/02/2014 04:01 PM, Álvaro Hernández Tortosa wrote:
>
>>      It's not copying. It's easying a path for others to migrate and
>> come to Postgres.
>>
>>      I'm interested why you are more interested in MSSQL. My reasons for
>> being interested in Oracle are:
>>
>> - It has more users (biggest and above all, the main reason: we could
>> attract more)
>> - Postgres is perceived as "similar" to Oracle (so migration is likely
>> to be easier)
>>
>>      That's all I want. Grow postgres userbase, attracting Oracle 
>> users :)
>
> I find that we have more opportunity to replace MSSQL than Oracle. 
> Obviously it depends on a lot of things but my goal is as yours, just 
> with a different database.

    Honestly, I don't care whether MSSQL or Oracle. What I want is to 
attract more users, get Postgres out of where it is and appeal even more 
users. With that regard, Oracle or MSSQL doesn't matter to me.
    That's why if you have some time, I'd love to listen to why do you 
think there is more opportunity to replace MSSQL. We may continue that 
privately as is a little bit off-topic.
    Thanks!
    Álvaro

On Tue, Sep 2, 2014 at 5:47 PM, Álvaro Hernández Tortosa <aht@nosys.es> wrote:
>     Yeah, we differ there. I think having an Oracle compatibility layer in
> PostgreSQL would be the-next-big-thing we could have. Oracle is has orders
> of magnitude bigger user base than postgres has; and having the ability to
> attract them would bring us many many more users which, in turn, would
> benefit us all very significantly.
>
>     It would be my #1 priority to do in postgres (but yes, I know -guess-
> how hard and what resources that would require). But dreaming is free :)

There are a number of reasons why this isn't really practical.

First, Oracle compatibility isn't one feature.  The compatibility
"layer" that exists in EnterpriseDB's Advanced Server product consists
of many different changes to many different parts of the system.  A
few of those changes are simple syntax compatibility, where we do the
exact same thing PostgreSQL does but with different syntax, but a lot
of them are functional enhancements.  Even within SPL, there's a whole
bunch of different changes to a whole bunch of different areas, and
most of those are functional enhancements rather than just tweaking
syntax.  So, if you tried to implement a new, Oracle-compatible PL,
you'd find that you don't have one or a small number of changes to
make, but a long series of features ranging from small to very large.
You'd also find that adding a new PL, without changing any other parts
of the server, only bridges a small part of the compatibility gap.

Second, if you did manage to develop something which was significantly
more compatible with Oracle than PostgreSQL or PL/pgsql is today,
you'd probably find that the community wouldn't accept it.  It's
almost misleading to think of Oracle as a database; it's an enormous
software ecosystem with facilities for doing just about everything
under the sun, and many of those things more than one way.  For
example, in 9.4, EnterpriseDB will be releasing a UTL_HTTP package
that contains many of the same interfaces that are present in Oracle.
The interface decisions made by Oracle Corporation are reasonable in
view of their architecture, but I am quite sure that this community
would not want, for example, to return long text values as SETOF
VARCHAR(2000) rather than TEXT, just because Oracle does that.  And
rightly so: I wouldn't want PostgreSQL to follow any other product
that slavishly whether I worked at EnterpriseDB or not.  This kind of
thing crops up over and over again, and it only works to say that
PostgreSQL should choose the Oracle behavior in every case if you
believe that the primary mission of PostgreSQL should be to copy
Oracle, and I don't.  I also don't think it's a bad thing that
Advanced Server makes different decisions than PostgreSQL in some
cases.  A further problem is that, in this particular case, you'd
probably here the argument from PostgreSQL hackers that they really
don't want to be burdened with maintaining an HTTP client in the core
server when the same thing could be done from an extension, and that's
a valid argument, too.  It is also valid for EnterpriseDB to make a
different decision for itself, based on business priorities.

Now, none of that is to say that we wouldn't do well to give a little
more thought to Oracle compatibility than we do.  We've either made or
narrowly avoided a number of decisions over the years which introduced
- or threatened to introduce - minor, pointless incompatibilities with
other database products, Oracle included.  That really doesn't benefit
anyone.  To take another example, I've been complaining about the fact
that PostgreSQL 8.3+ requires far more typecasts in stored procedures
than any other database I'm aware of for years, probably since before
I joined EnterpriseDB.  And I still think we're kidding ourselves to
think that we've got that right when nobody else is doing something
similar.  I don't think the community should reverse that decision to
benefit EnterpriseDB, or to be compatible with Oracle: I think the
community should reverse that decision because it's stupid, and the
precedent of other systems demonstrates that it is possible to do
better.  Oracle's handling of reserved words also seems to be
considerably less irritating than ours, and I'd propose that we
improve that in PostgreSQL too, if I knew how to do it.
Unfortunately, I suspect that requires jettisoning bison and rolling
our own parser generator, and it's hard to argue that would be a good
investment of effort for the benefit we'd get.

Anyway, to get back around to the topic of PL/SQL compatibility
specifically, if you care about that issue, pick one thing that exists
in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
it'll be something that EnterpiseDB has already done something about,
in which case, if your patch gets committed, Advanced Server will lose
a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
something that EnterpriseDB hasn't done anything about, and then
everybody comes out strictly ahead.  What I think you shouldn't do
(although you're free to ignore me) is continue thinking of Oracle
compatibility as one monolithic thing, because it isn't, or to pursue
of a course of trying to get the PostgreSQL community to slavishly
follow Oracle, because I think you'll fail, and even if you succeed I
don't think the results will actually be positive for PostgreSQL.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

This is more of an SQL request the pl/pgsql but is/has there been thought to
adding the ternary if/then opeator?  Something like:

boolean_exp ?> val_if_true : val_if_false

using "?" by itself would be OK but not ideal - and the addition of the ">"
doesn't seem hateful...

Sorry if this is deemed off-topic but I just went to write

CASE WHEN boolean_exp THEN val_if_true ELSE val_if_false END

And the fact there is as much standard code as there is custom bothered me
just as is being discussed on this thread.

I'm going to go write a "ifthen(bool, anyelement, anyelement)" function
now....

David J.




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

On 09/03/2014 05:09 PM, Marko Tiikkaja wrote:
> On 9/3/14 5:05 PM, Bruce Momjian wrote:
>> On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
>>> I am not against to improve a PL/pgSQL. And I repeat, what can be
>>> done and can
>>> be done early:
>>>
>>> a) ASSERT clause -- with some other modification to allow better
>>> static analyze
>>> of DML statements, and enforces checks in runtime.
>>>
>>> b) #option or PRAGMA clause with GUC with function scope that
>>> enforce check on
>>> processed rows after any DML statement
>>>
>>> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
>>> DIAGNOSTICS rc = ROW_COUNT
>>
>> All these ideas are being captured somewhere, right?  Where?
>
> I'm working on a wiki page with all these ideas.  Some of them break
> backwards compatibility somewhat blatantly, some of them could be
> added into PL/PgSQL if we're okay with reserving a keyword for the
> feature. All of them we think are necessary.

Ok, here are my 0.5 cents worth of proposals for some features discussed
in this thread

They should be backwards compatible, but perhaps they are not very
ADA/SQL-kosher  ;)

They also could be implemented as macros first with possible
optimisations in the future


1. Conditions for number of rows returned by SELECT or touched by UPDATE
or DELETE
---------------------------------------------------------------------------------------------------------

Enforcing number of rows returned/affected could be done using the
following syntax which is concise and clear (and should be in no way
backwards incompatible)

SELECT[1]   - select exactly one row, anything else raises error
SELECT[0:1]   - select zero or one rows, anything else raises error
SELECT[1:] - select one or more rows

plain SELECT is equivalent to SELECT[0:]

same syntax could be used for enforcing sane affected row counts
for INSERT and DELETE


A more SQL-ish way of doing the same could probably be called COMMAND
CONSTRAINTS
and look something like this

SELECT
...
CHECK (ROWCOUNT BETWEEN 0 AND 1);



2. Substitute for EXECUTE with string manipulation
----------------------------------------------------------------

using backticks `` for value/command substitution in SQL as an alternative
to EXECUTE string

Again it should be backwards compatible as , as currently `` are not
allowed inside pl/pgsql functions

Sample 1:

ALTER USER `current_user` PASSWORD newpassword;

would be expanded to

EXECUTE 'ALTER USER ' || current_user ||               ' PASSWORD = $1' USING newpassword;

Sample2:

SELECT * FROM `tablename` WHERE "`idcolumn`" = idvalue;

this could be expanded to

EXECUTE 'SELECT * FROM ' || tablename ||               ' WHERE quote_ident(idcolumn) = $1' USING idvalue;

Notice that the use of "" around `` forced use of quote_ident()


3. A way to tell pl/pggsql not to cache plans fro normal queries
-----------------------------------------------------------------------------------

This could be done using a #pragma or special /* NOPLANCACHE */
comment as suggested by Pavel

Or we could expand the [] descriptor from 1. to allow more options

OR we could do it in SQL-ish way using like this:

SELECT
...
USING FRESH PLAN;


Best Regards

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

Hannu Krosing <hannu@2ndQuadrant.com> wrote:

> [suggested syntax]

Interesting.  The only one that really offends me is:

> SELECT * FROM `tablename` WHERE "`idcolumn`" = idvalue;

I think that should be:

SELECT * FROM `tablename` WHERE `"idcolumn"` = idvalue;

i.e., I think the backticks belong on the outside.


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

On 2014-09-03 23:19, Hannu Krosing wrote:
> 1. Conditions for number of rows returned by SELECT or touched by UPDATE
> or DELETE
> ---------------------------------------------------------------------------------------------------------
>
> Enforcing number of rows returned/affected could be done using the
> following syntax which is concise and clear (and should be in no way
> backwards incompatible)
>
> SELECT[1]   - select exactly one row, anything else raises error
> SELECT[0:1]   - select zero or one rows, anything else raises error
> SELECT[1:] - select one or more rows
>
> plain SELECT is equivalent to SELECT[0:]
>
> same syntax could be used for enforcing sane affected row counts
> for INSERT and DELETE

I'm not sure how much I like that syntax in cases like:
  WITH t AS (    -- multi-line query here  )  SELECT[0:] foo, bar  INTO _bat, _man  FROM foo  JOIN ..  JOIN ..  WHERE
.. -- etc.
 

It seems quite well hidden compared to a single keyword at the beginning 
of the query.

It's also not clear whether all of this flexibility is required. 
Enforcing "exactly one" conveniently is my main priority.  Supporting 
the "at most one" case could be nice, too, but anything else feels like 
overkill.  Though if the syntax is based on numbers (and not a keyword), 
then I guess we get the flexibility for free anyway.

I also have my doubts about how easy it would be to implement this 
syntax given that we're using the "real" SQL parser.


.marko

On 09/03/2014 11:48 AM, Robert Haas wrote:

> Anyway, to get back around to the topic of PL/SQL compatibility
> specifically, if you care about that issue, pick one thing that exists
> in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
> it'll be something that EnterpiseDB has already done something about,
> in which case, if your patch gets committed, Advanced Server will lose
> a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
> something that EnterpriseDB hasn't done anything about, and then
> everybody comes out strictly ahead.  What I think you shouldn't do
> (although you're free to ignore me) is continue thinking of Oracle
> compatibility as one monolithic thing, because it isn't, or to pursue
> of a course of trying to get the PostgreSQL community to slavishly
> follow Oracle, because I think you'll fail, and even if you succeed I
> don't think the results will actually be positive for PostgreSQL.

Well put Robert.

JD



-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should             not be surprised when they come back as
Romans."

On 09/04/2014 12:17 AM, Marko Tiikkaja wrote:
> On 2014-09-03 23:19, Hannu Krosing wrote:
>> 1. Conditions for number of rows returned by SELECT or touched by UPDATE
>> or DELETE
>> ---------------------------------------------------------------------------------------------------------
>>
>>
>> Enforcing number of rows returned/affected could be done using the
>> following syntax which is concise and clear (and should be in no way
>> backwards incompatible)
>>
>> SELECT[1]   - select exactly one row, anything else raises error
>> SELECT[0:1]   - select zero or one rows, anything else raises error
>> SELECT[1:] - select one or more rows
>>
>> plain SELECT is equivalent to SELECT[0:]
>>
>> same syntax could be used for enforcing sane affected row counts
>> for INSERT and DELETE
>
> I'm not sure how much I like that syntax in cases like:
>
>   WITH t AS (
>     -- multi-line query here
>   )
>   SELECT[0:] foo, bar
>   INTO _bat, _man
>   FROM foo
>   JOIN ..
>   JOIN ..
>   WHERE ..
>   -- etc.
>
> It seems quite well hidden compared to a single keyword at the
> beginning of the query.
What do you have in mind ?

Is your wiki page already available somewhere ?
>
> It's also not clear whether all of this flexibility is required.
> Enforcing "exactly one" conveniently is my main priority.
What do you want here on top of SELECT ... INTO STRICT ... ?
> Supporting the "at most one" case could be nice, too, but anything
> else feels like overkill.  Though if the syntax is based on numbers
> (and not a keyword), then I guess we get the flexibility for free anyway.
>
> I also have my doubts about how easy it would be to implement this
> syntax given that we're using the "real" SQL parser.
Definitely not trivial, but at least doable :)

Finding and processing SELECT[...] could probably even be done with
a (regex-based ?) pre-parser .


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

On Wed, Sep 3, 2014 at 11:19 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
> SELECT[1]   - select exactly one row, anything else raises error
> SELECT[0:1]   - select zero or one rows, anything else raises error
> SELECT[1:] - select one or more rows
>
> plain SELECT is equivalent to SELECT[0:]
>
> same syntax could be used for enforcing sane affected row counts
> for INSERT and DELETE

+1 for a new fresh creative idea! My mind was stuck in a "looking for
keywords" state.

The only suggestion I think is slightly better is the "STRICT UPDATE",
but only if I'm right assuming the "one row" use-case is much more
common than the "zero or one rows" and "one or more rows" use-cases.

If all use-cases are equally important to support in a nice way,
then the suggested syntax is brilliant, as it supports all of them.

Bonus points for the extremely condensed syntax.

> 2. Substitute for EXECUTE with string manipulation
> ----------------------------------------------------------------
>
> using backticks `` for value/command substitution in SQL as an alternative
> to EXECUTE string

+1 for being backwards compatible, but I have no strong opinion.

> 3. A way to tell pl/pggsql not to cache plans fro normal queries
> -----------------------------------------------------------------------------------

> OR we could do it in SQL-ish way using like this:
>
> SELECT
> ...
> USING FRESH PLAN;

+1 for clean syntax

<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">2014-09-04 9:37 GMT+02:00 Joel
Jacobson<span dir="ltr"><<a href="mailto:joel@trustly.com" target="_blank">joel@trustly.com</a>></span>:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">On
Wed,Sep 3, 2014 at 11:19 PM, Hannu Krosing <<a href="mailto:hannu@2ndquadrant.com">hannu@2ndquadrant.com</a>>
wrote:<br/> > SELECT[1]   - select exactly one row, anything else raises error<br /> > SELECT[0:1]   - select
zeroor one rows, anything else raises error<br /> > SELECT[1:] - select one or more rows<br /> ><br /> > plain
SELECTis equivalent to SELECT[0:]<br /> ><br /> > same syntax could be used for enforcing sane affected row
counts<br/> > for INSERT and DELETE<br /><br /></div>+1 for a new fresh creative idea! My mind was stuck in a
"lookingfor<br /> keywords" state.<br /><br /> The only suggestion I think is slightly better is the "STRICT
UPDATE",<br/> but only if I'm right assuming the "one row" use-case is much more<br /> common than the "zero or one
rows"and "one or more rows" use-cases.<br /><br /> If all use-cases are equally important to support in a nice way,<br
/>then the suggested syntax is brilliant, as it supports all of them.<br /><br /> Bonus points for the extremely
condensedsyntax.<br /><div class=""><br /> > 2. Substitute for EXECUTE with string manipulation<br /> >
----------------------------------------------------------------<br/> ><br /> > using backticks `` for
value/commandsubstitution in SQL as an alternative<br /> > to EXECUTE string<br /><br /></div>+1 for being backwards
compatible,but I have no strong opinion.<br /><div class=""><br /> > 3. A way to tell pl/pggsql not to cache plans
fronormal queries<br /> > -----------------------------------------------------------------------------------<br
/><br/></div><div class="">> OR we could do it in SQL-ish way using like this:<br /> ><br /> > SELECT<br />
>...<br /> > USING FRESH PLAN;<br /><br /></div>+1 for clean syntax<br /></blockquote></div><br /></div><div
class="gmail_extra">wehave totally different opinion what is good<br /><br /></div><div class="gmail_extra">Regards<br
/><br/>Pavel<br /></div></div> 

On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> we have totally different opinion what is good

Can you elaborate on that?

Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like it.
Imagine if having to type
my $var =========================== 'foo';
instead of
my $var = 'foo';
on every single line of could where you want to assign a variable,
that would just be ridiculous.

If you have a typical CRUD application and decide to do *all* data
operations via PL functions,
which is a design pattern advocated by many*, then you will end up
with a lot of very simple
short PL functions, to do things like update_worker_status(),
set_notification_response(), etc,
in which you always pass something which is a primary key in some
table, and want to update
exactly one row. Having to type 27 extra characters for every single
line of code, instead of the
suggested 3 extra characters, is a big difference, for anyone who
designs a CRUD application
which relies on the usage of PL functions.

For me, it would be useful to understand if you are developing CRUD
applications,
or if your main usage for PL/pgSQL functions are other things?

If the latter, then maybe that could explain why you don't feel strongly about
simplifying and condensing the syntax for the most common use-case of them all.

*) but there are probably equally who prefer to handle business logics
outside the database

On 9/4/14 2:10 AM, Hannu Krosing wrote:
> On 09/04/2014 12:17 AM, Marko Tiikkaja wrote:
>> I'm not sure how much I like that syntax in cases like:
>>
>>    WITH t AS (
>>      -- multi-line query here
>>    )
>>    SELECT[0:] foo, bar
>>    INTO _bat, _man
>>    FROM foo
>>    JOIN ..
>>    JOIN ..
>>    WHERE ..
>>    -- etc.
>>
>> It seems quite well hidden compared to a single keyword at the
>> beginning of the query.
> What do you have in mind ?

I'm just comparing it to the  STRICT UPDATE ..;  and  ONE ROW UPDATE ..;  syntaxes proposed earlier.

> Is your wiki page already available somewhere ?

I'm working on getting it up ASAP; hopefully it will be today around the 
UTC noon.


.marko

On 9/4/14 10:42 AM, Pavel Stehule wrote:
> 2014-09-04 10:06 GMT+02:00 Joel Jacobson <joel@trustly.com>:
>> *) but there are probably equally who prefer to handle business logics
>> outside the database
>>
> It is maybe main difference between me and you. Usually I don't write CRUD
> applications, and I am not sure if plpgsql is good for CRUD.
>
> Mainly I would not to optimize plpgsql primary for CRUD.

I don't think providing syntax to support the CRUD-like use case would 
be "optimizing it primarily for CRUD".  Changing how UPDATE and DELETE 
work by default would be, but that's not being suggested here (anymore).


.marko

On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> I am strong in opinion so PLpgSQL is targeted primary for implementation
> business logic in server side. CRUD  is only one from possible use cases -
> and without any special importance to others.

Just curious, what kind of business logic do you write, where "one row"
is not more special to you than "two rows" or "five rows"?

Think about for a second what PL functions are able to return.

Compare,
RETURNS RECORD
vs
RETURNS SETOF RECORD

When you return from a function, you get exactly 1 row, unless
you explicitly use the SETOF RECORD syntax, where 0...n rows are possible.

To add to that, we have the STRICT keyword, which also recognize the
fact 1 row is special.

So, we already *have* special handling for the 1 row case in many areas.

I cannot see how you can fail to agree it would be a good thing to
make it simple also for UPDATE/DELETE/INSERT.



On Thu, Sep 4, 2014 at 10:42 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
>
> 2014-09-04 10:06 GMT+02:00 Joel Jacobson <joel@trustly.com>:
>
>> On Thu, Sep 4, 2014 at 9:39 AM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>> > we have totally different opinion what is good
>>
>> Can you elaborate on that?
>
>
> I would to elaborate on enhancing plpgsql - but my primary target is
> readability without necessity of special special statements, types.
>
> I am strong against to create some shortcuts for relative too special use
> case.
>
>>
>>
>> Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like
>> it.
>> Imagine if having to type
>> my $var =========================== 'foo';
>> instead of
>> my $var = 'foo';
>> on every single line of could where you want to assign a variable,
>> that would just be ridiculous.
>>
>> If you have a typical CRUD application and decide to do *all* data
>> operations via PL functions,
>> which is a design pattern advocated by many*, then you will end up
>> with a lot of very simple
>> short PL functions, to do things like update_worker_status(),
>> set_notification_response(), etc,
>> in which you always pass something which is a primary key in some
>> table, and want to update
>> exactly one row. Having to type 27 extra characters for every single
>> line of code, instead of the
>> suggested 3 extra characters, is a big difference, for anyone who
>> designs a CRUD application
>> which relies on the usage of PL functions.
>
>
> Is not better to design special PL for this usage? I understand to your
> motivation, but it is not acceptable for me in plpgsql.
>
> Ten years ago, we had to solve similar problem - and we designed
> metalanguage that was translated to plpgsql.
>
>>
>>
>> For me, it would be useful to understand if you are developing CRUD
>> applications,
>> or if your main usage for PL/pgSQL functions are other things?
>
>
> I am strong in opinion so PLpgSQL is targeted primary for implementation
> business logic in server side. CRUD  is only one from possible use cases -
> and without any special importance to others.
>
>>
>>
>> If the latter, then maybe that could explain why you don't feel strongly
>> about
>> simplifying and condensing the syntax for the most common use-case of them
>> all.
>
>
> I don't agree so what you propose, it is common use case. And I don't think
> so it can be used in synergy with current design
>
>>
>>
>> *) but there are probably equally who prefer to handle business logics
>> outside the database
>
>
> It is maybe main difference between me and you. Usually I don't write CRUD
> applications, and I am not sure if plpgsql is good for CRUD.
>
> Mainly I would not to optimize plpgsql primary for CRUD.
>
>

On Thu, Sep 4, 2014 at 11:07 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> it is different semantic - returns composite or set of composites  --- it is
> not row or rows

The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n.

> Actually BL is usually processed oriented, so PL functions coverages changes
> in data, and for queries you use SELECT

OK, so you SELECT directly from tables?
And in the PLs you change a lot of rows in the same txn?

> Returning SET from function is less often - and usually it is not in
> preferred patterns because you can very simple block a optimizer.

Not if you do all access, also SELECT via PLs, then you might want to returns
lists of things based on some input.

But that's a different topic. What I wanted to examplify is the fact
we *already*
have a lot of syntax which handles the 1 row case in a special way.

Everyone,

I've started a wiki page with the list of the things I could think of at 
this very moment.  I probably got the most annoying ones in there, but I 
also might have forgotten about some things.  I invite discussion of 
every suggestion on -HACKERS.

https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014)

I'm sure other people's lists would look different; perhaps they should 
be added to the same page?  Should we divide it based on who's 
suggesting the change to keep a better track?

Anyway, you can start shooting now that we have at least one list of 
concrete proposals.


.marko

On 9/4/14 1:47 PM, Pavel Stehule wrote:
> 2014-09-04 13:37 GMT+02:00 Marko Tiikkaja <marko@joh.to>:
>> I've started a wiki page with the list of the things I could think of at
>> this very moment.  I probably got the most annoying ones in there, but I
>> also might have forgotten about some things.  I invite discussion of every
>> suggestion on -HACKERS.
>
> where I can wrote comments?

I guess comments could be added as a subsection if you feel -HACKERS 
doesn't work.

> I am sorry, It is difficult - your usage of plpgsql is sometimes less, some
> times more against best practices :(

Best practices according to whom?  And which parts, exactly?  There's 
quite a lot of stuff in there, both explicitly stated and implicitly 
assumed.

> PL functions should not be only envelope to SQL statement

I disagree, to some extent.  Our "external" applications interface with 
the database only by calling functions (which are often written in 
PL/PgSQL).  If that function has no more work to do than to run a single 
query, then yes, the function will just serve as an envelope to a single 
query.  But that also allows the function to be changed in the future 
without having to change the application.  Similarly to how you would 
expose an API when communicating with others instead of giving them a 
postgres user account and saying "update these tables" and after a month 
saying "oh, you need to remember to INSERT into this table as well or we 
won't have logs of what you did".


.marko

On Thu, Sep 4, 2014 at 4:06 AM, Joel Jacobson <joel@trustly.com> wrote:
> Your "ASSERT CHECK ROWCOUNT = 1;" is lengthly, which is why I don't like it.
> Imagine if having to type
> my $var =========================== 'foo';
> instead of
> my $var = 'foo';
> on every single line of could where you want to assign a variable,
> that would just be ridiculous.

This is really the core of the problem.  PL/pgsql is an incredibly
verbose language, and things that ought to be expressable in a small
number of characters often take a large number of characters.  The
reason this problem is hard to solve is that PL/pgsql is based on SQL,
and SQL is inherently pretty verbose.  When we try to extend PL/pgsql,
we can either pick syntax that looks like the existing language (which
sucks because it's verbose) or we can pick syntax that is compact
(which sucks because it looks nothing like the rest of the language).
This is a deep and fundamental conflict that is not going away.

So I'm with the crowd of people who says there's really nothing that
can be done to improve PL/pgsql much.  Yeah, we could make certain
cases a little better at the expense of certain other cases (which is
how this thread got started), but it's not really clear that we'd end
up ahead at all (since a lot of people objected to the proposed
changes) and if we did we wouldn't end up very far ahead (since the
original proposal consisted of three minor items which are not going
to radically transform anyone's experience).  Even Oracle, who has a
vastly larger R&D budget than the PostgreSQL community, frankly hasn't
done all that much with it.  They have some nice conveniences which we
lack, but it's still a clunky language.

So, I think the right solution here is to work on improving the user
experience people have with other procedural languages.  Perl, Python,
and Javascript are all notable for being terse, sometimes to excess.
I remember trying to use PL/perl many years ago and giving up on it
because of various problems, like not being able to get "require" to
work, and not being able to create helper functions that could be
called directly from Perl without going back through the SQL layer.
But some of those kinds of things may have been fixed between then and
now, and whichever ones haven't probably can be if people are willing
to put in some work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 9/4/14 2:04 PM, Pavel Stehule wrote:
> for example best practices for PL/SQL by Steven Feuerstein

I'll spend some time with that book to have a better idea on where 
you're coming from.

Also, *please* don't try and extrapolate what I do based on the code 
examples on the wiki page; they're all crap just to point out the issues.

> We can talk about it - it subjective and I know so there are not only one
> style.
>
> The language has these possibilities. Why to add new redundant?

Adding a new alias for every single OUT parameter for every single 
function seems like a waste of time.  It also doesn't improve 
readability in the way that  OUT.foo := 1;  does (though I guess you 
could add an "out_" prefix to all of them).


.marko

On 4 sep 2014, at 11:42, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2014-09-04 11:22 GMT+02:00 Joel Jacobson <joel@trustly.com>:
>
> The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n.
>

no RETURNS return "VALUE" (it is not a row) .. and in combination with
SELECT - value will be a row. RETURNS SETOF returns rows


I intentionally excluded the data type of what is returned.
1 "VALUE" vs 0...n "VALUES"
Do you still fail to see the point 1 "VALUE" is special in the context of
what a function returns?

On 09/01/2014 04:04 AM, Joel Jacobson 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.

What? No. The whole point of SQL is that it's set-based and can modify 
multiple rows at once. Hobbling it specifically for functions seems 
fundamentally flawed. Especially for what we purport to be a core 
PostgreSQL language.

> + Change all warnings into errors

I... what? I could see coming up with a better exception handling 
mechanism for escalating messages. But you're talking about taking a 
core element of PostgreSQL (warnings) and simply ripping them out so 
plpgsql2 loses even that small functionality.

I'm sure you've put a lot of thought into this, but you're not the only 
person using plpgsql or any, however ambitious, potential replacement.

-- 
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 09/04/2014 01:14 AM, Pavel Stehule wrote:
> 2014-09-03 23:19 GMT+02:00 Hannu Krosing <hannu@2ndquadrant.com
>     A more SQL-ish way of doing the same could probably be called COMMAND
>     CONSTRAINTS
>     and look something like this
>
>     SELECT
>     ...
>     CHECK (ROWCOUNT BETWEEN 0 AND 1);
>
>
> It is very near to my proposed ASSERT

Only if the ASSERT syntax would become part of the original statement, 
it is supposed to check. In Hannu's command constraint example above, 
the statement that causes the error, and thus will be logged and become 
identified by the error message, is the actual SELECT (or other DML 
statement).

I think I like the COMMAND CONSTRAINT the best so far.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On 09/04/2014 09:31 AM, Pavel Stehule wrote:
> 2014-09-04 15:24 GMT+02:00 Jan Wieck <jan@wi3ck.info
>
>     I think I like the COMMAND CONSTRAINT the best so far.
>
>
> I not, because when it will not be part of SQL, than parser in plpgsql
> will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE

Making the COMMAND CONSTRAINT part of the core SQL parser was how I 
understood Hannu's idea. It would be horrible to tuck that feature away 
inside of a PL, rather than making it available to all PLs as well as 
applications, that use SQL directly (I think there still are two or 
three applications that do).


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On 09/03/2014 04:19 PM, Hannu Krosing wrote:

> 1. Conditions for number of rows returned by SELECT or touched by
> UPDATE or DELETE

Now that I think upon this... don't we already have it?

SELECT ... LIMIT 1

That already solves the purported problem of multiple results in SELECT 
INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too?

-- 
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 9/4/14 4:09 PM, Shaun Thomas wrote:
> On 09/03/2014 04:19 PM, Hannu Krosing wrote:
>
>> 1. Conditions for number of rows returned by SELECT or touched by
>> UPDATE or DELETE
>
> Now that I think upon this... don't we already have it?
>
> SELECT ... LIMIT 1

No, that just hides any bugs.  We want the opposite: any bugs or 
problems should be obvious.  If the query returns or touches more than 
one row, that should raise an error, not just give you a random one and 
call it a day.

> That already solves the purported problem of multiple results in SELECT
> INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too?

Again, this is a different problem, but LIMIT syntax for UPDATE and 
DELETE has been proposed, see: 
http://www.postgresql.org/message-id/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm=mg@mail.gmail.com


.marko

2014-09-04 14:37 GMT+02:00 Joel Jacobson <joel@trustly.com>:

On 4 sep 2014, at 11:42, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2014-09-04 11:22 GMT+02:00 Joel Jacobson <joel@trustly.com>:

The point was, RETURNS returns 1 while RETURNS SETOF returns 0 .. n.

no RETURNS return "VALUE" (it is not a row) .. and in combination with SELECT - value will be a row. RETURNS SETOF returns rows

I intentionally excluded the data type of what is returned.

1 "VALUE" vs 0...n "VALUES"

Do you still fail to see the point 1 "VALUE" is special in the context of what a function returns?

sorry, I don't understand .. for me SRF functions are absolutly different monsters than scalar, array or composite function - so its impossible to compare it.

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

> On 4 sep 2014, at 15:09, Shaun Thomas <sthomas@optionshouse.com> wrote:
>
>> On 09/01/2014 04:04 AM, Joel Jacobson 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.
>
> What? No. The whole point of SQL is that it's set-based and can modify multiple rows at once. Hobbling it
specificallyfor functions seems fundamentally flawed. Especially for what we purport to be a core PostgreSQL language. 

I've dropped that suggestion and is instead in favour of a keyword
like STRICT, ONE ROW, SINGLETON or [1] like suggested by others. Any
keyword or syntax will do fine, but I would prefer STRICT.

>
>> + Change all warnings into errors
>
> I... what? I could see coming up with a better exception handling mechanism for escalating messages. But you're
talkingabout taking a core element of PostgreSQL (warnings) and simply ripping them out so plpgsql2 loses even that
smallfunctionality. 

You misunderstood, I meant plpgsql warnings, that you currently can
turn into errors by setting things in the config file. Such as
shadowing of variables.

>
> I'm sure you've put a lot of thought into this, but you're not the only person using plpgsql or any, however
ambitious,potential replacement. 
>
> --
> Shaun Thomas
> OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> sthomas@optionshouse.com
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 4 sep 2014, at 15:32, Pavel Stehule <pavel.stehule@gmail.com> wrote:




2014-09-04 15:24 GMT+02:00 Jan Wieck <jan@wi3ck.info>:

> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>
>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing <hannu@2ndquadrant.com
>>     A more SQL-ish way of doing the same could probably be called COMMAND
>>     CONSTRAINTS
>>     and look something like this
>>
>>     SELECT
>>     ...
>>     CHECK (ROWCOUNT BETWEEN 0 AND 1);
>>
>>
>> It is very near to my proposed ASSERT
>>
>
> Only if the ASSERT syntax would become part of the original statement, it
> is supposed to check. In Hannu's command constraint example above, the
> statement that causes the error, and thus will be logged and become
> identified by the error message, is the actual SELECT (or other DML
> statement).
>

this is valid argument.

On second hand, I proposed a ASSERT that was not based on expressions only.
There is not a technical issue to write assert with knowledge of related
statement.


>
> I think I like the COMMAND CONSTRAINT the best so far.
>

I not, because when it will not be part of SQL, than parser in plpgsql will
be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE


This is what I suspected. You are against the best syntax because they are
more complex to implement. I think that's coming into the discussion from
the wrong direction. First agree on the best syntax, then worry about the
implementation.

I also understand the syntax changes will mean a lot of trouble for your
plpgsql_check_function() project, but that cannot hold us back, we must aim
for the best possible syntax with plpgsql2.
Your work with plpgsql_check_function() btw saved me hundreds of hours of
work, when we upgraded from 8.4 a few years ago, many thanks Pavel!


Pavel


>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

> On 4 sep 2014, at 16:45, Hannu Krosing <hannu@2ndquadrant.com> wrote:
>
> When looking from the other end of the problem, we are
> using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
> when we really want scalars.
>
> My understanding is that one main drivers of starting this thread
> was wanting also guaranteed SCALAR versions of these.
>
> And wanting them in a way that is easy to use.

+1

Thank you! I have been trying to explain this in multiple cryptic ways
but failed. You just nailed it! That's *exactly* what I mean!

Thanks for clarifying!

>
>
> Cheers
>
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic O=C3=9C

> On 4 sep 2014, at 17:18, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> You just need a ISAM API for Postgres, That is all.

Now you are being ironic, and I would prefer to keep the discussion on
a serious level. You know that's not applicable in my case, you know
what I do for work and what kind of system we already have.

I *love* plpgsql and our development method. I just want it to get
slightly more convenient and secure.

When you suggest ISAM, that's like saying "demolish your house and
build a new one" when all I want is to make small but important
changes to what I already do as a professional on a daily basis.

On 09/04/2014 11:16 AM, Joel Jacobson wrote:
>> On 4 sep 2014, at 16:45, Hannu Krosing <hannu@2ndquadrant.com> wrote:
>>
>> When looking from the other end of the problem, we are
>> using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
>> when we really want scalars.
>>
>> My understanding is that one main drivers of starting this thread
>> was wanting also guaranteed SCALAR versions of these.
>>
>> And wanting them in a way that is easy to use.
>
> +1
>
> Thank you! I have been trying to explain this in multiple cryptic ways
> but failed. You just nailed it! That's *exactly* what I mean!

I believe we all agree that the availability of most of the proposed 
functionality is desirable.

I think the main difference between your point of view and that of a few 
others (me included) is that you prefer a language that is easy and fast 
to type, with as few key strokes as possible, while we prefer a language 
that is similar to SQL, which is rather verbose to the reader. At least 
when the discussion is about the default procedural language installed 
with the core database system.

Such a language should be as similar as possible to SQL. Which is the 
reason why I believe that the CHECK clause belongs into the main parser, 
not into the PL.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

On Thu, Sep 4, 2014 at 11:32 AM, Joel Jacobson <joel@trustly.com> wrote:
>> On 4 sep 2014, at 17:18, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>> You just need a ISAM API for Postgres, That is all.
>
> Now you are being ironic, and I would prefer to keep the discussion on
> a serious level. You know that's not applicable in my case, you know
> what I do for work and what kind of system we already have.
>
> I *love* plpgsql and our development method. I just want it to get
> slightly more convenient and secure.
>
> When you suggest ISAM, that's like saying "demolish your house and
> build a new one" when all I want is to make small but important
> changes to what I already do as a professional on a daily basis.

Go right ahead: this is an open source project, after all, and with an
extremely permissive license to boot.  You can modify your copy of
PL/pgsql, or clone it and make PL/joelsql and then change whatever you
like.  Optionally, you could then publish that on PGXN for others to
use and contribute to.

On the other hand, if what you want is for other people to make
changes to the official versions of PostgreSQL that are supported and
maintained by the community, then that's a different thing altogether.
It entails two challenges: first, to persuade the community that those
changes will be good for everyone, not just you; and second,
convincing them that they (rather than you) should be the ones to do
the work.  So far I'd say you're losing the first argument, and I
expect you'll lose the second one, too (barring a financial
transaction, of course).

I'm not trying to brush you off here - I understand your concerns, and
they're not stupid.  But, like most of the people who have commented,
I don't agree that your proposals would be an improvement for the
majority of people.  There are several ways to deal with that, but if
your goal is to get those changes made in the PostgreSQL community
then you have to acknowledge the competing concerns to be just as
valid as your own and come up with a proposal everyone can live with.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 09/04/2014 02:48 AM, Robert Haas wrote:
> To take another example, I've been complaining about the fact
> that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> than any other database I'm aware of for years, probably since before
> I joined EnterpriseDB.

+10

This still drives me nuts, and it's a serious problem for ORM users too.

The idea that we won't accept a 'text' typed input for an 'xml' or
'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
an argument for what problems this solves.

I know why the changes in 8.3 were made, and they're clearly beneficial
overall, but we need to start putting some more implicit casts from text
to text-like types in, especially where there's no SQL-standard type
that users of JDBC etc can easily use in mappings.

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

On 9/4/14 5:54 PM, Craig Ringer wrote:
> On 09/04/2014 02:48 AM, Robert Haas wrote:
>> To take another example, I've been complaining about the fact
>> that PostgreSQL 8.3+ requires far more typecasts in stored procedures
>> than any other database I'm aware of for years, probably since before
>> I joined EnterpriseDB.
>
> +10
>
> This still drives me nuts, and it's a serious problem for ORM users too.
>
> The idea that we won't accept a 'text' typed input for an 'xml' or
> 'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
> an argument for what problems this solves.

In what context?  Are we talking about parameters which have been cast 
to text, or what?  I don't remember ever having an issue with this, 
though I remember the lack of implicit cast from text to json (or the 
other way round) making a bug more obvious a couple of times.


.marko

Pavel Stehule <pavel.stehule@gmail.com> wrote:

> You just need a ISAM API for Postgres, That is all.

Joel sure hasn't *shown* us anything to suggest that wouldn't
answer his needs better than any PL, or explained why that wouldn't
be a better solution for him.

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

On 09/04/2014 06:48 AM, Joshua D. Drake wrote:
> 
> On 09/03/2014 11:48 AM, Robert Haas wrote:
> 
>> Anyway, to get back around to the topic of PL/SQL compatibility
>> specifically, if you care about that issue, pick one thing that exists
>> in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
>> it'll be something that EnterpiseDB has already done something about,
>> in which case, if your patch gets committed, Advanced Server will lose
>> a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
>> something that EnterpriseDB hasn't done anything about, and then
>> everybody comes out strictly ahead.  What I think you shouldn't do
>> (although you're free to ignore me) is continue thinking of Oracle
>> compatibility as one monolithic thing, because it isn't, or to pursue
>> of a course of trying to get the PostgreSQL community to slavishly
>> follow Oracle, because I think you'll fail, and even if you succeed I
>> don't think the results will actually be positive for PostgreSQL.
> 
> Well put Robert.

Indeed, especially with reference to the size and scope of Oracle. Its
XML library alone is huge.

At best it's reasonable to hope for compatibility with a limited subset
of PL/SQL - and really, we're a good way there already, with most of
what's missing being down to missing core server features or things
PostgreSQL just does differently.

True "Oracle compatibility" (for procedures) pretty much requires an
embedded JVM with a rich class library. Since PL/Java seems to be dying
a slow death by neglect and disinterest I don't think it's likely anyone
would be tackling compatibility with the embedded JVM features anytime soon.

There are a few things I would like to see, like secure session
variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
compatibility" seems to be something that comes up before the speaker
has really understood what that would mean, and the sheer scope of the
endeavour.

It's not going from 50% compatible to 80% compatible, it's going from 5%
compatible to 7% compatible. The most used 5% maybe, but still...

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