Procedures

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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.
Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Bruce Momjian
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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Dave Cramer-7


On Wed, 5 Aug 2020 at 20:18, Bruce Momjian <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> wrote:


On Wed, 5 Aug 2020 at 20:18, Bruce Momjian <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

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

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



Reply | Threaded
Open this post in threaded view
|

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

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


proc.diff (668 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Procedures

David G Johnston
On Fri, Aug 21, 2020 at 3:52 PM Bruce Momjian <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Robin Abbi
In reply to this post by Bruce Momjian
On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <[hidden email]> 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."

Robin Abbi

Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Jürgen Purtz
On 22.08.20 13:05, Robin Abbi wrote:
On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <[hidden email]> 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."

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

Reply | Threaded
Open this post in threaded view
|

Re: Procedures

David G Johnston
On Saturday, August 22, 2020, Jürgen Purtz <[hidden email]> wrote:
On 22.08.20 13:05, Robin Abbi wrote:
On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <[hidden email]> 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."

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.

Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Bruce Momjian
In reply to this post by Robin Abbi
On Sat, Aug 22, 2020 at 12:05:24PM +0100, Robin Abbi wrote:

> On Fri, 21 Aug 2020 at 23:52, Bruce Momjian <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Bruce Momjian
In reply to this post by David G Johnston
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 <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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


proc.diff (794 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Procedures

David G Johnston
On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Bruce Momjian
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 <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Pavel Stehule


po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian <[hidden email]> 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 <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

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

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



Reply | Threaded
Open this post in threaded view
|

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

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



Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Pavel Stehule


po 24. 8. 2020 v 17:38 odesílatel Bruce Momjian <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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

Reply | Threaded
Open this post in threaded view
|

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

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


proc.diff (842 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Procedures

Pavel Stehule


po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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

12