Monday, January 25, 2016

Fixed length record files

I've had a few requests recently for support for the Fixed Length File FDW that I created as an experiment about 5 years ago. Apparently people are still getting data from COBOL programs or some such source. The code has bitrotted some in the meanwhile, so I'll try to grab a little time to update it, add some test cases and docco, etc.

Tuesday, January 19, 2016

Buildfarm server moving

Today we're moving the buildfarm server to a new machine and more modern Postgres. Here is yesterday's (corrrected) announcement:

Apologies for the late notice.

Tomorrow, January 19th, at 4.00 pm US East Coast time (UT - 5.0) we will be moving the buildfarm server from its current home at CommandPrompt, where we have been ever since we started, to a machine that is part of the standard core infrastructure. In doing so we will be moving to a) a more modern and supported PostgreSQL version, and b) a machine with more disk space so that our current severe pace shortage will be alleviated. In addition, the community would be much better placed to maintain the buildfarm if either JD or I were to fall under a bus.

The outage is expected to last about 4 hours or less, and we will sent out notifications when this is complete.

Buildfarm owners who want to avoid getting reporting failures should disable their animals during that time. We don't have an avalanche of commits right now either, but it might also be nice if committers were to refrain from adding changes in the hours leading up to this and until we announce that we're back online, for the benefit of those owners who don't see this message in time.

Thanks in advance for your help and understanding.

And many thanks to CommandPrompt for their constant support over the many years we've been in operation.

In a few hours I will start disabling my 12 buildfarm members.

Friday, January 15, 2016


This is a pretty nice feature in 9.5. The other day I needed to look at some data from a WordPress site that's stored in MySQL. I had a dump of the data, but I'm not terribly proficient at using MySQL tools or their SQL dialect, so I thought I would try out just importing the schema to PostgreSQL using the new IMPORT FOREIGN SCHEMA command. It worked pretty well. First I installed the Postgres rpms from the community repository. Unfortunately, the repository doesn't have the latest mysql foreign data wrapper for 9.5, so I cloned it from github and built and installed it without difficulties. Then I restored the backup into the test database on local mysql instance. Then there was a small glitch. When I tried to import the schema it complained that it didn't know about the type "tinytext". So I created a domain for this that just mapped it to text. and then re-ran the import, and it worked just fine. Here is the whole import session:
[andrew@dino mysql_fdw]$ psql wp
psql (9.5.0)
Type "help" for help.

wp=# create extension mysql_fdw;
wp=# CREATE SERVER mysql_server
         FOREIGN DATA WRAPPER mysql_fdw
         OPTIONS (host '', port '3306');
wp=# create user mapping for andrew server mysql_server;
wp=# import FOREIGN SCHEMA test from server mysql_server into public;
ERROR:  type "tinytext" does not exist
LINE 4:   comment_author tinytext NOT NULL,
  "comment_ID" bigint NOT NULL,
  "comment_post_ID" bigint NOT NULL,
  comment_author tinytext NOT NULL,
  comment_author_email varchar NOT NULL,
  comment_author_url varchar NOT NULL,
  "comment_author_IP" varchar NOT NULL,
  comment_date timestamp NOT NULL,
  comment_date_gmt timestamp NOT NULL,
  comment_content text NOT NULL,
  comment_karma int NOT NULL,
  comment_approved varchar NOT NULL,
  comment_agent varchar NOT NULL,
  comment_type varchar NOT NULL,
  comment_parent bigint NOT NULL,
  user_id bigint NOT NULL
) SERVER mysql_server OPTIONS (dbname 'test', table_name 'wp_comments');

CONTEXT:  importing foreign table "wp_comments"
wp=# create domain tinytext as text;
wp=# import FOREIGN SCHEMA test from server mysql_server into public;

Thursday, January 7, 2016

PostgreSQL 9.5 fills in lots of gaps

The two big features from my point of view in the long-awaited PostgreSQL 9.5, officially released today, are the INSERT ... ON CONFLICT DO NOTHING / UPDATE feature, and addition of CUBE, ROLLUP and more generalized GROUPING SETS. These are two very important developments that go a long way to filling in the gaps in our feature set.

