SQL Notes

The long-awaited Firebird 4 release is drawing closer, so I thought it would be a nice idea to check it out and verify that it works with firebird_fdw (and possibly add support for new features). As part of that I thought I'd try and build Firebird from source, something I've never done before. The aim is to be able to build and start an ad-hoc instance running under my local system user on a custom port, to prevent any kind ofconflict with the existing Firebird 3.x installation, installed from standard packages.

With PostgreSQL this is fairly straightforward:

./configure --prefix=/home/wherever/builds/postgresql-13
make -s -j 4 && make install

then execute initdb, modify the configuration to use a non-default port and and start it up with pg_ctl.

With Firebird the process is somewhat less straightforward. The basic source installation process is documented here:

but this assumes an install on a system where another Firebird version is not already installed and running.

In particular, the normal "./configure && make install" process will end up launching an interactive install script, which refuses to run if it detects a running Firebird instance, and requires root permission to perform the install.

After some false starts and some assistance from the firebird-devel list I was able to get a purely local install running, with the following steps:

Posted at 8:57 AM

firebird_fdw is now available as version 1.1.0.

This release provides following new features:

  • support for PostgreSQL 12 (tested against PostgreSQL 12beta1)
  • following utility functions:
    • firebird_fdw_close_connections()
    • firebird_fdw_diag()

This release requires Firebird API wrapper libfq version 0.4.0 or later.

It is compatible with PostgreSQL versions 9.2 ~ 11, and PostgreSQL 12beta1.

CentOS rpm packages are available via the Fedora Copr build system.

Posted at 10:15 AM

firebird_fdw is now available as version 1.0.0.

This update provides following new features:

  • Supports the Firebird BOOLEAN datatype (Firebird 3.0 and later)
  • IMPORT FOREIGN SCHEMA implementation has been improved:
    • Firebird views can now also be imported (this can be controlled with the new option import_views)
    • verbose option will show which Firebird tables/views are being imported
    • updatable option can be used to control whether all imported tables/views are marked as updatable
    • import_null option determines whether column NOT NULL constraints are included in the definition of imported tables
  • New table option estimated_row_count added
  • Handling of foreign tables defined as Firebird queries has been improved
  • PostgreSQL 11 is supported
  • Code has been adapted to compile against current PostgreSQL HEAD
  • A bug has been fixed where a segfault could occur if not all expected Firebird connection parameters were provided

This release requires Firebird API wrapper libfq version 0.4.0 (this is a new libfq release with support for the Firebird 3 BOOLEAN datatype).

It is compatible with PostgreSQL versions 9.2 ~ 11.

CentOS rpm packages are available via the Fedora Copr build system.

NOTE currently no RPM packages are available for RHEL/CentOS 7 and PostgreSQL 11, due to issues encountered with using clang in the copr build system (this is related to the LLVM JIT functionality being enabled in the PGDG build). This is now fixed.

Posted at 6:59 PM

firebird_fdw is now available as version 0.5.0.

This update provides following new features:

  • Firebird datatype BLOB SUB_TYPE TEXT is now supported
  • Foreign table triggers are now supported.

This release requires Firebird API wrapper libfq version 0.3.0 (which has not been modified since the previous firebird_fdw release).

CentOS rpm packages are available via the Fedora Copr build system.

This release has been developed and tested with Firebird 2.5. It will work with Firebird 3.0, but does not yet take advantage of any new features; future releases will hopefully address this.

It is compatible with PostgreSQL versions 9.2 ~ 10, and should work with the upcoming version 11 release.

Posted at 9:37 AM

firebird_fdw is now available as version 0.4.0.

This update, together with an update of the underlying Firebird API wrapper libfq to version 0.3.0, is a general cleanup/bugfix release, with improvements including:

  • new function firebird_fdw_version()
  • bug in INSERT ... RETURNING ... fixed
  • miscellaneous minor bug fixes and code cleanup
  • TAP regression tests have been added

CentOS rpm packages are available via the Fedora Copr build system.

