We'll be giving an introductory talk about PostGIS and also give a brief overview of what's packed in the PostGIS 2.1 release that is currently in beta, and that should be out by meetup time. Remember this is the first version of PostGIS to support upcoming PostgreSQL 9.3.

Not only will we have cocktails, but we'll also be giving out PostgreSQL/PostGIS door prizes.

Friday, February 08. 2013

PostgreSQL allows you to customize statement logging in numerous ways. You can globally set the level of logging you want at both the postgresql.conf (that will affect all databases) or at the database level using the various log_statement* variables. Most of these
are documented in runtime config logging.

For this brief article, we'll talk about the log_line_prefix variable. By default this variable is very minimalistic and just prefixes the log lines with the date time of the statement. If you have just one database (like the way Oracle runs) this is not a big deal,
but if you are like us and run several databases on one PostgreSQL instance, it would be nice to as part of the prefix include the database name.

Monday, December 31. 2012

We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this:
http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.

As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.

-- there is no such thing as T for default priviledges that pg_dump understands based on
-- https://github.com/adunstan/postgresql-dev/blob/master/src/bin/pg_dump/pg_dump.c#L11838
-- backup bad records just in case we need them again --SELECT*into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype ='T';
--deleteunknownrecords--DELETEfrom pg_default_acl WHERE defaclobjtype ='T';

Wednesday, November 28. 2012

This was an article we were meaning to write and got reminded by Szymon Guz latest PostgreSQL search_path behavior.
The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. The pg_catalog schema is one that never requires schema qualification even if you don't add it to your schema search_path. Schemas are searched in the order they are listed
and when creating new objects (e.g. tables, views, function, types) that are not schema qualified, they are always created in the first schema of the search path. Any objects not in a schema listed in the search_path must be schema qualified.

Monday, January 16. 2012

If I could name a number one feature I love most about PostgreSQL, it's the table inheritance feature which we described in How to Inherit and Uninherit. A lot of people use it for table partitioning using CONSTRAINT EXCLUSION. Aside from that, in combination with PostgreSQL schema search_path (customizable by user and/or database) it makes for a very flexible abstraction tool. For example, for many of our web apps that service many departments where each department/client wants to keep a high level of autonomy, we have a schema set aside for each
that inherits from a master template schema. Each department site uses a different set of accounts with the primary schema being that of the department/client so that they are hitting their own tables.

Inheritance allows us to keep data separate,do roll-up reports if we need to, use the same application front-end, and yet allows us the ability to add new columns in just one place (the master template schema). It is more flexible than other approaches because for example we may have a city organization that need to share tables, like for example a system loaded list of funding source shared across the agency. We can set aside these shared tables in a separate schema visible to all or have some have their own copy they can change if they don't want to use the shared one.

Every once in a while, we find ourselves needing to query the whole hierarchy and needing to know which table the results of the query are coming from. To help
solve that issue, we employ the use of the system column tableoid which all user tables have. The tableoid is the the object id of a table. PostgreSQL has many system columns that you have to explicitly select
and can't be accessed with a SELECT * with the tableoid being one of them. These are: tableoid, cmax,cmin, xmin,xmax,ctid which are all described in System Columns. The PostgreSQL docs on inheritance have examples of using it, but we thought it worthwile to repeat the exercise since it's not that common knowledge and is unique enough feature of PostgreSQL that others coming from other relational databases, may miss the treat. I've often demonstrated
it to non-PostgreSQL users who use for example SQL Server or MySQL, and they literally fall out of their chair when I show the feature to them and its endless possibilities.

Monday, November 21. 2011

In this day and age of clouds and what not, it is still important to keep tabs on your database growth to ensure it doesn't
outgrow your disks or so that you can be prepared when it does. A common question that is asked is how big your database is or all your databases combined.
Luckily PostgreSQL has had for a long time functions to help you answer this easily. In this little article,
I'll demonstrate how to answer these questions and more with some PostgreSQL built-in functions

Tuesday, November 01. 2011

Ever have the need to create a holding table say spreadsheet data with say 100 columns. You need to create a table to hold this stuff. Or perhaps you were feeling in a sadist mood and wanted to abuse your PostgreSQL database to see how many columns you can create in a table of a specific data type.
Here is a quick script to do it:

Sunday, September 04. 2011

We've been working on converting some of our SQL Server apps to PostgreSQL. In this
article we'll describe some things to watch out for and provide a function we wrote to automate some of
the conversion.

Although both databases are fairly ANSI-SQL compliant, there are still differences
with their CREATE TABLE statements, data types, and how they handle other things that makes porting applications not so trivial.

