internals

When in a pl/pgsql function there is an exception then the function stops the execution and returns an error. When this happens all the changes made are rolled back.

It’s always possible to manage the error at application level, however there are some cases where managing the exception inside the function it may be a sensible choice. And pl/pgsql have a nice way to do that. The EXCEPTION block.

However handling the exception inside a function is not just a cosmetic thing. The way the excepion is handled have implications that may cause issues.

The transactional model has been in PostgreSQL since the early versions. In PostgreSQL its implementation follows the guidelines of the SQL standard with some notable exceptions.

When designing an application it’s important to understand how the concurrent access to data happens in order to avoid unexpected results or even errors.

In the previous post we introduced the PostgreSQL’s dependency system.

At first sight the implementation can look like a maze where the succession of relationships are not clear.

This post will try to give a practical example to show how pg_depend can act like an Ariadne’s thread in order to resolve the dependencies.

The scenario presented is very simple but can be used as a starting point for more complex requirements.

One of the things that distinguish PostgreSQL from other database systems is the way the dependencies between database objects are managed.

PostgreSQL do not allow any invalid object like views or functions. Therefore any action that can invalidate dependent objects results in an error or it’s propagated to the dependent objects.

The mechanism could be confusing at first sight. However the implementation is brilliant and gives great flexibility.

Back in the days of when I was an Oracle DBA I had to solve a strange behaviour on an Oracle installation. The system for some reasons stopped accepting the writes occasionally and without an apparent reason. This behaviour appeared odd to anybody except for my team, which addressed the issue immediately. The problem was caused by a not optimal configuration on the Oracle instance. This thought led me to writing this post.