Re: Transaction Management

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(at)momjian(dot)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(at)momjian(dot)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(at)yobota(dot)xyz <erez(at)yobota(dot)xyz>

yobota.xyz

Bentima House, 168-172 Old Street

EC1V 9BP