This release has been developed and tested with Firebird 2.5. It will work with Firebird 3.0, but does not yet take advantage of any new features; future releases will hopefully address this.

It is compatible with PostgreSQL versions 9.2 ~ 10, and should work with the upcoming version 11 release.

Posted at 11:41 AM

After a long hiatus, I've finally found time to update firebird_fdw, which is now available as version 0.3.0.

This update, together with an update of the underlying Firebird API wrapper libfq to version 0.2.0, is a general cleanup/bugfix release, with improvements including:

  • support for PostgreSQL 9.6 and 10
  • support for IMPORT FOREIGN SCHEMA (PostgreSQL 9.5 and later)
  • automatic reconnection to Firebird
  • improved error message handling and display
  • improved memory handling and prevention of various unhandled situations which caused the PostgreSQL server to crash

Additionally, CentOS rpm packages are now available via the Fedora Copr build system.

This release has been developed and tested with Firebird 2.5. It will work with Firebird 3.0, but does not yet take advantage of any new features; future releases will hopefully address this.

Posted at 8:46 PM

After upgrading to OX X 10.11 "El Capitan", attempting to connect to Firebird - which had always started on boot - failed with the message:

Statement failed, SQLSTATE = 08006
Unable to complete network request to host "localhost".
-Failed to establish a connection.

This is evidently to do with OS X StartupItems being deprecated for starting daemons; this blog provides a solution.

Posted at 11:23 PM

