In checkpoint post we saw how a misbehaving session can crash PostgreSQL.
Before the version 9.6 the only option for avoiding this sort of things was to setup a script, usually via cron which killed the idle in transaction sessions after a specific timeout. In this post we’ll see in details what are the problems related with this approach and the new features implemented from PostgreSQL 9.6 can help the DBA to tackle potentially dangerous situations.
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.
