Re: procedures and plpgsql PERFORM

[Pavel Stehule]

2017-12-15 4:43 GMT+01:00 Ashutosh Bapat :

> On Thu, Dec 14, 2017 at 10:16 PM, Pavel Stehule 
> wrote:
> >
> >
> > 2017-12-14 17:10 GMT+01:00 David G. Johnston  >:
> >>
> >> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure 
> >> wrote:
> >>>
> >>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
> >>> > Ashutosh Bapat  writes:
> >>> >> We allow a function to be invoked as part of PERFORM statement in
> >>> >> plpgsql
> >>> >> ...
> >>> >> But we do not allow a procedure to be invoked this way
> >>> >
> >>> >> Procedures fit that category and like functions, I think, we should
> >>> >> allow them be invoked directly without any quoting and CALL
> >>> >> decoration.
> >>> >
> >>> > How is that going to work?  What if the procedure tries to commit the
> >>> > current transaction?
> >>> >
> >>> > IOW, this is not merely a syntactic-sugar question.
> >>>
> >>> BTW, We've already come to (near-but good enough) consensus that
> >>> PERFORM syntax is really just unnecessary, and I submitted a patch to
> >>> make it optional (which I really need to dust off and complete).
> >>
> >>
> >> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword
> to
> >> specify a specific limited form of the SQL SELECT command.  CALL is an
> SQL
> >> command.  I don't see any real upside to allowing pl/pgsql to accept
> >> omission of the command tag while SQL cannot - at least not without a
> >> use-case describe why such syntax would be beneficial.  And likely
> those use
> >> cases would revolve around some looping variant as opposed to a single
> >> stand-alone, result-less, CALL.
> >>
> >> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
> >> enhancement:
> >> PERFORM func() => SELECT func()
> >> PERFORM proc() => CALL proc()
> >
> >
> > I don't like this idea - functions are not procedures - can be nice if it
> > will be visible.
> >
>
> There is a certain similarly between functions and procedures which
> can not be denied, both take IN/OUT arguments and except SELECT/CALL
> syntax decoration they are invoked similarly. Just to note: users have
> been using function with void return value till now.
>

No, there are significant difference between SELECT and CALL - procedure is
not a void function.


> If we allow SELECT to be dropped while invoking a function through
> PERFORM, why not to drop CALL for procedures similarly?
>

>From my perspective a PERFORM is not bad idea, because it is consistent in
PLpgSQL.

Again - I don't see more issues related to PERFORM - usually much more
terrible is different system for OUT variables. This is a problem.

Regards

Pavel

>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>

Re: procedures and plpgsql PERFORM

[Ashutosh Bapat]

On Thu, Dec 14, 2017 at 10:16 PM, Pavel Stehule  wrote:
>
>
> 2017-12-14 17:10 GMT+01:00 David G. Johnston :
>>
>> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure 
>> wrote:
>>>
>>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
>>> > Ashutosh Bapat  writes:
>>> >> We allow a function to be invoked as part of PERFORM statement in
>>> >> plpgsql
>>> >> ...
>>> >> But we do not allow a procedure to be invoked this way
>>> >
>>> >> Procedures fit that category and like functions, I think, we should
>>> >> allow them be invoked directly without any quoting and CALL
>>> >> decoration.
>>> >
>>> > How is that going to work?  What if the procedure tries to commit the
>>> > current transaction?
>>> >
>>> > IOW, this is not merely a syntactic-sugar question.
>>>
>>> BTW, We've already come to (near-but good enough) consensus that
>>> PERFORM syntax is really just unnecessary, and I submitted a patch to
>>> make it optional (which I really need to dust off and complete).
>>
>>
>> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword to
>> specify a specific limited form of the SQL SELECT command.  CALL is an SQL
>> command.  I don't see any real upside to allowing pl/pgsql to accept
>> omission of the command tag while SQL cannot - at least not without a
>> use-case describe why such syntax would be beneficial.  And likely those use
>> cases would revolve around some looping variant as opposed to a single
>> stand-alone, result-less, CALL.
>>
>> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
>> enhancement:
>> PERFORM func() => SELECT func()
>> PERFORM proc() => CALL proc()
>
>
> I don't like this idea - functions are not procedures - can be nice if it
> will be visible.
>

There is a certain similarly between functions and procedures which
can not be denied, both take IN/OUT arguments and except SELECT/CALL
syntax decoration they are invoked similarly. Just to note: users have
been using function with void return value till now.

