Bryn Llewellyn <bryn@yugabyte.com> writes:
> I want to demonstrate how to meet this requirement:
> «
> Encapsulate each business function in a user-defined subprogram that hides all the implementation details like table
namesand the SQL statements that manipulate their contents so the they cannot be seen using SQL issued from the client.
Further,don't allow raw errors to escape to the client. Rather, if an expected error occurs (like a unique key
violation),then report this as an application-specific code that translates to, for example, "This nickname is already
taken.Choose a different one." And if an "others" error occurs (typically because the programmer forgot to cater for
it—likea too-wide varchar value) insert diagnostic info into an incident log table and return an "unexpected error"
application-specificcode together with the incident ID so that it can be reported to Support.
> »
> I've written proof-of-concept code that shows how to meet this requirement for most scenarios. But it seems to be
impossibleto meet the requirement for errors that occur at commit time.
So ... avoid those? It seems like it's only a problem if you use deferred
constraints, and that's not a necessary feature.
> Is there simply no way that inserts into table "t" in my example can be encapsulated in PL/pgSQL so that the error
fromthe failing trigger can be handled rather there than escaping, raw, to the client?
If you want a strict view of that you probably need to be doing the
encapsulation on the client side. There's nothing you can do on the
server side that would prevent, say, network-connection failures
"escaping" to the client. And that's actually one of the harder
cases to deal with: if the connection drops just after you issue
COMMIT, you can't tell whether the transaction got committed.
regards, tom lane