Procedures

On Tue, Aug 4, 2020 at 10:33:49AM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/sql-createprocedure.html
Description:

The information on procedures could helpfully include that the feature is
new from PostgreSQL 11 and give an explanation of how it differs from
functions. I found the information I needed here
https://dba.stackexchange.com/a/262662, but I think it would really benefit
others if the information was provided in the official documentation.

We don't normally mention what release added a features. However, I do
see your problem with finding that procedures can issue transaction
control statements. I see this for procedures:

https://www.postgresql.org/docs/12/xproc.html

but that has no mention of transactions, just a mention of using CALL,
then this pl/pgSQL section about transaction control mentions CALL:

https://www.postgresql.org/docs/12/plpgsql-transactions.html

Is this what you think needs improving?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Wed, 5 Aug 2020 at 20:18, Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Aug 4, 2020 at 10:33:49AM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/sql-createprocedure.html
Description:

The information on procedures could helpfully include that the feature is
new from PostgreSQL 11 and give an explanation of how it differs from
functions. I found the information I needed here
https://dba.stackexchange.com/a/262662, but I think it would really

benefit

others if the information was provided in the official documentation.

We don't normally mention what release added a features. However, I do
see your problem with finding that procedures can issue transaction
control statements. I see this for procedures:

https://www.postgresql.org/docs/12/xproc.html

but that has no mention of transactions, just a mention of using CALL,
then this pl/pgSQL section about transaction control mentions CALL:

https://www.postgresql.org/docs/12/plpgsql-transactions.html

Is this what you think needs improving?

Neither of those places mention that procedures cannot be called inside a

transaction.
So ya I think there there is some room for improvement

Dave Cramer
www.postgres.rocks

PostgreSQL went as far as release 10 without procedures.
Some third party resources written before 11 loosely conflate procedures
with functions.
Some third party resources written before 11 accurately state PostgreSQL
has functions but not procedures.
Referring to the PostgreSQL docs for 11 on, procedures have been added.

For someone like me, coming to the subject without much of a hinterland
other than googling around, it seemed clear that there was a motivating
case that caused Procedures to be added to PostgreSQL, but I was not
sufficiently familiar with the domain to be able to readily intuit what it
might have been.

For example, Procedures say they have no return value, yet Functions can
return void. Not the same I agree, but I wouldn't be aware in which
circumstances it mattered.

For me, perhaps the most useful thing would have been a small example
highlighting the essential thing(s) that procedures can do that functions
could not.

Robin

On Thu, 6 Aug 2020 at 11:32, Dave Cramer <davecramer@postgres.rocks> wrote:

On Thu, Aug 6, 2020 at 02:30:55PM +0100, Robin Abbi wrote:

PostgreSQL went as far as release 10 without procedures.
Some third party resources written before 11 loosely conflate procedures with
functions.
Some third party resources written before 11 accurately state PostgreSQL has
functions but not procedures.
Referring to the PostgreSQL docs for 11 on, procedures have been added.

For someone like me, coming to the subject without much of a hinterland other
than googling around, it seemed clear that there was a motivating case that
caused Procedures to be added to PostgreSQL, but I was not sufficiently
familiar with the domain to be able to readily intuit what it might have been.

For example, Procedures say they have no return value, yet Functions can return
void. Not the same I agree, but I wouldn't be aware in which circumstances it
mattered.

For me, perhaps the most useful thing would have been a small example
highlighting the essential thing(s) that procedures can do that functions could
not.

Agreed, this doc area needs help.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Thu, Aug 6, 2020 at 06:10:52PM -0400, Bruce Momjian wrote:

On Thu, Aug 6, 2020 at 02:30:55PM +0100, Robin Abbi wrote:

PostgreSQL went as far as release 10 without procedures.
Some third party resources written before 11 loosely conflate procedures with
functions.
Some third party resources written before 11 accurately state PostgreSQL has
functions but not procedures.
Referring to the PostgreSQL docs for 11 on, procedures have been added.