Japan has been an early and vigorous adopter of PostgreSQL (back in 2006, when PostgreSQL was still...

Posted at 10:48 PM

Let's assume you have a CSV file which looks like this:

    $ head -5 /tmp/prod.csv
    2,6,ACADEMY ACE,EWAN RICKMAN,20.99,0,6289
    4,3,ACADEMY AFFAIR,ALAN MARX,14.99,0,8042
Posted at 3:34 PM

Recently I needed to put together some simple design diagrams in a hurry and it occurred to me that pgAdmin's Graphical Query Builder would be just the thing. Admittedly its layout functionality is limited to creating boxes with tablename and columns, and drawing lines without fancy arrows between columns on different tables, but it's enough to sketch out basic entity relationships.

Posted at 11:44 AM

A recent thread on -general sparked my interest as I'm working with a large and heterogeneous code base recently converted from Oracle which makes heavy use of TO_DATE(), and it seems this function has a couple of gotchas worthy of consideration.

Posted at 5:28 AM

January 18th (Saturday):

3rd PostgreSQL Unconference@Tokyo

Time: 13:30 ~ 17:30
Location: Asushisuto Seminar Room, 4-2-1 Kudanshita-kita, Chiyoda-ku (東京都千代田区九段北4-2-1) (map)
Closest station: Ichigaya
Link: http://atnd.org/events/46187

Informal "unconference" event with no fixed agenda. Presentations mainly in Japanese, but English-speakers (and speakers in English) more than welcome. Unfortunately I won't be able to attend this time :(

February 1st (Saturday):

28th PostgreSQL Study Group

Time: 13:00 ~ 17:30
Location: AIIT Satellite Campus, 1-18-13 Soto-kanda, Chiyoda-ku (map)
Closest station: Akihabara
Link: http://www.postgresql.jp/wg/shikumi/shikumi28/view


  1. SAWADA Masahiko: Understanding the VACUUM source code
  2. HAYAMIZU Yuto: Performance Measurements Pt. II - Practical Session
  3. NAKANISHI Yoshinori: Monitoring PostgreSQL with Zabbix

(subject titles are my own translation)

Posted at 1:35 AM

Well I've finally fulfilled one of my 2013 New Year's resolutions, which was to write some kind of foreign data wrapper, so with no further ado here is the initial implementation. It is basically working but is still very much in the proof-of-concept/unstable/may-eat-your-data stage. Having said that, if anyone has a practical use for this I'd be very interested to hear about it - my main motivation for creating this FDW is to learn more about PostgreSQL internals.

Posted at 9:28 AM

PostgreSQL 9.3 RC1 was released the other day, and despite the dire warnings I couldn't resist putting it on this server to try out some of the new functionality in live operation. Admittedly it's not a real "production server" as having the database crash or mangle the data beyond repair would be merely an annoyance to myself and is nothing that can't be recovered from backups, so it's a good way of testing the new release. I've had good experiences with release candidates in the past, and probably the worst that could happen is the discovery of some issue requiring a bump of the catalog version number before final release, which means I'd have to upgrade from backups (which would probably mean whole minutes of downtime).

Disclaimer, esp. for any of my colleagues reading this wondering if I'm insane: there's no way I would ever do this with a genuine production installation.

Anyway, a full day's worth of log files shows no errors or other issues associated with the new release. This is particularly gratifying as there's now a (modest) custom background worker running which is giving me a warm tingly feeling and has got me thinking about ideas for new ones. I also feel a materialized view coming on, and I'm sure there's some way I could contrive a justification for using a writeable foreign data wrapper.

Thanks to everyone who has put so much effort into this release - I'm looking forward to the day when it can run in production for real.

Posted at 2:40 PM
1 comment  | 

A while back I posted some SQL which helps track of changes to the PostgreSQL settings file. I've found it useful when benchmarking tests with different settings, but unfortunately the pg_settings_log() function needs to be run manually after each setting change. However that sounds like something which a custom background worker (new in 9.3) could handle - basically all the putative background worker would need to do is execute the pg_settings_log() function whenever the server starts (or restarts) or receives SIGHUP.

This turned out to be surprisingly easy to implement. Based off the example test module and Michael Paquier's excellent posts, this is the code. Basically all it does is check for the presence of the required database objects (a function and a table) on startup, executes pg_settings_log() on startup, and adds a signal handler for SIGHUP which also calls pg_settings_log().

Posted at 8:36 PM

Instant PostgreSQL Starter

Having recently posted some thoughts on Shaun Thomas' ""PostgreSQL Backup and Restore How-to" review", Packt asked me if I'd like to review the new "Instant PostgreSQL Starter" by Daniel K. Lyons and kindly provided me with access to the ebook version. As I'm happily in a situation where I may need to introduce PostgreSQL to new users, I was interested in taking a look and here's a quick overview.

It follows the same "Instant" format as the backup booklet, which I quite like as it provides a useful way of focussing on particular aspects of PostgreSQL without being bogged down in reams of tl;dr documentation.  "Instant Pg Starter" is divided into three sections:

  • Installation
  • Quick start – creating your first table
  • Top 9 features you need to know about
Posted at 2:35 PM

PostgreSQL Backup and Restore How-to

Having worked with PostgreSQL continuously since 2001, I'd like to think there's nothing I don't know about backing up and restoring. However, experience shows that a) it's all too easy to develop "muscle memory" for a certain way of doing things, and b) PostgreSQL has a pesky habit of developing useful new features which fly under the radar if you're not paying sufficient attention to the release notes, so any opportunity to review things from a fresh perspective is a welcome one.

I ordered the paper version of "PostgreSQL Backup and Restore How-to" by Shaun Thomas from Amazon Japan for a tad under 2,000 yen, which is a little on the expensive side for what was described as a "booklet" (and four times the price of the eBook version), but I have a (meagre) book budget to burn and I have this old-fashioned habit of scribbling notes on margins and making little bookmarks from Post-It notes etc., also it's nice to spend some time not staring at a screen. To my surprise it arrived less than 48 hours after ordering - upon closer examination it turned out the book was actually printed by Amazon in Japan, which is kind of nifty.

First impression: it's a very thin volume - 42 actual content pages - so is genuinely a booklet. On the other hand, bearing in mind I've got a bookshelf full of largely unread weighty tomes, less can be more.

The booklet's table of contents, as lifted directly from the publisher's site, is:

  • Getting a basic export (Simple)
  • Partial database exports (Simple)
  • Restoring a database export (Simple)
  • Obtaining a binary backup (Simple)
  • Stepping into TAR backups (Intermediate)
  • Taking snapshots (Advanced)
  • Synchronizing backup servers (Intermediate)
  • Restoring a binary backup (Simple)
  • Point in time recovery (Intermediate)
  • Warm and hot standby restore (Intermediate)
  • Streaming replication (Advanced)
