tsurugi_fdw (PostgreSQL add-on)

Requirements

C++ Compiler >= C++17
Source code of PostgreSQL >=12.4
Access to installed dependent modules:
- managers (metadata-manager, message-manager)
- ogawayama

How to build for tsurugi_fdw

Install required packages. Install required packages for building tsurugi_fdw.
```
sudo apt -y install make gcc g++ git libboost-filesystem-dev
```
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.
```
curl -sL https://ftp.postgresql.org/pub/source/v12.4/postgresql-12.4.tar.bz2 | tar -xj
cd postgresql-12.4
./configure --prefix=$HOME/pgsql
make
make install
```
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 git@github.com:project-tsurugi/tsurugi_fdw.git
cd tsurugi_fdw
git submodule update --init --recursive
```
Build and Install dependent modules.
```
make install_dependencies
```
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_dependencies USE_PGXS=1
```
Dependent modules installed in <PostgreSQL install directory>.
Add <PostgreSQL install directory> to LIBRARY_PATH.
```
export LIBRARY_PATH=$LIBRARY_PATH:<PostgreSQL install directory>/lib
```
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
```

How to set up for tsurugi_fdw

Create PostgreSQL server.

mkdir <PostgreSQL install directory>/data
initdb -D <PostgreSQL install directory>/data

Update shared_preload_libraries parameter in postgresql.conf as below.
- postgresql.conf exists in <PostgreSQL install directory>/data/.
```
shared_preload_libraries = 'tsurugi_fdw'
```

Start PostgreSQL.

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

Define metadata tables and load initial metadata.

psql postgres < <tsurugi_fdw clone directory>/third_party/metadata-manager/sql/ddl.sql

Install tsurugi_fdw extension

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

Define external-server

Execute CREATE SERVER command

CREATE SERVER tsurugi FOREIGN DATA WRAPPER tsurugi_fdw;

Check with the meta-command(\des)

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

Define TABLESPACE

Create a directory for TABLESPACE.
- The following directory is an example of execution and can be created at any location.
```
mkdir -p <PostgreSQL install directory>/tsurugi
```

Execute CREATE TABLESPACE command

CREATE TABLESPACE tsurugi LOCATION '<PostgreSQL install directory>/tsurugi';

Check with the meta-command(\db)

postgres=# \db
            List of tablespaces
    Name    |  Owner   |             Location
------------+----------+-----------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 tsurugi    | postgres | /home/postgres/local/pgsql/tsurugi

Regression tests

Structure

expected/ test results expectations
results/ test results
sql/ all the tests

How to execute the tests

Start Tsurugi Server
- Refer to the Tsurugi documentation on how to start the Tsurugi server.
Build tsurugi_fdw
- How to build for tsurugi_fdw
Set up tsurugi_fdw
- How to set up for tsurugi_fdw
Execute the following command

in case when you run only basic tests
```
make tests
```
Or in case when run extra tests with basic tests,
execute the following command:
```
make tests REGRESS_EXTRA=1
```
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
```

Define table

Define table
- Execute CREATE TABLE command
  - You must add "TABLESPACE tsurugi"
  - You must specify PRIMARY KEY
```
CREATE TABLE table1 (column1 INTEGER NOT NULL PRIMARY KEY) TABLESPACE tsurugi;
```
Define foreign table
- Execute CREATE FOREIGN TABLE command
  - You must define same table name and schema as specified in CREATE TABLE
  - You must NOT specify PRIMARY KEY
  - You must specify same server name as specified in CREATE SERVER
```
CREATE FOREIGN TABLE table1 (column1 INTEGER NOT NULL) SERVER tsurugi;
```

Execute DML

SELECT * FROM table1;
INSERT INTO table1 (column1) VALUES (100);

project-tsurugi/tsurugi_fdw