For someone like me, coming to the subject without much of a hinterland other
than googling around, it seemed clear that there was a motivating case that
caused Procedures to be added to PostgreSQL, but I was not sufficiently
familiar with the domain to be able to readily intuit what it might have been.

For example, Procedures say they have no return value, yet Functions can return
void. Not the same I agree, but I wouldn't be aware in which circumstances it
mattered.

For me, perhaps the most useful thing would have been a small example
highlighting the essential thing(s) that procedures can do that functions could
not.

Agreed, this doc area needs help.

I developed the attached patach for this. Is this sufficient?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Fri, Aug 21, 2020 at 3:52 PM Bruce Momjian <bruce@momjian.us> wrote:

Agreed, this doc area needs help.

I developed the attached patach for this. Is this sufficient?

For consistency I would change "statement" to "command" at the end of that
paragraph .

the <xref linkend="sql-call"/> command.

and to contrast with "a part of" I would modify the following fragment to
read:

a procedure is called in isolation

Taken together:

While a function is called as part of a query or DML command, a procedure
is called in isolation using the <xref linked="sql-call"/> command.

And then swap the order of, and tweak, the transaction and isolation
sentences:

[...] the CALL command. If the CALL command is not part of an explicit
transaction a procedure can also manage multiple transactions during its
execution.

David J.

On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <bruce@momjian.us> wrote:

I developed the attached patach for this. Is this sufficient?

Would it be appropriate to consider including some language with a similar
information content to this

" ... prior to PostgreSQL 11, these functions were unable to manage their
own transactions. PostgreSQL 11 adds SQL procedures that can perform full
transaction management within the body of a function, enabling developers
to create more advanced server-side applications, such as ones involving
incremental bulk data loading."

from here https://www.postgresql.org/about/news/1894/ .

Robin Abbi

On 22.08.20 13:05, Robin Abbi wrote:

On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>> wrote:

I developed the attached patach for this.  Is this sufficient?

Would it be appropriate to consider including some language with a
similar information content to this

" ... prior to PostgreSQL 11, these functions were unable to
manage their own transactions. PostgreSQL 11 adds SQL procedures
that can perform full transaction management within the body of a
function, enabling developers to create more advanced server-side
applications, such as ones involving incremental bulk data loading."

from here https://www.postgresql.org/about/news/1894/ .

Robin Abbi

Can we more clearly distinguish between "function" and "procedure"? eg:

"Developers have been able to create user-defined functions in
PostgreSQL since decades, but functions are unable to manage their own
transactions. PostgreSQL 11 adds SQL procedures that can perform full
transaction management within their body, enabling developers to create
more advanced server-side applications, such as ones involving
incremental bulk data loading."

as a modification of the original release notes:

"Developers have been able to create user-defined functions in
PostgreSQL for over 20 years, but prior to PostgreSQL 11, these
functions were unable to manage their own transactions. PostgreSQL 11
adds SQL procedures that can perform full transaction management within
the body of a function, enabling developers to create more advanced
server-side applications, such as ones involving incremental bulk data
loading."

--

J. Purtz

On Saturday, August 22, 2020, Jürgen Purtz <juergen@purtz.de> wrote:

On 22.08.20 13:05, Robin Abbi wrote:

On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <bruce@momjian.us> wrote:

I developed the attached patach for this. Is this sufficient?

Would it be appropriate to consider including some language with a similar
information content to this

" ... prior to PostgreSQL 11, these functions were unable to manage their
own transactions. PostgreSQL 11 adds SQL procedures that can perform full
transaction management within the body of a function, enabling developers
to create more advanced server-side applications, such as ones involving
incremental bulk data loading."

from here https://www.postgresql.org/about/news/1894/ .

Robin Abbi