If we allow SELECT to be dropped while invoking a function through
PERFORM, why not to drop CALL for procedures similarly?

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: procedures and plpgsql PERFORM

[Ashutosh Bapat]

On Thu, Dec 14, 2017 at 9:40 PM, David G. Johnston
 wrote:
> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure  wrote:
>>
>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
>> > Ashutosh Bapat  writes:
>> >> We allow a function to be invoked as part of PERFORM statement in
>> >> plpgsql
>> >> ...
>> >> But we do not allow a procedure to be invoked this way
>> >
>> >> Procedures fit that category and like functions, I think, we should
>> >> allow them be invoked directly without any quoting and CALL
>> >> decoration.
>> >
>> > How is that going to work?  What if the procedure tries to commit the
>> > current transaction?
>> >
>> > IOW, this is not merely a syntactic-sugar question.
>>
>> BTW, We've already come to (near-but good enough) consensus that
>> PERFORM syntax is really just unnecessary, and I submitted a patch to
>> make it optional (which I really need to dust off and complete).
>
>
> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword to
> specify a specific limited form of the SQL SELECT command.  CALL is an SQL
> command.  I don't see any real upside to allowing pl/pgsql to accept
> omission of the command tag while SQL cannot - at least not without a
> use-case describe why such syntax would be beneficial.  And likely those use
> cases would revolve around some looping variant as opposed to a single
> stand-alone, result-less, CALL.
>
> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
> enhancement:
> PERFORM func() => SELECT func()
> PERFORM proc() => CALL proc()

Right, that's what I am suggesting.

Furthermore the current error message is misleading:

do $$
begin perform dummy_proc(1); end; $$ language plpgsql;
ERROR:  dummy_proc(integer) is a procedure
LINE 1: SELECT dummy_proc(1)
   ^
HINT:  To call a procedure, use CALL.
QUERY:  SELECT dummy_proc(1)
CONTEXT:  PL/pgSQL function inline_code_block line 2 at PERFORM

The user never wrote SELECT dummy_proc(), it was injected by plpgsql.
Let's assume for a moment, that user infers that s/he has to use CALL
instead. Even then plpgsql doesn't support PERFORM CALL dummy_proc()
or CALL dummy_proc().

>
> I prefer Merlin's suggestion to just documenting that PERFORM is deprecated
> and works only with functions - and that to use procedures in pl/pgsql just
> use the normal SQL CALL command.  And to write: "SELECT func()" to invoke
> functions, again just like one would in an SQL script.

That would simplify it, but I don't have any opinion as to whether we
should remove PERFORM or not.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: procedures and plpgsql PERFORM

[Ashutosh Bapat]

On Thu, Dec 14, 2017 at 8:08 PM, Tom Lane  wrote:
> Ashutosh Bapat  writes:
>> We allow a function to be invoked as part of PERFORM statement in plpgsql
>> ...
>> But we do not allow a procedure to be invoked this way
>
>> Procedures fit that category and like functions, I think, we should
>> allow them be invoked directly without any quoting and CALL
>> decoration.
>
> How is that going to work?  What if the procedure tries to commit the
> current transaction?

That can happen even today if somebody uses PERFORM 'call procedure()'
and procedure tries to commit the transaction. I don't think we have
any mechanism to prevent that. If we device one, it will be equally
applicable to PERFORM procedure().

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: procedures and plpgsql PERFORM

[Merlin Moncure]

