Recent updates:
PostgreSQL Notes
Generating comma-separated files with psql
Japan PostgreSQL Conference 2014
PostgreSQL Gotchas - Postgres Gotchas
Newlines and implicit string constant concatenation
Articles about new features in PostgreSQL 9.4
BLOB/TEXT column ... can't have a default value
MySQL Gotchas
MySQL Gotchas

SQL Notes

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 12:44 PM

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)

PostgreSQL specialist wanted

While I'm at it, my employer is actively seeking a PostgreSQL specialist to assist with various database migration projects in the financial sector. Finance experience welcome but not necessary; reasonable competence in spoken/written Japanese preferred. Details here.

Posted at 5: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 10: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 contrib 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