Обсуждение: Procedures
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.
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 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?
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 improvementDave Cramerwww.postgres.rocks
> Agreed, this doc area needs help.
I developed the attached patach for this. Is this sufficient?
I developed the attached patach for this. Is this sufficient?
" ... 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."
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." --
J. Purtz
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 AbbiCan 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."
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 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?
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
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
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
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.
execution"
--
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 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
Вложения
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
On 2020-08-24 18:00, Bruce Momjian wrote: > - command, a procedure is called explicitly using > - the <xref linkend="sql-call"/> statement. > + command, a procedure is called in isolation using > + the <xref linkend="sql-call"/> 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
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 <xref linkend="sql-call"/> statement. > > + command, a procedure is called in isolation using > > + the <xref linkend="sql-call"/> 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 <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Вложения
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 <xref linkend="sql-call"/> statement. > > > + command, a procedure is called in isolation using > > > + the <xref linkend="sql-call"/> 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 <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee