Postgres and Open Source Experts

This has been cooking for a while now, and now it's time to open it up: July 31st, 2012 was my last day with Command Prompt, Inc.

I joined Command Prompt in October 2005. Back then I wasn't a very prolific blogger, it seems, because it took me six months to get this fact out. I haven't improved much since then, even though boss Josh Drake kept telling me …

This is something I have seen many times now: a customer calls us because they lost some data and they want help recovering.

Now you must be wondering: surely if they lost data they can just recover from their last backup, right? Right — they had that. However we know that pg_dump takes a while to run and is stressful on the server, so it's normally run just once a …

Come on, admit it: you've always wanted to display the infomask bits from a tuple header in a human-readable manner, but you've never gotten around to it and you still keep htup.h in display while you peek around tuples.

Fortunately, that time is now past! Here's a short and simple recipe to decode the bits for your reading pleasure. Gone is the htup.h cheat sheet. Here's what you need:

create …

As I have posted in previous articles ( Fixing foreign key deadlocks and Part 2), I am working on reducing the lock strength required by foreign key checks. I have a working patch that solves a lot of the problems already; however it doesn't solve the one problem that I initially set to fix. It turned out to require a bit more rejiggering than I initially considered.

Note: this …

For those that are curious (and which for some reason don't follow the #pgcon tag at twitter), here's a picture of the attendees of Developer's Meeting.

Back row, from left to right: Robert Haas, Selena Deckelmann, Marko Kreen, KaiGai Kohei, Stephen Frost, Magnus Hagander , Robert Treat, Tom Lane, Heikki Linnakangas, Mark Wong, Josh Berkus, Kevin Grittner, Dimitri Fontaine, Koichi Suzuki, Andrew Dunstan, Fujii Masao, Jeff Davis, Greg Smith, Tatsuo …

Some time ago, a customer came to us with a strange vacuuming problem. Their regular vacuum job would die with a message such as this one:

vacuumdb: vacuuming of database "clint_app" failed:
  ERROR: failed to re-find parent key in index "work_items_pkey" for deletion target page 6100

Eventually, it turned out that their storage firmware had some glitch that caused things to go wrong in random ways, and corruption in various …

I got curious about a bloat problem on a customer's system today. The statistics as calculated by normal tools/queries say that one of the tables is 2x bloated. Another table is 6x bloated. And so on. For some reason I wanted to see what it looked like in a graphical way, so I threw together this simple query:

select s, coalesce(count, 0)
  from (select block, count(*)
          from (select split_part(tid, ',', …

Last week, a company's only PostgreSQL database server suffered an UPS failure. When they found they couldn't connect to it afterwards, they called Command Prompt to investigate.

The most important lesson to be learned here is: you need to make very sure you have backups. Even if the strategy is too simple, or even if they are taken only once a fortnight, they are going to save your neck …

It all started with a help request from a someone on IRC: he has about 2500 roles, and all of them have been granted USAGE privileges on a schema. He went on to create a bunch more and grant the same privilege, but it failed:

CREATE ROLE some_user_2501;
GRANT USAGE on schema genetics to some_user_2501;

ERROR:  row is too big: size 8168, maximum size 8164

Oops.

Now, let this be …

A week ago, I submitted my patch to fix the foreign key lock problem. What I propose is almost exactly what was described in my previous blog posts, with a couple of differences.

The first is that I decided to only check unique indexes, not all indexes. This is good because you can have other indexes for performance and they will not affect locking for foreign keys. I noticed that …