Back from the FODESM 2014 Conference, here’s the slides I’ve been using for the Advanced Extension Use Cases talk I gave, based on the ongoing work to be found under the Tour of Extensions index in this web site.
36 Articles tagged “Extensions”
Last week I had the pleasure to present two talks at the awesome PostgreSQL Conference Europe. The first one was actually a tutorial about Writing & using Postgres Extensions where we spent 3 hours on what are PostgreSQL Extensions, what you can expect from them, and how to develop a new one. Then I also had the opportunity to present the new version of pgloader in a talk about Migrating from MySQL to PostgreSQL.
In our Tour of Extensions today’s article is about advanced tag indexing. We have a great data collection to play with and our goal today is to be able to quickly find data matching a complex set of tags. So, let’s find out those lastfm tracks that are tagged as blues and rhythm and blues, for instance.
We’re going to use the Last.fm dataset from the Million Song Dataset project here.
PostgreSQL is an all round impressive Relational DataBase Management System which implements the SQL standard (see the very useful reference page Comparison of different SQL implementations for details). PostgreSQL also provides with unique solutions in the database market and has been leading innovation for some years now. Still, there’s no support for Autonomous Transactions within the server itself. Let’s have a look at how to easily implement them with PL/Proxy.
Let’s get back to our Tour of Extensions that had to be kept aside for awhile with other concerns such as last chance PostgreSQL data recovery. Now that we have a data loading tool up to the task (read about it in the Loading Geolocation Data article) we’re going to be able to play with the awesome ip4r extension from RhodiumToad.
In our previous article about Loading Geolocation Data, we did load some data into PostgreSQL and saw the quite noticable impact of a user transformation. As it happens, the function that did the integer to IP representation was so naive as to scratch the micro optimisation itch of some Common Lisp hackers: thanks a lot guys, in particular stassats who came up with the solution we’re seeing now.
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
trigrams PostgreSQL extension: its usage got seriously enhanced in
recent PostgreSQL releases and it’s now a poor’s man
Full Text Search
In a previous article about
Trigger Parameters we have been using the
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
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
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.