-
NoCOUG SQL challenge
NoCOUG (which stands for Northen California Oracle Users Group) published an SQL challenge [PDF]: using SQL determine the probability of achieving a given number by throwing a non-balanced dice N times. Being a PostgreSQL fanboy that I am, I’ve given a try with PG. Here are the results: To create the table and populate it…
-
Small programming tips
A quickpost inspired by issues I encountered recently. How to concatenate (aggregate) strings with PostgreSQL? In MySQL you can write: SELECT concat(name) FROM test_table Because concat also works as an aggregate function (like MIN, MAX, SUM, COUNT, etc). To get the equivalent result in PostgreSQL, you can use the following query (based on ideas from…
-
PostgreSQL data corruption issues
Lately I’ve been helping out a friend with PG data corruption issues. Usually PG is pretty good about data consistency, but it too can fail under extreme conditions (multiple power failures, fsync=off in the name of speed, no battery-backed RAID controller). The interesting thing I didn’t realize, is that your transaction log can get corrupted!…
-
PostgreSQL musings
First, a very good article about creating (and maintaining!) data clustering with PostgreSQL. This made me think: wouldn’t it be nice if the automated tuning wizards would give you a short article to read which discusses the proposed solution instead of just the “turn knob X” type of suggestions? Also, Percona is hiring performance experts,…
-
Random Database Blogging
From the Database and Performance blog: Queuing Theory & Resource Utilization – a lightweight introduction into the field which explains why you don’t have linear growth all the way – at a moment you hit a magic ceiling and things get much worse. PostgreSQL Replicator is an other way to replicate your PostgreSQL database. Now…
-
pl/lolcode
The news (via Joshua Drake’s blog): video / audio / slides available for two more talks on the postgresql conference site. Now for the funny part (this is from the slides of the “Babel of Procedural Languages” by David Fetter): HAI CAN HAS DATABUKKIT? I HAS A RESULT I HAS A RECORD GIMMEH RESULT OUTTA…
-
How to be the coolest DBA out there…
By managing your PostgreSQL install with your iPhone! 😀
-
Poor man’s traffic logger
I was reading the following blog post about filtering out MySQL queries and was reminded of a situation I faced once. The situation was as follows: I needed to find out where certain PostgreSQL queries were coming from, however the server was behind a pgpool instance, so all the queries were seen as coming from…
-
A small Slony-I tutorial
Update: the altperl scripts seem to take away the need for most of the steps here. Take a look at the post Slony1-2.0.0 + PostgreSQL 8.4devel for an example for how to use them. When I first installed Slony-I to replicate between two PostgreSQL servers, it was very confusing. Now I am somewhat less confused…
-
Using Perl to access PostgreSQL under Windows
This appears by a non-intuitive problem for people. Below I assume that you are using some version of ActivePerl for Windows (5.8 or 5.10). First of all: Under no circumstances (ok, I rephrase: only under extreme circumstances) should you use DBD::PgPP. It is old, not very performant (given that its implemented in Pure Perl) and…