On Thu, Dec 14, 2017 at 11:56 AM, Pavel Stehule  wrote:
>
>
> 2017-12-14 18:33 GMT+01:00 Merlin Moncure :
>>
>> On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule 
>> wrote:
>> >
>> >
>> > 2017-12-14 17:10 GMT+01:00 David G. Johnston
>> > :
>> >>
>> >> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure 
>> >> wrote:
>> >>>
>> >>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
>> >>> > Ashutosh Bapat  writes:
>> >>> >> We allow a function to be invoked as part of PERFORM statement in
>> >>> >> plpgsql
>> >>> >> ...
>> >>> >> But we do not allow a procedure to be invoked this way
>> >>> >
>> >>> >> Procedures fit that category and like functions, I think, we should
>> >>> >> allow them be invoked directly without any quoting and CALL
>> >>> >> decoration.
>> >>> >
>> >>> > How is that going to work?  What if the procedure tries to commit
>> >>> > the
>> >>> > current transaction?
>> >>> >
>> >>> > IOW, this is not merely a syntactic-sugar question.
>> >>>
>> >>> BTW, We've already come to (near-but good enough) consensus that
>> >>> PERFORM syntax is really just unnecessary, and I submitted a patch to
>> >>> make it optional (which I really need to dust off and complete).
>> >>
>> >>
>> >> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword
>> >> to
>> >> specify a specific limited form of the SQL SELECT command.  CALL is an
>> >> SQL
>> >> command.  I don't see any real upside to allowing pl/pgsql to accept
>> >> omission of the command tag while SQL cannot - at least not without a
>> >> use-case describe why such syntax would be beneficial.  And likely
>> >> those use
>> >> cases would revolve around some looping variant as opposed to a single
>> >> stand-alone, result-less, CALL.
>> >>
>> >> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the
>> >> following
>> >> enhancement:
>> >> PERFORM func() => SELECT func()
>> >> PERFORM proc() => CALL proc()
>> >
>> >
>> > I don't like this idea - functions are not procedures - can be nice if
>> > it
>> > will be visible.
>>
>> We need to get rid of PERFORM ASAP.  Agree that we need to not obfuscate
>> CALL.
>
> If we have a procedures, then functions without returned values lost a sense
> - and I don't see any changes with PERFORM necessary.

I don't think the presence of procedures really changes the thinking
here.  Having to simulate procedures with void returning functions
wasn't really the point; it's an annoying syntax departure from SQL
for little benefit other than assuming the users are wrong when they
are not explicitly capturing the result..

the topic was heavily discussed:
https://www.postgresql.org/message-id/CAHyXU0zYbeT-FzuonaaycbS9Wd8d5JO%2B_niAygzYtv5FMdx4rg%40mail.gmail.com


merlin

Re: procedures and plpgsql PERFORM

[Pavel Stehule]

2017-12-14 18:33 GMT+01:00 Merlin Moncure :

> On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule 
> wrote:
> >
> >
> > 2017-12-14 17:10 GMT+01:00 David G. Johnston  >:
> >>
> >> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure 
> >> wrote:
> >>>
> >>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
> >>> > Ashutosh Bapat  writes:
> >>> >> We allow a function to be invoked as part of PERFORM statement in
> >>> >> plpgsql
> >>> >> ...
> >>> >> But we do not allow a procedure to be invoked this way
> >>> >
> >>> >> Procedures fit that category and like functions, I think, we should
> >>> >> allow them be invoked directly without any quoting and CALL
> >>> >> decoration.
> >>> >
> >>> > How is that going to work?  What if the procedure tries to commit the
> >>> > current transaction?
> >>> >
> >>> > IOW, this is not merely a syntactic-sugar question.
> >>>
> >>> BTW, We've already come to (near-but good enough) consensus that
> >>> PERFORM syntax is really just unnecessary, and I submitted a patch to
> >>> make it optional (which I really need to dust off and complete).
> >>
> >>
> >> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword
> to
> >> specify a specific limited form of the SQL SELECT command.  CALL is an
> SQL
> >> command.  I don't see any real upside to allowing pl/pgsql to accept
> >> omission of the command tag while SQL cannot - at least not without a
> >> use-case describe why such syntax would be beneficial.  And likely
> those use
> >> cases would revolve around some looping variant as opposed to a single
> >> stand-alone, result-less, CALL.
> >>
> >> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
> >> enhancement:
> >> PERFORM func() => SELECT func()
> >> PERFORM proc() => CALL proc()
> >
> >
> > I don't like this idea - functions are not procedures - can be nice if it
> > will be visible.
>
> We need to get rid of PERFORM ASAP.  Agree that we need to not obfuscate
> CALL.
>

If we have a procedures, then functions without returned values lost a
sense - and I don't see any changes with PERFORM necessary.

Regards

Pavel



> merlin
>

Re: procedures and plpgsql PERFORM

[Merlin Moncure]

