procedures and plpgsql PERFORM

2017-12-14 8:21 GMT+01:00 Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>:

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

On Thursday, December 14, 2017, Ashutosh Bapat <
ashutosh.bapat@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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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.

regards, tom lane

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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.

I think OP is simply misunderstanding the docs. In pl/pgsql, a leading
keyword (SELECT/PERFORM/CALL/etc) is *required*. For example, you
can't do this:

<snip>
BEGIN
a := 1;
f(); -- illegal
PERFORM f(); -- ok
b := f(); -- ok
...
<snip>

What the documentation is trying to say is that you can do
INSERT/UPDATE/etc without any extra decoration and no special handling
as with PERFORM. Another way of stating that is SQL commands are
'first class' in pl/pgsql, in that they can be inserted without any
pl/pgsql handling.

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). If
so done, that entire section of language in the docs would be moot
since SELECT wouldn't really be any different in pl/pgsql than say,
INSERT from a syntax perspective. All SQL commands, except for those
that we've reserve to be not usable in functions/procedures would
operate similarly 'in-procedure' vs 'not-in-'procedure', which is a
good thing IMO. This thread is yet another example of why the
SELECT/PERFORM dichotomy just confuses people.

merlin

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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.

2017-12-14 17:10 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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

On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-12-14 17:10 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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

2017-12-14 18:33 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2017-12-14 17:10 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com
:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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

On Thu, Dec 14, 2017 at 11:56 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-12-14 18:33 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2017-12-14 17:10 GMT+01:00 David G. Johnston
<david.g.johnston@gmail.com>:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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:
/messages/by-id/CAHyXU0zYbeT-FzuonaaycbS9Wd8d5JO+_niAygzYtv5FMdx4rg@mail.gmail.com

merlin

On Thu, Dec 14, 2017 at 8:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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

On Thu, Dec 14, 2017 at 9:40 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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

On Thu, Dec 14, 2017 at 10:16 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-12-14 17:10 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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

2017-12-15 4:43 GMT+01:00 Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>:

On Thu, Dec 14, 2017 at 10:16 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2017-12-14 17:10 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com
:

On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Thu, Dec 14, 2017 at 8:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> 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