Can we more clearly distinguish between "function" and "procedure"? eg:
"Developers have been able to create user-defined functions in PostgreSQL
since decades, but functions are unable to manage their own transactions.
PostgreSQL 11 adds SQL procedures that can perform full transaction
management within their body, enabling developers to create more advanced
server-side applications, such as ones involving incremental bulk data
loading."

as a modification of the original release notes:

"Developers have been able to create user-defined functions in PostgreSQL
for over 20 years, but prior to PostgreSQL 11, these functions were unable
to manage their own transactions. PostgreSQL 11 adds SQL procedures that
can perform full transaction management within the body of a function,
enabling developers to create more advanced server-side applications, such
as ones involving incremental bulk data loading."

Neither the 20 years or mention of specific versions are included in the
main body of the documentation. If a feature exists its documented in that
version in such a manner as “this is how things are”.

Replacing “their body” with “the body of a function” isn’t an improvement.

David J.

On Sat, Aug 22, 2020 at 12:05:24PM +0100, Robin Abbi wrote:

On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <bruce@momjian.us> wrote:

I developed the attached patach for this.� Is this sufficient?

Would it be appropriate to consider including some language with a similar
information content to this

" ... prior to PostgreSQL 11, these functions were unable to manage their
own transactions. PostgreSQL 11 adds SQL procedures that can perform full
transaction management within the body of a function, enabling developers
to create more advanced server-side applications, such as ones involving
incremental bulk data loading."

from here https://www.postgresql.org/about/news/1894/ .

No, we would only mention it if there some kind of incompatibility here.
We always have to balance adding text with making the text longer and
harder to read.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:

On Fri, Aug 21, 2020 at 3:52 PM Bruce Momjian <bruce@momjian.us> wrote:

Agreed, this doc area needs help.

I developed the attached patach for this.� Is this sufficient?

For consistency I would change "statement" to "command" at the end of that
paragraph .

�� � �the <xref linkend="sql-call"/> command.

and to contrast with "a part of" I would modify the following fragment to read:

� � a procedure is called in isolation

Taken together:

While a function is called as part of a query or DML command,�a procedure is
called in isolation�using the <xref linked="sql-call"/> command.

And then swap the order of, and tweak, the transaction and isolation sentences:

[...] the CALL command.� If the CALL command is not part of an explicit
transaction a procedure can also manage multiple transactions during its
execution.

OK, how is this updated patch?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:

[...] the CALL command. If the CALL command is not part of an explicit
transaction a procedure can also manage multiple transactions during its
execution.

OK, how is this updated patch?

Looks good. I felt "begin and commit" was a bit wordy but it works.

David J.

On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:

[...] the CALL command.� If the CALL command is not part of an explicit
transaction a procedure can also manage multiple transactions during its
execution.

OK, how is this updated patch?

Looks good.� I felt "begin and commit" was a bit wordy but it works.

So, I was worried that "manage multiple transactions" could imply
something like savepoints, which can be managed by functions. It is
really the top-level begin/commit that is unique for procedures.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian <bruce@momjian.us> napsal:

On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:

[...] the CALL command. If the CALL command is not part of an

explicit

transaction a procedure can also manage multiple transactions

during its

execution.

OK, how is this updated patch?

Looks good. I felt "begin and commit" was a bit wordy but it works.

So, I was worried that "manage multiple transactions" could imply
something like savepoints, which can be managed by functions. It is
really the top-level begin/commit that is unique for procedures.

Functions is executed under outer transaction every time - rollback to save
point hasn't impact on outer transaction. Inside procedures (in special
case) can be transactions ended (by statements COMMIT or ROLLBACK).
Immediately is started new transaction.

On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote:

po 24. 8. 2020 v�17:01 odes�latel Bruce Momjian <bruce@momjian.us> napsal:

On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <bruce@momjian.us> wrote:

� � �On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:

� � �> [...] the CALL command.� If the CALL command is not part of an

explicit

� � �> transaction a procedure can also manage multiple transactions

during its

