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