Sunday, July 31. 2011

One of the biggest complaints about software and perhaps even more specifically FLOSS software is the lack of documentation. I'm not talking about those
small little dialogs we throw in our code that rarely see the light of day. I'm talking about stuff you throw in user docs or specifications and so forth
that an analyst or end-user reads.

The main reasons for this lack of documentation is that while everyone seems to want documentation, few really want to pay for it, and it's time consuming to keep
documentation in synch with what the software actually does. Having documentation that is very inconsistent with the software is worse than not having any documentation at all.
A good way to keep documentation up to date is to fold it into the process of developing and testing the software such that changes in software expose errors in the documentation and vice-versa
or that the act of changing the software corrects the documentation.

Part of the way we try to do that on the PostGIS project is to require each function patch to include documentation. We also try to make our documentation executable
by auto-generating PostgreSQL help and
some test suites from the documentation. For example the process of installing documentation-generated function and type descriptions
in PostgreSQL catches inconsistencies between
the implementation and what we actually publish about how the functions work. Even the image generator that builds most of the images in the manual is built with PostGIS as a core of it
so that when our build-bot is rebuilding the documentation it is exercising some PostGIS output functions.

I can't tell you how many times I've flagged changes in the current PostGIS 2.0 release
simply by trying to install the docbook generated PostgreSQL comment descriptions and PostgreSQL complains that the function signature no longer exists that the documentations says should be there.
So then I go back and correct
the documentation or yell at the programmer if the documentation makes more sense than what they coded. On the other side,
its easy to catch what we neglected to document simply by scanning the functions in PostgreSQL and seeing which ones don't have descriptions.

Okay as far as databases goes, many a DB Programmer/Analyst has waltzed into a project only to be befuddled about the meanings of all these tables used by the application.
If you are like us, you don't even want to look at any database documentation that is not part of the definition of the database because you know 99% of the time it's so obsolete
or was just a pipe dream of someone working in a vacuum
that its more useless than not having any
documentation at all. It is nice to have nicely formatted documentation you can read separate from the code,
but even sweeter if you can query the documentation just as easily as you can query the data.

A good way of having up to date documentation is to weave it in as part of the process of defining the structure. For databases
this means using foreign keys, primary keys, and using the commenting features that most relational databases offer these days. For example the databases we commonly work with,
PostgreSQL, SQL Server, MS Access, and even MySQL all allow you to provide descriptions for tables, table columns, and sometimes other objects such as functions and stored procs right in the database. PostgreSQL even allows you to provide descriptions of columns in
views though that's a bit messier to do. Sadly there isn't a consistent way of pulling these descriptions out of the database that will work for all of these. Each has differently defined meta tables it stores these descriptions in.
For thise article, we'll demonstrate how to pull this information from PostgreSQL.

PostgreSQL does offer many useful switches in psql for querying this data, but we'll focus our attention on pulling this data via SQL. It's much easier to incorporate this information
in auto-generated documentation with SQL because you can have more control what you include and how to format it.

We've started to play with PostgreSQL 9.1beta and the PgAdmin III 1.14.0 Beta 1. We'll briefly go over the cool gems
found in PgAdmin III beta 1. Most of the new features are for navigating the upcoming PostgreSQL 9.1. Well first obstacle we ran into
was we can't get our favorite extension, PostGIS, to compile against PostgreSQL 9.1beta though it did with the alphas, so you won't be seeing any windows experimental builds until we resolve this issue.
Details of ticket here? PostGIS 2.0 won't compile for PostgreSQL 9.1 beta1

Despite that minor set back, we decided to push on and navigate the new features by using PgAdmin III 1.14.0 as our Tour Guide. Below is a list of new features you can experience
via PgAdmin III 1.14.0 Beta 1. I'm sure there are more we missed, but these are the ones that were most flashing.

In the past I have always chosen to compile my own PostGIS because the GEOS library that came with the regular PostgreSQL yum install, was a bit antiquated.
This has changed, so this time around I figured I'd give it a go at using the Yum repository 1.5.2 release of PostGIS available via Yum Package List.

PostGIS in Action has started shipping from Amazon and we already have 3 positive reviews. We are hoping to write another book sometime soon, but haven't decided yet on the topic. Will definitely have something to do with databases and probably a lot of PostgreSQL in it.

Monday, April 18. 2011

In this exercise, we'll go thru installing PostgreSQL 9.0 on a Cent OS 5.5 32-bit box. This I'm testing on a GoGrid Cloud server so I can do parallel benchmarks between my windows GoGrid
and Linux GoGrid server.

