The Case Against Triggers and Stored FunctionsĪ review of commentary on the PostgreSQL General mailing list revealed some opinions unfavorable toward the use of triggers and stored functions which I mention here for completeness and to encourage you and your team to weigh the pros and cons for your implementation.Īmongst the objections were, for example, the perception that stored functions are not easy to maintain, thus requiring an experienced person with sophisticated skills and knowledge in database administration to manage them. Also, in PostgreSQL stored functions can be configured to execute code at a higher privilege level than the session user, which admits some powerful capabilities. Additionally, data processed on the server rather than in application code does not cross the network, so there is some lesser risk of data being exposed to eavesdropping as well as a reduction in network congestion. Another common use of triggers and stored functions is to make views writable, i.e., to enable inserts and/or updates on complex views or to protect certain column data from unauthorized modification. This ensures consistent execution of DML code for data validation, data cleansing, or other functionality such as data auditing (i.e., logging changes) or maintaining a summary table independently of any calling application. By building DML code into the database itself, you can avoid duplicate implementation of data-related code in multiple separate applications that may be built to interface with the database. Let’s talk about why you might want to invest in understanding triggers and stored functions.
In this article we delve further into another more capable aspect to automatically invoke custom code, namely the use of triggers and stored functions.Introduction Use Cases for Triggers and Stored Functions
This automated behavior of invoking functional code as part of the integral response to data manipulation language (DML) activity is a powerful feature of sophisticated relational database management systems (RDBMS) like PostgreSQL. We saw that employing the serial data type keyword in a table data definition language (DDL) statement is implemented as an integer type column declaration that is populated, upon a database insert, with a default value derived from a simple function call.
In a previous article we discussed the PostgreSQL serial pseudo-type, which is useful for populating synthetic key values with incrementing integers. We honor his contributions to the PostgreSQL community and wish peace for our friend and guest writer. Note from Severalnines: This blog is being published posthumously as Berend Tober passed away on July 16, 2018.
0 kommentar(er)
