Transaction Management

On Tue, Jul 7, 2020 at 10:46:59AM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

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

Hello,

We are looking to use transaction management and have run into a limitation
when the procedure is security definer. There doesn't appear to be any
reference to this in the
https://www.postgresql.org/docs/13/plpgsql-transactions.html so we cannot
see when the limitation is lifted.

Are you able to clarify the situation with this between PG11, 12 and 13? We
are hoping the limitation is lifted as we cannot see what the reason for the
limitation is.

You are going to need to be more specific if you want help, and this
doesn't seem like a documentation problem either.

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

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

Hi Bruce,

Thanks for taking a look at this.

This is really about making a limitation obvious. In other parts of the
documentation, like table partitioning, limitations are really spelled out,
good solid health warnings for those uninitiated. For transactions we have
found that procedures cannot handle commit and rollbacks when marked as
security definer, we found this out through trial and error (we don't have
a clue as to why the limitation is in there and are trying to figure it
out). The only reference we found in the documentation was on
https://www.postgresql.org/docs/11/sql-createprocedure.html with the line "A
SECURITY DEFINER procedure cannot execute transaction control statements
(for example, COMMIT and ROLLBACK, depending on the language)." We only
spotted this when digging as to find any information as to why we received
this error.

My reasoning of raising the point is it would have been really helpful for
us if the limitation was listed on the versions for
https://www.postgresql.org/docs/13/plpgsql-transactions.html so we didn't
sleepwalk into a limitation and if documented there we can see when the
limitation is raised (we can't figure out what the problem is with security
definer as it works so well for our use case).

Thanks,
James

On Tue, 14 Jul 2020 at 21:23, Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Jul 7, 2020 at 10:46:59AM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

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

Hello,

We are looking to use transaction management and have run into a

limitation

when the procedure is security definer. There doesn't appear to be any
reference to this in the
https://www.postgresql.org/docs/13/plpgsql-transactions.html so we

cannot

see when the limitation is lifted.

Are you able to clarify the situation with this between PG11, 12 and

13? We

are hoping the limitation is lifted as we cannot see what the reason for

the

limitation is.

You are going to need to be more specific if you want help, and this
doesn't seem like a documentation problem either.

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

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

--

James King

COO & Co-Founder

E:

W:

A:

james@yobota.xyz <erez@yobota.xyz>

yobota.xyz

Bentima House, 168-172 Old Street

EC1V 9BP

On Wed, Jul 15, 2020 at 10:52:04AM +0100, James King wrote:

Hi Bruce,

Thanks for taking a look at this.

This is really about making a limitation obvious.� In other parts of the
documentation, like table partitioning, limitations are really spelled out,
good solid health warnings for those uninitiated.� For transactions we have
found that procedures cannot handle commit and rollbacks when marked as
security definer, we found this out through trial and error (we don't have a
clue as to why the limitation is in there and are trying to figure it out).�
The only reference we found in the documentation was on�https://
www.postgresql.org/docs/11/sql-createprocedure.html�with the line "A�SECURITY
DEFINER�procedure cannot execute transaction control statements (for example,�
COMMIT�and�ROLLBACK, depending on the language)."��We only spotted this when
digging as to find any information as to why we received this error.

My reasoning of raising the point is it would have been really helpful for us
if the limitation was listed on the versions for�https://www.postgresql.org/
docs/13/plpgsql-transactions.html�so we didn't sleepwalk into a limitation and
if documented there we can see when the limitation is raised (we can't figure
out what the problem is with security definer as it works so well for our use
case).��

Well, we try to put warnings at the places where it is most relevant,
and I assume the CREATE PROCEDURE is the most relevent place to have it,
and we do. Putting it in other places can get tiresome on the reader.

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

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