On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule  wrote:
>
>
> 2017-12-14 17:10 GMT+01:00 David G. Johnston :
>>
>> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure 
>> wrote:
>>>
>>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
>>> > Ashutosh Bapat  writes:
>>> >> We allow a function to be invoked as part of PERFORM statement in
>>> >> plpgsql
>>> >> ...
>>> >> But we do not allow a procedure to be invoked this way
>>> >
>>> >> Procedures fit that category and like functions, I think, we should
>>> >> allow them be invoked directly without any quoting and CALL
>>> >> decoration.
>>> >
>>> > How is that going to work?  What if the procedure tries to commit the
>>> > current transaction?
>>> >
>>> > IOW, this is not merely a syntactic-sugar question.
>>>
>>> BTW, We've already come to (near-but good enough) consensus that
>>> PERFORM syntax is really just unnecessary, and I submitted a patch to
>>> make it optional (which I really need to dust off and complete).
>>
>>
>> Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword to
>> specify a specific limited form of the SQL SELECT command.  CALL is an SQL
>> command.  I don't see any real upside to allowing pl/pgsql to accept
>> omission of the command tag while SQL cannot - at least not without a
>> use-case describe why such syntax would be beneficial.  And likely those use
>> cases would revolve around some looping variant as opposed to a single
>> stand-alone, result-less, CALL.
>>
>> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
>> enhancement:
>> PERFORM func() => SELECT func()
>> PERFORM proc() => CALL proc()
>
>
> I don't like this idea - functions are not procedures - can be nice if it
> will be visible.

We need to get rid of PERFORM ASAP.  Agree that we need to not obfuscate CALL.

merlin

Re: procedures and plpgsql PERFORM

[Pavel Stehule]

2017-12-14 17:10 GMT+01:00 David G. Johnston :

> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure 
> wrote:
>
>> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
>> > Ashutosh Bapat  writes:
>> >> We allow a function to be invoked as part of PERFORM statement in
>> plpgsql
>> >> ...
>> >> But we do not allow a procedure to be invoked this way
>> >
>> >> Procedures fit that category and like functions, I think, we should
>> >> allow them be invoked directly without any quoting and CALL
>> >> decoration.
>> >
>> > How is that going to work?  What if the procedure tries to commit the
>> > current transaction?
>> >
>> > IOW, this is not merely a syntactic-sugar question.
>>
>> BTW, We've already come to (near-but good enough) consensus that
>> PERFORM syntax is really just unnecessary, and I submitted a patch to
>> make it optional (which I really need to dust off and complete).
>
>
> ​Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword
> to specify a specific limited form of the SQL SELECT command.  CALL is an
> SQL command.  I don't see any real upside to allowing pl/pgsql to accept
> omission of the command tag while SQL cannot - at least not without a
> use-case describe why such syntax would be beneficial.  And likely those
> use cases would revolve around some looping variant as opposed to a single
> stand-alone, result-less, CALL.
>
> If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
> enhancement:
> PERFORM func() => SELECT func()
> PERFORM proc() => CALL proc()
>

I don't like this idea - functions are not procedures - can be nice if it
will be visible.

Pavel


> I prefer Merlin's suggestion to just documenting that PERFORM is
> deprecated and works only with functions - and that to use procedures in
> pl/pgsql just use the normal SQL CALL command.  And to write: "SELECT
> func()" to invoke functions, again just like one would in an SQL script.
>
> David J.
>

Re: procedures and plpgsql PERFORM

[David G. Johnston]

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure  wrote:

> On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane  wrote:
> > Ashutosh Bapat  writes:
> >> We allow a function to be invoked as part of PERFORM statement in
> plpgsql
> >> ...
> >> But we do not allow a procedure to be invoked this way
> >
> >> Procedures fit that category and like functions, I think, we should
> >> allow them be invoked directly without any quoting and CALL
> >> decoration.
> >
> > How is that going to work?  What if the procedure tries to commit the
> > current transaction?
> >
> > IOW, this is not merely a syntactic-sugar question.
>
> BTW, We've already come to (near-but good enough) consensus that
> PERFORM syntax is really just unnecessary, and I submitted a patch to
> make it optional (which I really need to dust off and complete).


​Except right now PERFORM doesn't exist in SQL and is a pl/pgsql keyword to
specify a specific limited form of the SQL SELECT command.  CALL is an SQL
command.  I don't see any real upside to allowing pl/pgsql to accept
omission of the command tag while SQL cannot - at least not without a
use-case describe why such syntax would be beneficial.  And likely those
use cases would revolve around some looping variant as opposed to a single
stand-alone, result-less, CALL.

If we do keep "PERFORM" in the pl/pgsql vocab I'd consider the following
enhancement:
PERFORM func() => SELECT func()
PERFORM proc() => CALL proc()

I prefer Merlin's suggestion to just documenting that PERFORM is deprecated
and works only with functions - and that to use procedures in pl/pgsql just
use the normal SQL CALL command.  And to write: "SELECT func()" to invoke
functions, again just like one would in an SQL script.

David J.

Re: procedures and plpgsql PERFORM

[Merlin Moncure]

On Thursday, December 14, 2017, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:

> Hi,
> We allow a function to be invoked as part of PERFORM statement in plpgsql
> do $$
> begin perform pg_relation_size('t1'); end; $$ language plpgsql;
> DO
>
> But we do not allow a procedure to be invoked this way
>  create procedure dummy_proc(a int) as $$
> begin null; end;
> $$ language plpgsql;
> CREATE PROCEDURE
>
> do $$
> begin perform dummy_proc(1); end; $$ language plpgsql;
> ERROR:  dummy_proc(integer) is a procedure
> LINE 1: SELECT dummy_proc(1)
>^
> HINT:  To call a procedure, use CALL.
> QUERY:  SELECT dummy_proc(1)
> CONTEXT:  PL/pgSQL function inline_code_block line 2 at PERFORM
>
> The documentation of PERFORM [1] says
> "For any SQL command that does not return rows, for example INSERT
> without a RETURNING clause, you can execute the command within a
> PL/pgSQL function just by writing the command."
>
> Procedures fit that category and like functions, I think, we should
> allow them be invoked directly without any quoting and CALL
> decoration.
>

I disagree.  The SQL command is 'CALL'.  The documentation is really only
clarifying when PERFORM is explicitly required.

merlin

Re: procedures and plpgsql PERFORM

[Pavel Stehule]

2017-12-14 8:21 GMT+01:00 Ashutosh Bapat :

> Hi,
> We allow a function to be invoked as part of PERFORM statement in plpgsql
> do $$
> begin perform pg_relation_size('t1'); end; $$ language plpgsql;
> DO
>
> But we do not allow a procedure to be invoked this way
>  create procedure dummy_proc(a int) as $$
> begin null; end;
> $$ language plpgsql;
> CREATE PROCEDURE
>
> do $$
> begin perform dummy_proc(1); end; $$ language plpgsql;
> ERROR:  dummy_proc(integer) is a procedure
> LINE 1: SELECT dummy_proc(1)
>^
> HINT:  To call a procedure, use CALL.
> QUERY:  SELECT dummy_proc(1)
> CONTEXT:  PL/pgSQL function inline_code_block line 2 at PERFORM
>
> The documentation of PERFORM [1] says
> "For any SQL command that does not return rows, for example INSERT
> without a RETURNING clause, you can execute the command within a
> PL/pgSQL function just by writing the command."
>
> Procedures fit that category and like functions, I think, we should
> allow them be invoked directly without any quoting and CALL
> decoration.
>

Why? The CALL is four chars more. It is keyword, and it reduce parser
complexity - we should not to different between routine name and variable
name.

So -1 from my for this proposal.

Regards

Pavel

>
> [1] https://www.postgresql.org/docs/devel/static/plpgsql-
> statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
>

procedures and plpgsql PERFORM

[Ashutosh Bapat]

Hi,
We allow a function to be invoked as part of PERFORM statement in plpgsql
do $$
begin perform pg_relation_size('t1'); end; $$ language plpgsql;
DO

But we do not allow a procedure to be invoked this way
 create procedure dummy_proc(a int) as $$
begin null; end;
$$ language plpgsql;
CREATE PROCEDURE

do $$
begin perform dummy_proc(1); end; $$ language plpgsql;
ERROR:  dummy_proc(integer) is a procedure
LINE 1: SELECT dummy_proc(1)
   ^
HINT:  To call a procedure, use CALL.
QUERY:  SELECT dummy_proc(1)
CONTEXT:  PL/pgSQL function inline_code_block line 2 at PERFORM

The documentation of PERFORM [1] says
"For any SQL command that does not return rows, for example INSERT
without a RETURNING clause, you can execute the command within a
PL/pgSQL function just by writing the command."

Procedures fit that category and like functions, I think, we should
allow them be invoked directly without any quoting and CALL
decoration.

[1] 
https://www.postgresql.org/docs/devel/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company