> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> 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.
I'll take this to mean that there is no plan for PG ever to allow txn control in a PL/pgSQL block that has an exception handler. Please tell me if I misunderstood. Your point about a certain class of server side error is well taken. (In Oracle Database, at least, errors like that cannot be handled in an exception block. They inevitably escape to the client. Is it the same in PG? But I'm not convinced by this "what-about-ism" argument that it's pointless to handle those errors that allow it so that hackers get only the bare minimum information on things like schema-object names and the like. The more that hackers know about a system's internals, the better are their chances of doing evil. You said "it's only a problem if you use deferred constraints, and that's not a necessary feature". My example was contrived. But some requirements (like entity level constraints) require commit-time checking. Like, say, a department must have just one or two staff whose job is 'Admin'. (This is presumably why the feature exists.) Using the serializable isolation level is another possible approach. But doing that can also lead to commit-time errors. Is there really no sympathy for what I want to achieve?
