Bryn Llewellyn <b...@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 names and 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—like a
> too-wide varchar value) insert diagnostic info into an incident log table and
> return an "unexpected error" application-specific code 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 impossible to 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 from the 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