Upgrading from PostgreSQL 8.* to PostgreSQL 9.0

For the rest of this article, we'll go over configuring your yum to use the PostgreSQL PGDG Yum repository managed by Devrim Gunduz, which has the latest and greatest of
9.0 as well as the 9.1 latest development release. We'll also demonstrate how to have two instances of PostgreSQL running so you can experiment with the new features of
PostgreSQL 9.1 while reminiscing about the soon to be old-hat features of PostgreSQL 9.0.

Monday, February 21. 2011

We were setting up another SQL Server 2005 64-bit where we needed a linked server connection to our PostgreSQL 9.0 server. This is something we've done before so not new and something we documented in
Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit.
What was different this time is that we decided to use the latest version of the new PostgreSQL 64-bit drivers now available main PostgreSQL site http://www.postgresql.org/ftp/odbc/versions/msi/.
Sadly these did not work for us. They seemed to work fine in our MS Access 2010 64-bit install, but when used via SQL Server, SQL Server would choke with a message:

Saturday, February 19. 2011

You have a system of products and categories and you want a product to be allowed to be in multiple categories, but you want a product to only be allowed to be in one main category.
How do you enforce this rule in the database?

Some people will say -- why can't you just deal with this in your application logic. Our general reason is that much of our updating doesn't happen at our application level. We like enforcing rules at the database
level because it saves us from ourselves. We are in the business of massaging data. For this particular kind of example we wanted to make sure the database would provide us a nice safety net so that
we wouldn't accidentally assign a product in two main categories.

Answer

There are two approaches we thought of. One is the obvious have a primary category column and a bridge table that has secondary categories. That is an ugly solution because when you do a query you have to do a union
and always treat the secondary categories as different from the main. For most use-cases we don't usually care about distinguisihing primary from secondary category.

The solution we finally settled on was to have one bridge table with a boolean field for if its the main category. We enforce the only one main category requirement using a partial index. Now not all databases support partial indexes
This is one major value of using PostgreSQL that you have so many more options for implementing logic.

As some people noted in the comments and the reddit entry. SQL Server 2008 has a similar feature called Filtered Indexes. Though PostgreSQL has had partial indexes for as far back as I can remember. This brings up an interesting point which I have observed -- if you were using PostgreSQL before, you would already know how to use the Filtered Indexes, Multi row inserts introduced in SQL Server 2008, and the SEQUENCES feature coming in SQL Server 2010. So we should all use PostgreSQL, because it teaches us how to use the newer versions of SQL Server before they come out. :)

So how does the partial index solution look: NOTE for simplicity, we are leaving out all the complimentary tables and the foreign key constraints that we also have in place.

In our last article we talked about String Aggregation implementing in PostgreSQL, SQL Server, and MySQL. This task is one that makes purist relational database programmers
a bit squeamish. In this article we'll talk about the reverse of that, how do you deal with data that someone hands you delimeted in a single field and that you are asked to explode or re-sort based on some lookup table.

Well for the casual programmer or simple text file database that knows nothing about JOINS and so forth, it makes it simple to pull a list of people who like Tumbling.
You simply do a WHERE ';' || activities || ';' LIKE '%;Tumbling;%'. It's great for security too because you can determine security with a simple like check and also list all the security groups a member belongs in without doing anything.
Quite easy for even the least data-skilled of programmers to work with because most procedural languages have a split function that can easily parse these into an array useful for stuffing into drop down lists and so forth. As a consultant of semi-techie people
I'm often torn by the dilemma of "What is the way I would program for myself vs. the way that provides the most autonomy to the client". By that I mean
for example I try to avoid heavy-weight things like Wizards that add additional bloated dependencies or slow the speed down of an application. These bloated dependencies may provide ease to the client but make my debugging life harder. So I weight the options
and figure out which way works now and also provides me an easy escape route should things like speed or complexity become more of an issue.

This brings us to the topic of, what is wrong with this model? It can be slow because the LIKE condition you have can't easily take advantage of an index unless using a full text index so not ideal where this is the primary filtering factor. It's also prone to pollution because
you can't easily validate that the values in the field are in your valid set of lookups or if your lookup changes, the text can be forced to change with a CASCADE UPDATE/DELETE RULE etc. In cases where this is of minor consequence
which is many if referential integrity is not high on your list of requirements, this design is not bad. It might make a purist throw up but oh well there is always dramamine to fall back on. As long as you have done your cost benefit analysis, I don't think there should be any shame of following this
less than respected route.

While you may despise this model, it has its place and it's a fact of life that one day someone will hand it to you and you may need to flip it around a bit. We shall demonstrate how to do that in this article.