Re: Functions should be Functions & Procedures

[Bruce Momjian]

On Wed, Nov 25, 2020 at 06:38:56PM +, Daniel Westermann (DWE) wrote:
> >On Wed, Nov 25, 2020 at 06:23:44PM +, Daniel Westermann (DWE) wrote:
> >> >Good point.  It seems PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI
> >> >server-side languages all support procedures.  I have developed the
> >> >attached patch to mention procedures right at the top.  I didn't see a
> >> >need to mention function "or procedure" throughout the entire document.
> >> >Does this seem sufficient?
> >> 
> >> Thanks for looking at it. Seems you attachment is corrupt, at least I can 
> >> not open it. Can you please resend?
> 
> >It is a gzipp'ed file.  I had to use gzip and can't resend it since it
> >contains the T-C-L URL that is flagged by Spamhaus as spam and causes my
> >emails to be marked as spam. You can see the patch here:
> 
> Looks good, thanks.

Thanks, patch applied through 11.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Functions should be Functions & Procedures

[Daniel Westermann (DWE)]

>On Wed, Nov 25, 2020 at 06:23:44PM +, Daniel Westermann (DWE) wrote:
>> Hi Bruce,
>> 
>> >On Tue, Nov 24, 2020 at 07:15:29PM +, PG Doc comments form wrote:
>> >> The following documentation comment has been logged on the website:
>> >> 
>> >> Page: https://www.postgresql.org/docs/13/plpgsql-overview.html
>> >> Description:
>> >> 
>> >> Hi,
>> >> 
>> >> since PostgreSQL 11 we have procedures, so referencing to only functions
>> >> here seems to ignore that. Shouldn't procedures be mentioned here as well?
>> 
>> >Good point.  It seems PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI
>> >server-side languages all support procedures.  I have developed the
>> >attached patch to mention procedures right at the top.  I didn't see a
>> >need to mention function "or procedure" throughout the entire document.
>> >Does this seem sufficient?
>> 
>> Thanks for looking at it. Seems you attachment is corrupt, at least I can 
>> not open it. Can you please resend?

>It is a gzipp'ed file.  I had to use gzip and can't resend it since it
>contains the T-C-L URL that is flagged by Spamhaus as spam and causes my
>emails to be marked as spam. You can see the patch here:

Looks good, thanks.

Regards
Daniel

Re: Functions should be Functions & Procedures

[Bruce Momjian]

On Wed, Nov 25, 2020 at 06:23:44PM +, Daniel Westermann (DWE) wrote:
> Hi Bruce,
> 
> >On Tue, Nov 24, 2020 at 07:15:29PM +, PG Doc comments form wrote:
> >> The following documentation comment has been logged on the website:
> >> 
> >> Page: https://www.postgresql.org/docs/13/plpgsql-overview.html
> >> Description:
> >> 
> >> Hi,
> >> 
> >> since PostgreSQL 11 we have procedures, so referencing to only functions
> >> here seems to ignore that. Shouldn't procedures be mentioned here as well?
> 
> >Good point.  It seems PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI
> >server-side languages all support procedures.  I have developed the
> >attached patch to mention procedures right at the top.  I didn't see a
> >need to mention function "or procedure" throughout the entire document.
> >Does this seem sufficient?
> 
> Thanks for looking at it. Seems you attachment is corrupt, at least I can not 
> open it. Can you please resend?

It is a gzipp'ed file.  I had to use gzip and can't resend it since it
contains the T-C-L URL that is flagged by Spamhaus as spam and causes my
emails to be marked as spam. You can see the patch here:

https://momjian.us/tmp/proc.txt

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Functions should be Functions & Procedures

[Daniel Westermann (DWE)]

Hi Bruce,

>On Tue, Nov 24, 2020 at 07:15:29PM +, PG Doc comments form wrote:
>> The following documentation comment has been logged on the website:
>> 
>> Page: https://www.postgresql.org/docs/13/plpgsql-overview.html
>> Description:
>> 
>> Hi,
>> 
>> since PostgreSQL 11 we have procedures, so referencing to only functions
>> here seems to ignore that. Shouldn't procedures be mentioned here as well?

>Good point.  It seems PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI
>server-side languages all support procedures.  I have developed the
>attached patch to mention procedures right at the top.  I didn't see a
>need to mention function "or procedure" throughout the entire document.
>Does this seem sufficient?

