Experts in Postgres and Open Source Infrastructure

24x7, 365 Enterprise services since 1997


... and that was my last day

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 …

The Write–Ahead Log

WAL (acronym for Write–Ahead Log) is the mechanism that Postgres uses to implement durability of data changes in the face of abrupt system crashes.

The WAL system's central concept is that changes to data files must be written to such data files only after those changes are guaranteed to have been logged to WAL, that is, when WAL records have been flushed to permanent storage. (This flushing is accomplished by …

Another day, another recovery

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 …

Decoding infomasks

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 …

Fixing foreign key deadlocks, part three

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 …

PGCon 2011 Developer's Meeting picture

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 …

An attempt at finding glaring btree problems

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 …

This is what table bloat looks like

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, ',', …

Recovering a lost-and-found database

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 …

Grant schema usage to 2500 users? No can do!

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


Now, let this be …