Thursday, July 27

Author: Andrew Dunstan

Announcing Release 5 of the PostgreSQL Buildfarm Client

Andrew's PlanetPostgreSQL
Release 5 of the PostgreSQL Buildfarm Client has been released and can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-5.tgz In a similar move to PostgreSQL version numbering, with this release we move to a one part numbering system. In addition to a number of bug fixes and very minor changes, this release has the following features / changes: Cross-version pg_upgrade module is no longer experimental - see below TAP tests now run the "check" target, but in most cases redundant installs are avoided Improved and expanded TAP test running on MSVC animals - these now run the same tests as other animals Automatic garbage collection of git repositories, once a week by default. This should improve the speed of git operations, especially on (more…)

Up to date access to postgres logs

Andrew's PlanetPostgreSQL
Some of my Italian colleagues have made a nifty little gadget called redislog for pushing postgres logs into Redis, the distributed in-memory cache. From there it can be fed into things like logstash. I thought it would be interesting instead to make the logs available via the Redis Foreign Data Wrapper as a Postgres table. That way we would have easy access to the running logs from Postgres with almost no effort. Here's what I did. First I built and installed redislog and redis_fdw. Then I added redislog to my server's shared_preload_libraries,  set log_min_duration_statement to 0 and restarted. Then I created a database called logger and did this in it: create extension redis_fdw; create server localredis foreign data wrapper redis_fdw; create foreign table (more…)

Out of tree builds

Andrew's PlanetPostgreSQL
Recently I was asked "what's a vpath build?" If you regularly build postgres from source it's something you should know about. A vpath build is one where the build tree is kept completely separate from the source tree, so you can completely remove the build tree and your source tree is still there, clean and pristine. It's very easy to set up unless you're building with the Microsoft toolset. Essentially what you do is create the root of your build tree, change directory into that directory, and then call configure in your source tree from there.  configure knows all about setting up the vpath tree and does all the work for you. After that, you just run make etc just like you would normally. So it looks like this: mkdir mybuild cd mybuild /path/to/postgresql-source/configure make (more…)

BuildFarm client release 4.19

Andrew's PlanetPostgreSQL
I have released version 4.19 of the PostgreSQL Buildfarm client. It can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-4_19.tgz Apart from some minor bug fixes, the following changes are made: Include the script's path in @INC. That means you can usually run the script from anywhere rather than just its own directory. Set TZ after "make check" is run. This makes for slightly faster initdb runs in later stages. make TAP tests run with --timer change default log_line_prefix in config file to use %p instead of %c. That makes it easier to tie log messages to other messages that mention a pid Add a module to log running commands to a file as it runs and replace critical uses of `` with the new procedure. That means we have better traces if (more…)

Support for enums in btree_gin and btree_gist

Andrew's PlanetPostgreSQL
I have just committed a series of patches that provide support for enum types in the btree_gin and btree_gist standard extensions. This is something I first started work on about a year ago. It turned out to be more involved that I had first thought it would be, as it requires some additional core code due to the way that enum comparisons work, which is a bit more complex than for most data types, and involves use of PostgresSQL's internal caching mechanism. The practical upshot of this, however, is that starting with PostgreSQL 10 you will be able to use enum columns in exclusion constraints. That's something that could be very useful - I started this work when I found, somewhat to my surprise, that it wasn't possible.    

JSON version of XMLTABLE example

Andrew's PlanetPostgreSQL
My colleague Álvaro Herrera gave a terrific explanation of the new XMLTABLE feature from Pavel Stěhule that he's put a huge amount of effort into, and finally recently committed. I thought it would be fun to see how the example he gave might work today with JSON. First, I came up with a JSON equivalent version of his piece of XML. Notice that in JSON, unlike in XML, containers are nameless, so we have no "room"  tags, for example, a room is just an object in the relevant array. This is what I came up with: CREATE TABLE IF NOT EXISTS hoteldata AS SELECT json $$[ {"id": "mancha", "name": "La Mancha", "rooms": [ {"id": "201", "capacity": 3, "comment": "Great view of the Channel"}, { "id": "202", "capacity": 5 } ], "personnel": [ {"id": "1025"," (more…)

Problem with Buildfarm registrations fixed

Andrew's PlanetPostgreSQL
Recently we switched the PostgreSQL Buildfarm to use https for its front-facing web transactions. That's worked pretty well, but one thing got broken in the change. The registration page uses a Captcha to help avoid web spam, which has been a problem in the past. However, the code to fetch the relevant html was still getting plain http, and thus browsers were refusing to display the Captcha because it came from an untrusted source. Without the Captcha the registration was rejected. This was discovered yesterday, and has now been fixed. The Captcha code now uses https. If you have recently found a buildfarm registration rejected like this, please resubmit it.

The rds_superuser role isn’t that super

Andrew's PlanetPostgreSQL
The Amazon RDS documentation blithely contains this statement: "When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions." But just how super is it? One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it's just an ordinary user. So if you're using more than one user in your RDS database, even if one or even all of them are rds_superusers, you're going to become very familiar with the GRANT command if (more…)

Managed Database Services – pros and cons

Andrew's PlanetPostgreSQL
Using a managed service is a very attractive proposition. You are offloading a heck of a lot of worry, especially when it comes to something as complicated and, let's face it, specialized as a database. Someone else will set it up for you, and back it up, and keep it running, without you having to worry too much about it. However, there are downsides. You can only get what the manager is offering. Often that's good enough. But needs change, and I have often seen people start with managed services, only to find that they want more than they can get that way. Just yesterday I received a complaint that the Redis Foreign Data Wrapper (which I have done a lot of work on) is not available on Amazon RDS. And that's completely understandable. Amazon only provides a limited number of extensions (more…)