Experts in Postgres and Open Source Infrastructure

24x7, 365 Enterprise services since 1997


PostgreSQL Conference: Good Lord... twice as many as we expected

When Selena Deckelmann and I decided to start a PostgreSQL Conference series, we believed that a small, regional, technically specific conference was appropriate. We considered that at most, we would get around 35 people and it would essentially be a large user group meeting that had a series of talks and was good for the community. That was the end of July. It is now the night before the conference, …

plphp 1.3.5 Beta 1 Released

Not much to say here but plphp 1.3.5 Beta1 supports named parameters and PostgreSQL 8.3 Beta1. Enjoy :) PL/php website

PostgreSQL Gotcha: Default timestamps are not exact!

If you are compiling PostgreSQL from source you have a configure option called:
Now Debian/Ubuntu wisely turn this option on by default, unfortunately the RPM provided by PostgreSQL.Org and the RPM provided by RedHat/Fedora do not. Why is this a problem? I think we can all agree that given that a particular value is within a column that value should be able to be retrieved from that column. Consider …

Recursive PLPGSQL, Lookup Table and Ltree

There are many reasons and needs to implement a tree structure inside of an SQL database. In our particular example today, we're using a basic knowledge base tree as an example of a small- to large-scale tree inside of an SQL database. Additionally, any sort of directory/file system layout would be a good match for these concepts. I am going to discuss three basic concepts on dealing with small, medium, …

The easy but harder than I thought road to PostgreSQL Conference Fall 2007

The PostgreSQL Conference Fall 2007 has given me a knew appreciation for the little things. If it wasn't for PDXPUG leader Selena Deckelmann the conference would not have the complimentary breakfast/social nor would it have the synopsis of the talks on the conference website. The hard work of Selena, myself and the speakers appears to be paying off. We have a strong list of sponsors as well as a very …

Log level correlations

There was a post recently on using Syslog with PostgreSQL on pgsql-general. As you can see, Tom Lane kindly replied in the thread. I decided that it might be a good idea to submit a DOC patch. I only received on comment on the patch, so I don't know if it will be applied or not. The comment, caused me to consider creating a table for the docs that correlated …

PostgreSQL Conference Fall 07

When sitting in the booth with the Army of Smurfs (tm) at OSCON 2007, I was struck by the idea that having PostgreSQL Conference(s) (yes plural) could be a great way to insure the growth of our community. Check -- duh! You would think that this is obvious. In fact I would think this is obvious. This is why the community had the Anniversary two years ago and Dan Langille …

PostgreSQL 8.3, it's faster, really it is!

I downloaded the latest check out of PostgreSQL 8.3 (not even beta yet) just to see how things are progressing. Of particular interest to me was a recent conversation about sequential scans I had with Jeff Davis. In short, in 8.3 sequential scans should be faster. I decided to test the theory. Using the exact same machine, and an optimized 8.1.8 installation, I compared 8.1.8 versus a out of the …

How many rows do I have anyway?

Have you ever wondered how many tuples you have in a relation? Normally to find out how many tuples you have you would do something like:
SELECT count(1) FROM rows;
This will return the exact number, per your snapshot of committed tuples within a relation. It is also a guaranteed sequence scan on the relation and a performance nightmare on large tables. So how do you get …

Surrogate versus Natural Primary Keys

This is a constant source of argument, flame and general discomfort with any database design. On the PostgreSQL lists it comes up occasionally and it is always a long drawn out thread with people arguing on each side about which one is correct. Before I go on about the good and bad of both, let me define a couple of things. 1. Surrogate keys (also known as artificial keys) are …