Thanks for looking at it. Seems you attachment is corrupt, at least I can not 
open it. Can you please resend?

Regards
Daniel

Re: Functions should be Functions & Procedures

[Bruce Momjian]

On Tue, Nov 24, 2020 at 07:15:29PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/13/plpgsql-overview.html
> Description:
> 
> Hi,
> 
> since PostgreSQL 11 we have procedures, so referencing to only functions
> here seems to ignore that. Shouldn't procedures be mentioned here as well?

Good point.  It seems PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI
server-side languages all support procedures.  I have developed the
attached patch to mention procedures right at the top.  I didn't see a
need to mention function "or procedure" throughout the entire document.
Does this seem sufficient?

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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



proc.diff.gz
Description: application/gzip

Functions should be Functions & Procedures

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/plpgsql-overview.html
Description:

Hi,

since PostgreSQL 11 we have procedures, so referencing to only functions
here seems to ignore that. Shouldn't procedures be mentioned here as well?

Regards
Daniel

Re: Procedures

[Bruce Momjian]

On Tue, Aug 25, 2020 at 03:03:13PM -0400, Bruce Momjian wrote:
> On Tue, Aug 25, 2020 at 08:38:11PM +0200, Peter Eisentraut wrote:
> > On 2020-08-24 18:00, Bruce Momjian wrote:
> > > -command, a procedure is called explicitly using
> > > -the  statement.
> > > +command, a procedure is called in isolation using
> > > +the  command.  If the CALL command is not
> > > +part of an explicit transaction, a procedure can commit, rollback,
> > > +and begin new transactions during its execution, which is not 
> > > possible
> > > +in functions.
> > 
> > There are additional conditions for when a procedure can do transaction
> > control, and it also depends on the language.  It's not clear how much
> > detail we should give in a general section like this.  Often people read
> > this and then wonder why it doesn't work.
> 
> I have updated the patch to mention it is dependend on the server-side
> language.

Patch applied back through PG 11.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Procedures

[Bruce Momjian]

On Tue, Aug 25, 2020 at 08:38:11PM +0200, Peter Eisentraut wrote:
> On 2020-08-24 18:00, Bruce Momjian wrote:
> > -command, a procedure is called explicitly using
> > -the  statement.
> > +command, a procedure is called in isolation using
> > +the  command.  If the CALL command is not
> > +part of an explicit transaction, a procedure can commit, rollback,
> > +and begin new transactions during its execution, which is not possible
> > +in functions.
> 
> There are additional conditions for when a procedure can do transaction
> control, and it also depends on the language.  It's not clear how much
> detail we should give in a general section like this.  Often people read
> this and then wonder why it doesn't work.

I have updated the patch to mention it is dependend on the server-side
language.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 6de464c654..732d935521 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -84,8 +84,11 @@
 A procedure is a database object similar to a function.  The difference is
 that a procedure does not return a value, so there is no return type
 declaration.  While a function is called as part of a query or DML
-command, a procedure is called explicitly using
-the  statement.
+command, a procedure is called in isolation using
+the  command.  If the CALL command is not
+part of an explicit transaction, a procedure in many server-side
+languages can commit, rollback, and begin new transactions during
+its execution, which is not possible in functions.

 

Re: Procedures

[Peter Eisentraut]

On 2020-08-24 18:00, Bruce Momjian wrote:

-command, a procedure is called explicitly using
-the  statement.
+command, a procedure is called in isolation using
+the  command.  If the CALL command is not
+part of an explicit transaction, a procedure can commit, rollback,
+and begin new transactions during its execution, which is not possible
+in functions.


There are additional conditions for when a procedure can do transaction 
control, and it also depends on the language.  It's not clear how much 
detail we should give in a general section like this.  Often people read 
this and then wonder why it doesn't work.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Procedures

[David G. Johnston]

On Mon, Aug 24, 2020 at 9:09 AM Pavel Stehule 
wrote:

>
>
> po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian  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
>

Works for me.  I wasn't thinking about the implication of the wording with
respect to Savepoints in functions.

David J.

Re: Procedures

[Pavel Stehule]

po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian  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


> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>

