Foreign Data Wrapper for Tsurugi

tsurugi_fdw is a PostgreSQL extension that provides a Foreign Data Wrapper for access to Tsurugi.

Notice

tsurugi_fdw specializes in accessing Tsurugi database, so in the PostgreSQL database using this extension, only foreign tables for accessing Tsurugi database can be used. Local tables in PostgreSQL cannot be used.

The current version of tsurugi_fdw pushes down queries directly to Tsurugi, which may result in execution failure for queries containing PostgreSQL-specific syntax.

Installation

Requirements

Since tsurugi_fdw accesses the Tsurugi database via IPC endpoint, the PostgreSQL installing this extension must be located on the same host as Tsurugi.

• C++ Compiler >= C++17
• Source code of PostgreSQL 14/15/16/17 >=14.18, >=15.13, >=16.10, >=17.8
• Access to installed dependent modules:
  • ogawayama

How to build for tsurugi_fdw

1. Install required packages.

   Install required packages for building tsurugi_fdw. If you already know that the required packages are installed, skip this procedure.

   sudo apt -y install make gcc g++ git libboost-filesystem-dev

2. Build and Install PostgreSQL.

   tsurugi_fdw uses the PostgreSQL build environment.
   If you already know that the environment is set up, skip this procedure.

   • Specify the PostgreSQL install directory to "--prefix". In the following example, $HOME/pgsql is specified.

   • From now on, this directory is defined as <PostgreSQL install directory>.

   • Refer to the PostgreSQL documentation or online manuals for the installation of PostgreSQL.

     sudo apt -y install curl bzip2 libreadline-dev libz-dev

   curl -sL https://ftp.postgresql.org/pub/source/v17.8/postgresql-17.8.tar.bz2 | tar -xj
   cd postgresql-17.8
   ./configure --prefix=$HOME/pgsql
   make
   make install

3. Clone tsurugi_fdw.

   Clone tsurugi_fdw to contrib directory in PostgreSQL.

   • From now on, this directory is defined as <tsurugi_fdw clone directory>.

   cd contrib
   git clone https://github.com/project-tsurugi/tsurugi_fdw.git
   cd tsurugi_fdw
   git submodule update --init --recursive

4. Install libraries required to build dependent modules.

   # Common dependency library for each dependent module.
   sudo apt -y install build-essential cmake ninja-build
   # Dependency libraries for takarori.
   sudo apt -y install libboost-container-dev libboost-stacktrace-dev libicu-dev flex bison
   # Dependency libraries for ogawayama(stub).
   sudo apt -y install libboost-thread-dev libgoogle-glog-dev libprotobuf-dev protobuf-compiler

   For libraries required, refer to README of each dependent module.

5. Build and Install dependent modules.

   make install_deps

   If tsurugi_fdw was cloned into a directory other than the "contrib" directory in PostgreSQL, add a directory of pg_config to PATH and use "USE_PGXS=1".

   make install_deps USE_PGXS=1

   Dependent modules installed in <PostgreSQL install directory>.

6. Add <PostgreSQL install directory> to LIBRARY_PATH.

   export LIBRARY_PATH=$LIBRARY_PATH:<PostgreSQL install directory>/lib

7. Build and Install tsurugi_fdw.

   make
   make install

   If tsurugi_fdw was cloned into a directory other than the "contrib" directory in PostgreSQL, add a directory of pg_config to PATH and use "USE_PGXS=1".

   make USE_PGXS=1
   make install USE_PGXS=1

Usage

1. Update shared_preload_libraries parameter in postgresql.conf as below.

   • postgresql.conf exists in <PostgreSQL install directory>/data/.

   shared_preload_libraries = 'tsurugi_fdw'
   

2. Restart PostgreSQL.

   pg_ctl -D <PostgreSQL install directory>/data/ restart

3. Install tsurugi_fdw extension

   From here, enter commands in psql.

   • Execute CREATE EXTENSION command

     CREATE EXTENSION tsurugi_fdw;

   • Check with the meta-command(\dew)

     postgres=# \dew
                     List of foreign-data wrappers
             Name      |  Owner   |        Handler        | Validator
        ---------------+----------+-----------------------+-----------
         tsurugi_fdw   | postgres | tsurugi_fdw_handler   | -

4. Create foreign server for Tsurugi

   • Execute CREATE SERVER command

     CREATE SERVER tsurugidb FOREIGN DATA WRAPPER tsurugi_fdw;

     IMPORTANT If you have changed the name of the Tsurugi database from its default (default is 'tsurugi'), you need to set the new database name to PostgreSQL as well.

     CREATE SERVER tsurugidb FOREIGN DATA WRAPPER tsurugi_fdw OPTIONS (dbname 'new-database-name');

     Check with the meta-command(\des+).

     postgres=# \des+
                                                       List of foreign servers
        Name    |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |       FDW options            | Description
     -----------+----------+----------------------+-------------------+------+---------+------------------------------+-------------
      tsurugidb | postgres | tsurugi_fdw          |                   |      |         | (dbname 'new-database-name') |

   • Check with the meta-command(\des)

     postgres=# \des
                 List of foreign servers
         Name    |  Owner   | Foreign-data wrapper
      -----------+----------+----------------------
       tsurugidb | postgres | tsurugi_fdw

5. Create user mapping option

   You can define user mapping between PostgreSQL user and Tsurugi user using CREATE USER MAPPING command:

   CREATE USER MAPPING FOR pguser
           SERVER tsurugidb
           OPTIONS (user 'tsurugi-user', password 'tsurugi-password');    

   • Check with the meta-command(\deu+)

     postgres=# \deu+
                                 List of user mappings
     Server   | User name |                     FDW options
     -----------+-----------+------------------------------------------------------
     tsurugidb | pguser    | ("user" 'tsurugi-user', password 'tsurugi-password')
     (1 row)

6. Create foreign tables

   CREATE FOREIGN TABLE tg_table (... columns ... ) SERVER tsurugidb;

   You can also import the tables of a specific schema in Tsurugi database.

   IMPORT FOREIGN SCHEMA public FROM SERVER tsurugidb INTO public;

   CAUTION If you plan to execute UPDATE or DELETE via the foreign table, specify the column(s) that uniquely identify a row (typically the PRIMARY KEY) using the key option.

   If it’s not specified correctly, wrong results may occur when executing UPDATE or DELETE.

   Mark the column(s) that uniquely identify a row (for a composite key, mark all key columns) with OPTIONS (key 'true') in the foreign table definition.

   CREATE FOREIGN TABLE tg_table(
   column1 integer OPTIONS (key 'true'),
   column2 text
   )
   SERVER tsurugidb;

   If you want to add key later, use ALTER FOREIGN TABLE (note that IMPORT FOREIGN SCHEMA does not set the key option automatically):

   ALTER FOREIGN TABLE tg_table
   ALTER COLUMN column1 OPTIONS (ADD key 'true');

7. Execute DML using foreign tables.

   SELECT * FROM tg_table;

Regression tests

1. Start up tsurugidb

   • Refer to the Tsurugi documentation on how to start the Tsurugi server.

2. Execute the following command

   in case when you run only basic tests

   make tests

   If tsurugi_fdw was cloned into a directory other than the "contrib" directory in PostgreSQL, add a directory of pg_config to PATH and use "USE_PGXS=1".

   make tests USE_PGXS=1