sql-info.de

SQL Notes

If you have a CentOS or Fedora system with Oracle installed, and the system doesn't come up after a reboot (in my case it just hung with nothing obvious happening), it's highly likely you've added $ORACLE_HOME/lib to /etc/ld.so.conf (or preferably as a separate file under /etc/ld.so.conf.d/), which means Oracle's version of the libexpat library is prioritized, but this is lacking the XML_SetHashSalt symbol required by dbus, meaning the whole systemd caboodle just falls over and dies.

The precise error (from /var/log/messages) was:

dbus-daemon: /usr/bin/dbus-daemon: symbol lookup error: /usr/bin/dbus-daemon: undefined symbol: XML_SetHashSalt

The solution is simply to remove the entry from /etc/ld.so.conf or wherever and run ldconfig before rebooting the system.

Provision of the Oracle library path will then have be done at individual user level with e.g.

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

(yes, we know it's considered harmful).

Hat-tip to Laurenz Albe (of oracle_fdw fame) in this Stackoverflow question: Fedora Linux cannot boot after installing Oracle XE 18.4

Posted at 7:58 PM

Beginning with release 1.2.1, RPM packages for firebird_fdw are available via the PostgreSQL community YUM repository. This increases the number of distributions/distribution versions/architectures  for which firebird_fdw is available (particulary Fedora and Centos 8) and makes it easy to install firebird_fdw alongside other PostgreSQL packages.

Currently packages are available for:

  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10

Packages for PostgreSQL 9.5 and PostgreSQL 9.6 remain available via the existing copr repository (albeit for CentOS 6 and CentOS 7 only).

My thanks to Devrim Gündüz for setting this up.

Posted at 3:53 AM

There have been a lot of articles about new features in the recent PostgreSQL 13 release, but one set of improvements which seems to have escaped much attention is in pg_rewind, which has gained some additional useful functionality deserving of closer examination.

New option "-R /--write-recovery-conf"

As the name implies, this option causes pg_rewind to write recovery (replication) configuration (and additionally the "standby.signal" file) after rewinding a server, in exactly the same way that pg_basebackup does. This is useful for example when restoring a former primary to become a standby. Note that it can only be used in conjunction with the option --source-server, and (unless the --dry-run option is supplied) that the configuration will be written regardless of whether a rewind is needed.

As replication configuration via the recovery.conf file was removed in PostgreSQL 12, the generated replication configuration is actually appended to postgresql.auto.conf, which is the one configuration file which is guaranteed to be in a known location (the data directory) and which will be read last when PostgreSQL starts up, ensuring any prior replication configuration is overridden by whatever pg_rewind has written.

Currently the only replication configuration item actually written is primary_conninfo - be aware that this is generated from a blend of the connection information passed in --source-server and the default parameters generated by libpq, which may be different to the primary_conninfo string you would normally generate and will look something like this:

primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=node1 port=5432
sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

Note that currently replication slot configuration (primary_slot_name) cannot be generated by pg_rewind.

Also be aware that if attempting to rewind a standby which was not shut down properly, the standby.signal file must be removed otherwise pg_rewind will attempt to start the server in single user mode to run recovery, which cannot be done with replication configuration in place. See also the section "Automatic crash recovery" below.

This option was added in commit 927474ce

Posted at 10:31 AM

firebird_fdw is now available as version 1.2.0.

This release provides following new features:

  • support for PostgreSQL 13
  • support for generated columns (PostgreSQL 12 and later)
  • support for COPY and partition tuple routing (PostgreSQL 11 and later)
  • improved handling of case folding/identifier quoting between PostgreSQL and Firebird
  • support for "implicit booleans"
  • pushdown of boolean tests in WHERE clauses (including implicit booleans)
  • following utility functions have been added:
    • firebird_fdw_server_options(servername TEXT) - displays the current settings for the named server
    • firebird_version() - shows Firebird version information for each firebird_fdw foreign server in the current database

Following bugs have been fixed:

  • EXPLAIN for a remote modify now shows the Firebird query used
  • UPDATE statements where a BEFORE ROW UPDATE trigger is present now work correctly
  • CREATE SERVER's "port" option is now recognized
  • foreign tables defined as queries can no longer be set as "updatable = 'true'"

This release requires Firebird API wrapper libfq version 0.4.0 or later (latest version at the time of writing: 0.4.2)

firebird_fdw 1.2.0 is compatible with PostgreSQL versions 9.3 ~ 13. Note that support for PostgreSQL 9.2 has been removed in this release.

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

Posted at 5:52 AM

October 17, 2020

libfq 0.4.2 has been released with following changes:

  • configure option --with-fbclient added
  • output of FQexplainStatement() fixed

This is a maintenance release which does not introduce any new features.

Source code is available at the libfq GitHub page.

RPM packages are available via the libfq copr repository.

Online libfq documentation is available here: https://libfq.sql-info.de/.

Posted at 2:37 AM

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
3 comments  | 

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
    1,14,ACADEMY ACADEMY,PENELOPE GUINESS,25.99,0,1976
    2,6,ACADEMY ACE,EWAN RICKMAN,20.99,0,6289
    3,6,ACADEMY ADAPTATION,VIVIEN KAHN,28.99,0,7173
    4,3,ACADEMY AFFAIR,ALAN MARX,14.99,0,8042
    5,3,ACADEMY AFRICAN,CARRIE HANNAH,11.99,1,2183
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

Speakers:

  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 8: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