Re: Procedures

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 6de464c654..5dda2a80af 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -84,8 +84,11 @@
 A procedure is a database object similar to a function.  The difference is
 that a procedure does not return a value, so there is no return type
 declaration.  While a function is called as part of a query or DML
-command, a procedure is called explicitly using
-the  statement.
+command, a procedure is called in isolation using
+the  command.  If the CALL command is not
+part of an explicit transaction, a procedure can commit, rollback,
+and begin new transactions during its execution, which is not possible
+in functions.

 

Re: Procedures

[Pavel Stehule]

po 24. 8. 2020 v 17:38 odesílatel Bruce Momjian  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 
> 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 
> 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"


> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>

Re: Procedures

[Bruce Momjian]

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  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  
> > 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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Procedures

[Bruce Momjian]

On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote:
> 
> 
> po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian  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  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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Procedures

[Pavel Stehule]

po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian  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  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.



>
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>
>
>

Re: Procedures

[David G. Johnston]

On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian  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.

Re: Procedures

[Bruce Momjian]

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  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  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  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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 6de464c654..1fdd98d41d 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -84,8 +84,10 @@
 A procedure is a database object similar to a function.  The difference is
 that a procedure does not return a value, so there is no return type
 declaration.  While a function is called as part of a query or DML
-command, a procedure is called explicitly using
-the  statement.
+command, a procedure is called in isolation using
+the  command.  If the CALL command is not
+part of an explicit transaction, a procedure can begin and commit
+multiple transactions during its execution.

 

Re: Procedures

[Bruce Momjian]

On Sat, Aug 22, 2020 at 12:05:24PM +0100, Robin Abbi wrote:
> On Fri, 21 Aug 2020 at 23:52, Bruce Momjian  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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Procedures

[David G. Johnston]

On Saturday, August 22, 2020, Jürgen Purtz  wrote:

> On 22.08.20 13:05, Robin Abbi wrote:
>
> On Fri, 21 Aug 2020 at 23:52, Bruce Momjian  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.

Re: Procedures

[Jürgen Purtz]

On 22.08.20 13:05, Robin Abbi wrote:
On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <mailto:br...@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

Re: Procedures

[Robin Abbi]

On Fri, 21 Aug 2020 at 23:52, Bruce Momjian  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

Re: Procedures

[David G. Johnston]

On Fri, Aug 21, 2020 at 3:52 PM Bruce Momjian  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  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  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.

Re: Procedures

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 6de464c654..0b52feffe5 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -83,7 +83,8 @@

 A procedure is a database object similar to a function.  The difference is
 that a procedure does not return a value, so there is no return type
-declaration.  While a function is called as part of a query or DML
+declaration.  Procedures can also begin and commit transactions.
+While a function is called as part of a query or DML
 command, a procedure is called explicitly using
 the  statement.

Re: Procedures

[Bruce Momjian]

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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Re: Procedures

[Robin Abbi]

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  wrote:

>
>
> On Wed, 5 Aug 2020 at 20:18, Bruce Momjian  wrote:
>
>> On Tue, Aug  4, 2020 at 10:33:49AM +, 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
>

Re: Procedures

[Dave Cramer]

On Wed, 5 Aug 2020 at 20:18, Bruce Momjian  wrote:

> On Tue, Aug  4, 2020 at 10:33:49AM +, 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

Re: Procedures

[Bruce Momjian]

On Tue, Aug  4, 2020 at 10:33:49AM +, 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  https://momjian.us
  EnterpriseDB https://enterprisedb.com

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

Procedures

[PG Doc comments form]

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.

Re: Wrong 'Special local variables PG_' prefix in 'Trigger procedures' section

[Tom Lane]

=?utf-8?q?PG_Doc_comments_form?=  writes:
> Page: https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

> The documentation (10 and 9.6) contains a typo when mentioning 'Special
> local variables PG_something'. Instead it should be 'Special local variables
> TG_something' as it is for trigger local variables.

Ugh, yeah, that's a thinko isn't it.  Will fix, thanks for noticing!

regards, tom lane

Wrong 'Special local variables PG_' prefix in 'Trigger procedures' section

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/plpgsql-trigger.html
Description:

The documentation (10 and 9.6) contains a typo when mentioning 'Special
local variables PG_something'. Instead it should be 'Special local variables
TG_something' as it is for trigger local variables.

Examples just below all start with TG_ so it should be obvious.