� � �> execution.

� � �OK, how is this updated patch?

Looks good.� I felt "begin and commit" was a bit wordy but it works.

So, I was worried that "manage multiple transactions" could imply
something like savepoints, which can be managed by functions.� It is
really the top-level begin/commit that is unique for procedures.

Functions is executed under outer transaction every time - rollback to save
point hasn't impact on outer transaction. Inside procedures (in special case)
can be transactions ended (by statements COMMIT or ROLLBACK). Immediately is
started new transaction.

Well, savepoints control what commands are considered _part_ of the
outer transaction, so in a way you are managing what is in the outer
transaction. This is why begin/commit was clearer for me. Maybe "start
and commit" is clearer?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

On Mon, Aug 24, 2020 at 11:35:57AM -0400, Bruce Momjian wrote:

On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote:

po 24. 8. 2020 v�17:01 odes�latel Bruce Momjian <bruce@momjian.us> napsal:

On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <bruce@momjian.us> wrote:

� � �On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:

� � �> [...] the CALL command.� If the CALL command is not part of an

explicit

� � �> transaction a procedure can also manage multiple transactions

during its

� � �> execution.

� � �OK, how is this updated patch?

Looks good.� I felt "begin and commit" was a bit wordy but it works.

So, I was worried that "manage multiple transactions" could imply
something like savepoints, which can be managed by functions.� It is
really the top-level begin/commit that is unique for procedures.

Functions is executed under outer transaction every time - rollback to save
point hasn't impact on outer transaction. Inside procedures (in special case)
can be transactions ended (by statements COMMIT or ROLLBACK). Immediately is
started new transaction.

Well, savepoints control what commands are considered _part_ of the
outer transaction, so in a way you are managing what is in the outer
transaction. This is why begin/commit was clearer for me. Maybe "start
and commit" is clearer?

Should the new text be?

a procedure can commit and begin new transactions during its
execution.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

po 24. 8. 2020 v 17:38 odesílatel Bruce Momjian <bruce@momjian.us> napsal:

On Mon, Aug 24, 2020 at 11:35:57AM -0400, Bruce Momjian wrote:

On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote:

po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian <bruce@momjian.us>

napsal:

On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <bruce@momjian.us>

wrote:

On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston

wrote:

[...] the CALL command. If the CALL command is not part

of an

explicit

transaction a procedure can also manage multiple

transactions

during its

execution.

OK, how is this updated patch?

Looks good. I felt "begin and commit" was a bit wordy but it

works.

So, I was worried that "manage multiple transactions" could imply
something like savepoints, which can be managed by functions. It

is

really the top-level begin/commit that is unique for procedures.

Functions is executed under outer transaction every time - rollback to

save

point hasn't impact on outer transaction. Inside procedures (in

special case)

can be transactions ended (by statements COMMIT or ROLLBACK).

Immediately is

started new transaction.

Well, savepoints control what commands are considered _part_ of the
outer transaction, so in a way you are managing what is in the outer
transaction. This is why begin/commit was clearer for me. Maybe "start
and commit" is clearer?

Should the new text be?

a procedure can commit and begin new transactions during its
execution.

sure. Maybe enhancing about sentence like "it is not possible in a
function."

and

"a procedure can commit (or rollback) and begin new transactions during its
execution"

On Mon, Aug 24, 2020 at 05:51:29PM +0200, Pavel Stehule wrote:

sure. Maybe enhancing about sentence like "it is not possible in a function."

and

"a procedure can commit (or rollback) and begin new transactions during its
� � � � execution"

OK, updated patch.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian <bruce@momjian.us> napsal:

On Mon, Aug 24, 2020 at 05:51:29PM +0200, Pavel Stehule wrote:

sure. Maybe enhancing about sentence like "it is not possible in a

function."

and

"a procedure can commit (or rollback) and begin new transactions during

its

execution"

OK, updated patch.

it is clean for me