My contribution has been a bit more more modest than previously for this release. Here are the things I was involved with, along with others:
  • Add jsonb functions jsonb_set() and jsonb_pretty()
  • Allow text, text array, and integer values to be subtracted from jsonb documents
  • Add jsonb || operator
  • Add json_strip_nulls() and jsonb_strip_nulls() functions to remove JSON null values from documents
  • Add jsonb generator functions to_jsonb(), jsonb_object(), jsonb_build_object(), jsonb_build_array(), jsonb_agg(), and jsonb_object_agg()
  • Add \pset option pager_min_lines to control pager invocation in psql
  • Improve psql's line counting used when deciding to invoke the pager
  • Make psql's \sf and \ef commands honor ECHO_HIDDEN
  • Add statistics for minimum, maximum, mean, and standard deviation times to pg_stat_statements
Original work for the first three of these was done by Dmitry Dolgov, and for the last by Mitsumasa Kondo.

This is the fourth straight release where I've done a substantial amount of work on JSON features, and it will be the last for a while at least.  We've come a long way since Robert Haas and I snuck some JSON stuff into release 9.2 at the very last minute. Despite a somewhat rocky road, I venture to say that if we hadn't done that we would not have made as much progress as we have since then.

Wednesday, December 23, 2015

json functions for dynamic field modification in plpgsql, including in triggers.

One of the occasionally annoying things about Plpgsql is that it's very static, and any sort of dynamic access to records, say by using a trigger argument, is difficult. I just conducted a little experiment that seems to make life a bit easier. The json_populate_record() function returns a record of the same type as its first argument. Mostly we just use NULL::record_type here, but it turns out that in a trigger function it works perfectly happily with NEW and OLD, and they can be modified using any sort of dynamic values you like, including values from TG_ARGV

Here's a simple example:

mydb=# create type rainbow as enum('r','o','y','g','b','i','v');
mydb=# create table foo (a text, b int, c boolean, d rainbow);
mydb=# create or replace function foo_tr()
returns trigger
language plpgsql as $$
  if tg_op = 'INSERT' or tg_op = 'UPDATE'
    return json_populate_record(NEW,json_object(TG_ARGV));
    return old;
  end if;
mydb=# create trigger foo_tri before insert on foo for each row execute procedure foo_tr(d,i);
mydb=# create trigger foo_tru before update on foo for each row execute procedure foo_tr(c,true);
mydb=# insert into foo values ('x',1,null,'r') returning *;
 a | b | c | d 
 x | 1 |   | i
(1 row)

mydb=# update foo set c = null, d = 'o' returning *;
 a | b | c | d 
 x | 1 | t | o
(1 row)


Tuesday, December 22, 2015

Numeric scale and precision

From the quick tip file:

I have lost track of the number of times I have seen people get this wrong. The precision of numeric doesn't specify the number of digits allowed before the decimal point, it specifies the total number of digits before and after the decimal point. So the number allowed before the decimal point is the precision minus the scale. And that means that if you want to increase the scale for finer grained numbers without reducing the range of numbers allowed you need to increase both the precision and the scale.

Let's say you want to have money amounts of US dollars with no fractional cents and up to single trillions of dollars. You would use numeric(15,2). That allows thirteen digits before the decimal and two after. Now if you want to change that to allow fractional cents in hundredths, you will need to use numeric(17,4), which still allows thirteen digits before the decimal but now allows four after.

Saturday, December 12, 2015

psql -c will no longer imply --no-psqlrc

Recently some very good changes were made that allow you to have multiple -c and -f arguments to psql. This is going to be quite useful. One of the things this changed as a part of this is that -c no longer implies --no-psqlrc. I found this out the hard way when it proved to be that cause of unpleasant failures by one of my buildfarm animals. This animal runs on my normal Unix workstation, where I have a .psqlrc doing various things, and it also runs a non-standard module that calls "psql -c". Until now there was no issue, because -c meant that my .psqlrc was not processed. The solution was to as "--no-psqlrc" to those psql calls. But I suspect this might catch quite a few people once 9.6 is released.

Saturday, November 21, 2015

Announcing Release 4.16 of the PostgreSQL Buildfarm client

