Posts by: craig.ringer

Emulating row security in PostgreSQL 9.4

PostgreSQL 9.5 adds declarative row security. You can declare policies on tables and have them enforced automatically – for example, allowing user joe to only see rows with the owner column equal to joe. This is a great feature, and it’s been a long time coming. It didn’t make it into PostgreSQL 9.4, but automatically…

When are we going to contribute BDR to PostgreSQL?

My colleagues and I are often asked “when will you (2ndQuadrant) contribute BDR to PostgreSQL?” This makes an interesting assumption: that we have not already done so. We have. Much of BDR has already been contributed to core PostgreSQL, with more to come. All of BDR is under the PostgreSQL license and could be merged…

How to find out which PostgreSQL table a file on disk corresponds to

Sometimes you need to determine which table a file on disk corresponds to. You have a path full of numbers like base/16499/19401 and you want to make sense of it. You might be looking at an error message that mentions a file name, for example, like: ERROR: could not read block 11857 of relation base/16396/3720450:…

BDR 0.9.2 and BDR-PostgreSQL 9.4.4 released

Version 0.9.2 of the BDR (Bi-Directional Replication) extension for PosgreSQL has been released. This is a maintenance release in the current stable 0.9.x series, focused on bug fixes, stability and usability improvements. In particular bdr_init_copy, the pg_basebackup-based node bring-up tool, is significantly improved in this update. This release also updates the BDR-patched version of PostgreSQL…

PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns

PostgreSQL has json support – but you shouldn’t use it for the great majority of what you’re doing. This goes for hstore too, and the new jsonb type. These types are useful tools where they’re needed, but should not be your first choice when modelling your data in PostgreSQL, as it’ll make querying and manipulating…

Dynamic SQL-level configuration for BDR 0.9.0

The BDR team has recently introduced support for dynamically adding new nodes to a BDR group from SQL into the current development builds. Now no configuration file changes are required to add nodes and there’s no need to restart the existing or newly joining nodes. This change does not appear in the current 0.8.0 stable…

BDR for PostgreSQL: Present and future

For a couple of years now a team at 2ndQuadrant led by Andres Freund have been working on adding bi-directional asynchronous multi-master replication support for PostgreSQL. This effort has become known as the BDR project. We’re really excited to see these efforts leading to new PostgreSQL features and have a great deal more still to…

All-processes breakpoints / watchpoints for PostgreSQL

Sometimes SELECT pg_backend_pid() and gdb‘s attach aren’t enough. You might have a variable in shared memory that’s being changed by some unknown backend at some unknown time. Or a function that’s called from somewhere, but you don’t know where or when. I’ve recently been doing quite a bit of work on code where bgworkers launch…

Ware Yosemite? Possible PostgreSQL upgrade issues in OS X 10.10

I’m seeing reports of a number of issues with PostgreSQL after upgrades of OS X machines to Yosemite (OS X 10.10) that I’m concerned about, so I’m seeking more information about the experiences of PostgreSQL users who’ve done OS X 10.10 upgrades. I can’t confirm anything yet, but back up all your databases before any…

Compiling and debugging PostgreSQL’s PgJDBC under Eclipse

I’ve always worked on PgJDBC, the JDBC Type 4 driver for PostgreSQL, with just a terminal, ant and vim. I recently had occasion to do some PgJDBC debugging work on Windows specifics so I set up Eclipse to avoid having to work on the Windows command prompt. As the process isn’t completely obvious, here’s how…

123