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.

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.

Using PostgreSQL 9.5's IMPORT FOREIGN SCHEMA

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;
CREATE EXTENSION
wp=# CREATE SERVER mysql_server
         FOREIGN DATA WRAPPER mysql_fdw
         OPTIONS (host '127.0.0.1', port '3306');
CREATE SERVER
wp=# create user mapping for andrew server mysql_server;
CREATE USER MAPPING
wp=# import FOREIGN SCHEMA test from server mysql_server into public;
ERROR:  type "tinytext" does not exist
LINE 4:   comment_author tinytext NOT NULL,
                         ^
QUERY:  CREATE FOREIGN TABLE wp_comments (
  "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;
CREATE DOMAIN
wp=# import FOREIGN SCHEMA test from server mysql_server into public;
IMPORT FOREIGN SCHEMA
wp=# 

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.