Re: Transaction Management

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

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