A while ago I wrote about compiling PostgreSQL extensions under Visual Studio – without having to recompile the whole PostgreSQL source tree. I just finished the pg_sysdatetime extension, which is mainly for Windows but also supports compilation with PGXS on *nix. It’s small enough that it serves as a useful example of how to support […]
RPMs for BDR (Bi-Directional Replication for PostgreSQL) are now available for testing. They contain BDR release 0.7.1, which is based on PostgreSQL 9.4beta2. Because there may be an on-disk format change (catversion bump) before PostgreSQL 9.4.0 final, and because these RPMs are quite new, this should be considered a test release. RPMs are available for […]
Shaun Thomas’s recent post about client-side loops as an SQL anti-pattern is well worth a read if you’re relatively new to SQL-based application development. It’s reminded me of another SQL coding anti-pattern that I see quite a lot: the naïve read-modify-write cycle. Here I’ll explain what this common development mistake is, how to identify it, […]
As part of AXLE project work I recently found myself re-examining issues around optimisation fencing and non-semantic query execution changes in PostgreSQL. One key issue was the inability to use a CTE where optimisation (inlining, push-up/pull-down, flattening, etc) was desired. I’d like to explain that in more detail, as it’s something that can surprise new […]
I periodically see people being advised to put their tablspaces on RAM disks or tempfs volumes. This is very bad advice. Do not put a PostgreSQL TABLESPACE on a RAM disk or tempfs. Why you shouldn’t put a tablespace on a ramdisk Unlike MySQL and some other databases, PostgreSQL tablespaces are not completely independent of […]
In the next week I will be writing a series of posts about the row-security work I’ve been doing for PostgreSQL 9.4 as part of the EU’s AXLE project. I will be outlining the history, approaches tried, current status, remaining issues, and future work required. To open the series, I’d like to talk about what […]
Streaming replication slots are a pending feature in PostgreSQL 9.4, as part of the logical changeset extraction feature. What are they for, what do you need to know, what changes? What are replication slots? Streaming replication slots are a new facility introduced in PostgreSQL 9.4. They are a persistent record of the state of a […]
I’ve seen a number of users struggling with building PostgreSQL extensions under Visual Studio, so I thought I’d see what’s involved in getting it working. The result is this tutorial, showing how to compile a simple extension with Visual Studio 2010 Express Edition. Requirements You will need a supported version of Visual Studio installed. These […]
I’m delving into the PostgreSQL parser, query rewriter and query planner at the moment, as part of work on row-level security for the AXLE project. As I’ve noticed that there’s some great documentation on the overall structure and flow but not much on some of the details I thought I’d start posting about some of […]
You might have seen the support added for security_barrier views in PostgreSQL 9.2. I’ve been looking into that code with an eye to adding automatic update support for them as part of progressing row-level security work for the AXLE project, and I thought I’d take the chance to explain how they work. Robert already explained […]
