203 Articles tagged “PostgreSQL”

Have you heard about the Open World Forum conference that takes place in Paris, October 3-5, 2013? I’ll be presenting a talk about PostgreSQL in the track NewSQL: Managing large data sets with relational technologies. *Open World Forum used to be Open Source Developers Conference* My talk is PostgreSQL is web scale and here’s the summary: We call it the world’s most advanced open source database, and we are actually offering in the same package full ACID compliance per default and advanced trade-offs to reach any kind of flexibility needed, all with per-transaction controls.

The following story is only interesting to read if you like it when bad things happen, or if you don’t have a trustworthy backup policy in place. By trustworthy I mean that each backup you take must be tested with a test recovery job. Only tested backups will prove useful when you need them. So go read our Backup and Restore documentation chapter then learn how to setup Barman for handling physical backups and Point In Time Recovery.

In our ongoing Tour of Extensions we played with earth distance in How far is the nearest pub? then with hstore in a series about trigger, first to generalize Trigger Parameters then to enable us to Auditing Changes with Hstore. Today we are going to work with pg_trgm which is the trigrams PostgreSQL extension: its usage got seriously enhanced in recent PostgreSQL releases and it’s now a poor’s man Full Text Search engine.

In a previous article about Trigger Parameters we have been using the extension hstore in order to compute some extra field in our records, where the fields used both for the computation and for storing the results were passed in as dynamic parameters. Today we’re going to see another trigger use case for hstore: we are going to record changes made to our tuples.

Sometimes you want to compute values automatically at INSERT time, like for example a duration column out of a start and an end column, both timestamptz. It’s easy enough to do with a BEFORE TRIGGER on your table. What’s more complex is to come up with a parametrized spelling of the trigger, where you can attach the same stored procedure to any table even when the column names are different from one another.