I'm one of those old-fashioned folks that debugs with print lines and raise notices. They're nice.
They always work, you can put clock time stops in there and don't require any fancy configuration.
At a certain point you do have to pull out a real debugger to see what is going on. This often
happens when your one-liners are no longer good enough and now you have to write 20 liners of plpgsql code.
Such is the case with geocoding and the PostGIS tiger geocoder specifically. Lots of interest has revived
on that with people submitting bug reports and we've got paying clients in need of a fairly easy and speedy drop-in geocoder
that can be molded to handle such things as road way locations, badly mis-spelled real estate data, or just simply
to get rid of their dependency on Google, Yahoo, MapQuest, ESRI and other online or pricey geocoding tools.
So I thought I'd take this opportunity to supplement our old-fashioned debugging with plpgsqldebugger goodness.
In this article, we'll show you how to configure the plpgsql debugger integrated in PgAdmin and run with it.
The Lisp programmer stood up, a bit in disgust, and said, "No no! You are doing it all wrong!" The Lisp Programmer then pulled out
a Polish calculator, punched in + 1 2
,and with a very serious face, explained "+ should be pushing those other two around."
I find this episode interesting because while the Lisp programmer I feel is more right, the Smalltalk programmer has managed to follow the rest of the crowd and still stick
to her core principle. This brings us to what does this have to do with trigrams
in PostgreSQL 9.1. Well just like 1 + 2 being a common mathematical expression, abc LIKE '%b%' is a common logical relational database expression that we have long taken for granted as not an indexable operation in most
databases (not any other database to I can think of) until PostgreSQL 9.1, which can utilize trigram indices (the Lisp programmer behind the curtain) to make it fast.
There are 2 main enhancements happening with trigrams in PostgreSQL 9.1
both of which depesz has already touched on in FASTER LIKE/ILIKE
and KNNGIST. This means you can have an even faster trigram search than you ever
have had before and you can do it in such a fashion that doesn't require any PostgreSQL trigram specific syntactical expressions. So while PostgreSQL 9.1 might be understanding LIKE much like all the other databases
you work with, if you have a trigram index in place, it will just be doing it a little faster and sometimes a lot faster using the more clever PostgreSQL 9.1 planner.
This is one example of how you can use applications designed for many databases and still be able to utilize advanced features in
your database of choice. In this article we'll demonstrate.
For this example we'll use a table of 490,000 someodd records consisting of Massachusetts street segments and their names excerpted from TIGER 2010 data. You can
download the trimmed data set from here if you want to play along.
PostgreSQL 8.4 introduced the ability to create user-defined variadic functions. These are basically
functions that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. Depesz went over it two years ago in Waiting for 8.4 variadic functions,
so we are a bit late to the party. In a nutshell -- variadic functions are syntactic sugar for functions that would otherwise take arrays. In this article we'll provide some more demonstrations of them to supplement Depesz article.
I was reminded that I had never explored this feature, when recently documenting one of the
new PostGIS 2.0 Raster functions - ST_Reclass which employs this feature.
I think ST_Reclass is a superb function and one of my favorite raster functions thus far that I hope to put to good use soon. Our new PostGIS family member,Bborie Park, is running thru our
PostGIS Raster milestones much faster than I had dreamed. He's already implemented a good chunk of stuff we discussed in Chapter 13 - PostGIS Raster and had stated you probably won't see in PostGIS 2.0. He's
going a bit faster than I can catalog them, so the documentation is already embarrassingly behind the fantastic functionality that is already present in PostGIS 2.0.