Posted at 12:01 PM

It's Sunday morning here in Japan, which in my case means it's an excellent time for a round of database server updates without interrupting production flow (lucky me). None of the databases in question are directly vulnerable to the recent security issue as for some crazy reason I prefer not to have port 5432 swinging in the Internet breeze for all and sundry to probe. However updates are updates, and the sooner applied the better - you never know what creative attack vectors all and sundry will dream up.

While I was updating, I was taking the opportunity to perform the odd bit of administrative TLC, which involves editing the postgresql.conf file, which involves manually checking in the changed version into source control, which is mildly onerous. Also, it's not convenient for tracking changes to individual configuration items over time. And it would be kind of handy to record the database settings in the database itself. Also, if it's possible to record exactly which configuration file the setting was taken from (a potential issue if the 'include' directive is used), it might be helpful when tracking down errors.

Anyway, it occurred to me that pg_settings stores displays information about which configuration items were set from values explicitly defined in postgresql.conf (and also notes which line in which file they were set on), so it should be possible to track changes on the basis of pg_settings' output:

Posted at 11:09 AM

The venerable SQL language has been in existence for a good four decades now, tracing its origins to an era where the punch card was still a viable input method and a computer was something you filled a room with, not put in your pocket. While SQL was a ground-breaking technology at the time and has served the data storage industry well during the intervening years, its heritage from the era of character-orientated terminals, line-feed printers and COMMANDS IN UPPER CASE is proving an increasing impediment in our modern world of cloud-hosted distributed global networks pushing social content to always-connected portable touchscreen devices.

I therefore propose that it is time for the PostgreSQL project to let SQL fade into a well-deserved retirement and get in on the ground floor to ride the coming NoSQL wave. This is a radical step, but it will not be the first time PostgreSQL has switched to a new core language, and I feel the PostgreSQL code base is in an excellent position to handle the transition especially once NoSQL evangelists have reached out to the core developers.

Details on mapping the reduced complexity provided by NoSQL are still being hashed out, but it's likely PostgreNoSQL's NoSQL functionality will coalesce around the HSTORE datatype, currently available as a contrib module but which will form a streamlined, distributed core implemented in Node.js and communicating exclusively via the JSON protocol (with 90's-style XML support being available for an interim period). The confusing plethora of index types will be removed except for the hash type, because that sounds cool. This will enable application developers to create their own index methods as required, as they will no longer be restricted by the fuddy-duddy "Daddy knows best" attitude inherent in legacy RDBMSs.

Elephant Shrew
The PostgreNoSQL mascot

Type checking and constraints will also be removed, further reducing complexity and the overhead they entail, while empowering application developers to manage their data in the way they see best. The venerable command-line orientated client application psql will be deprecated, to be replaced by a touch-screen compatible app available for both iOS and Android, while a legacy browser version curated in JavaScript with "magic pixels" in the corners of the screen will provide accessibility to old-fashioned users who have not yet converted away from the dated mouse-orientated paradigm.

Of course, many users will be wondering what will happen to the many applications and projects which are written with PostgreSQL's historical SQL capability in mind. It's not unreasonable to expect a transition period of as long as 18 months for existing application code to be ported, during which time the current PostgreSQL version will be maintained under the title "PostgreSQLegacy". Meanwhile the future branch of the project, known as "PostgreNoSQL", or "ReNo" for short, will be marketed with the confidence-inspiring slogan:

"What goes into ReNo stays in ReNo"

Posted at 12:22 PM

There have been a quite a few excellent articles / blog posts posted in the last few months previewing features in the upcoming PostgreSQL 9.3 release, which I've been collating as they scroll of the bottom of Planet PostgreSQL pretty quickly, and thought it might be useful to share the list. I'll continue updating the list with any new articles, also if I've missed any please let me know in the comments. (Note I haven't yet confirmed the current status of all the features listed).

Posted at 6:11 AM