I have just released version 4.16 of the PostgreSQL Buildfarm client

It can be downloaded from

Several bugs have been fixed, and there are these non-trivial changes:

  • capturing of TAP test logs
  • bin check enabled on Windows
  • rm_worktrees feature
  • an experimental module to run Sepgsql tests
  • try database shutdown following a start failure
  • report all PGBuild module versions to server

rm_worktrees is enabled by putting this in your config file:

rm_worktrees => 1,

The effect is that at the end of a run the checked out work tree is removed, leaving just the git repository, which in turn might be mostly linked to your HEAD branch if you use the git_use_workdirs option. Before a run, the work tree will be checked out again. The net effect is a significant saving in space used. with these two options, the additional space for each branch except when it's actually building is reduced to less than 30Mb. On crake, the git mirror, branch trees and cache now come in at about 1.5Gb. That's a lot less than it used to be. The additional cost of checking out the worktree each time is very modest.

Shutdown after a start failure tries to remedy a situation where we try to start the server, and don't detect that it has started, but it has in fact started. So now if we get a failure we try to shut down any server that might have started, just in case. This change is possibly redundant given the recent change where postgres detects that its data directory has disappeared and shuts down when it has, but it's probably worth having anyway.

Saturday, November 14, 2015

A record with all defaults

Fresh from yesterday's help file:

Say you want to insert a record into your table with all the default values set. Later on you'll update some of the values. You might want to do this so you can get the row's ID field. So you try this:
insert into mytable values () returning my_id;
but you find it doesn't work. VALUES isn't allowed to have an empty list. Here's what you do:
insert into mytable values (default) returning my_id;
This will insert the default value explicitly into first column in the table, and implicitly into all the other columns. It doesn't matter if any of the columns doesn't have an explicit default value set - in that case the default is NULL. Of course, that means this won't work if you have NOT NULL columns without a default set.

Sunday, November 8, 2015

Latest adventures with a buildfarm animal on Raspberry Pi 2

My buildfarm member axolotl, that runs (or ran) on a Raspberry Pi 2 running a Fedora 21 remix, died the other day, apparently with a corrupted file system.

While I'm trying to revive it, I thought I would try running the stock Fedora spin built for ARM, instead of a remix. This is pretty easy to do using the script found here. It looks like this is a bad idea, though. It's running so slowly just doing a "dnf update" that I wouldn't think of running a buildfarm member on it.

So, if I can't resuscitate the file system on the other SD card, I'll try a respin with a custom kernel again. There is someone maintaining a set here, which you can read about on the Raspberry Pi forums.


fsck on the root partition managed to resuscitate it without apparent problems, So in the end I haven't had to jump thought more hoops.

Tuesday, November 3, 2015

Keeping config files under version control - reprise

I recently blogged about the virtue of putting PostgreSQL config files under version control. My attention was drawn to etckeeper, and a day or so ago I started playing with it, and it seems pretty nice. If you keep your configs in /etc like Debian/Ubuntu, then just deploying etckeeper is probably all you need to do. But if you are using Fedora, Redhat, Centos, or anything where the config files aren't in /etc, it's a little bit harder. But not too much. I found a nice little script at serverfault that you can put in etckeeper's commit.d directory that seems to do the trick and happily pulls files outside /etc into the repository. This seems like quite a good solution.

Friday, October 23, 2015

A little permissions problem

So you're on Fedora 22 and you want to run the latest stable PostgreSQL release. You install the PGDG repository package and then install postgresql94-server. You decide to test it out first, so as your normal login user you run initdb and then try to start the server using pg_ctl, something you've done a hundred times before. It should "just work," right? Not this time. You examine the log file and you see it complaining like this:
could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied
Ouch! I've never needed that before. These files have always gone in /tmp. OK, so why is it different now we are putting them elsewhere? It turns out that the directory has been created by the RPM with permissions different from /tmp, and only the postgres user can write there.

My solution (which I hope our RPM maintainers adopt) was to change the permissions on the directory like this, making them the same as those of /tmp:
sudo chmod 1777 /var/run/postgresql
After that when I try to start the database everything is as expected.