Home

Awesome

GridDB Foreign Data Wrapper for PostgreSQL

This is a foreign data wrapper (FDW) to connect PostgreSQL to GridDB. This FDW works with PostgreSQL 12, 13, 14, 15, 16 and confirmed with GridDB 5.1.0.

<img src="https://upload.wikimedia.org/wikipedia/commons/2/29/Postgresql_elephant.svg" align="center" height="100" alt="PostgreSQL"/> + <img src="https://docs.griddb.net/logo.png" align="center" height="100" alt="GridDB"/>

Contents

  1. Features
  2. Supported platforms
  3. Installation
  4. Usage
  5. Functions
  6. Identifier case handling
  7. Generated columns
  8. Character set handling
  9. Examples
  10. Limitations
  11. Tests
  12. Contributing
  13. Useful links
  14. License

Features

Common features

For PostgreSQL version 14 or later:

Pushdowning

Notes about features

Also see Limitations.

Supported platforms

griddb_fdw was developed on Linux, and should run on any reasonably POSIX-compliant system.

Installation

Source installation

Prerequisites:

GridDB's C client library. This library can be downloaded from the GridDB website on github.

  1. Download GridDB's C client and unpack it into griddb_fdw directory as griddb.

    Build GridDB's C client

    • gridstore.h should be in griddb_fdw/griddb/client/c/include.
    • libgridstore.so should be in griddb/bin.
  2. Build and install griddb_fdw

    Change into the griddb_fdw source directory.

<pre> $ make $ make install </pre>

If you want to build griddb_fdw in a source tree of PostgreSQL, use

<pre> $ make NO_PGXS=1 </pre>

Usage

CREATE SERVER options

griddb_fdw accepts the following options via the CREATE SERVER command:

CREATE USER MAPPING options

griddb_fdw accepts the following options via the CREATE USER MAPPING command:

CREATE FOREIGN TABLE options

griddb_fdw accepts the following table-level options via the CREATE FOREIGN TABLE command.

The following column-level options are available:

IMPORT FOREIGN SCHEMA options

griddb_fdw supports IMPORT FOREIGN SCHEMA (when running with PostgreSQL 9.5 or later) and accepts the following custom options:

TRUNCATE support

griddb_fdw yet don't support the foreign data wrapper TRUNCATE API, available from PostgreSQL 14.

Functions

As well as the standard griddb_fdw_handler() and griddb_fdw_validator() functions, griddb_fdw provides the following user-callable utility functions:

Functions from this FDW in PostgreSQL catalog are yet not described.

griddb_get_connection()

griddb_disconnect('server_name')

griddb_disconnect_all()

Identifier case handling

PostgreSQL folds identifiers to lower case by default. Rules and problems with GridDB identifiers yet not tested and described.

Generated columns

GridDB does not support generated column, so data will be generated at FDW layer. griddb_fdw supports generated columns in foreign tables based on output of GridDB. Example:

	CREATE FOREIGN TABLE grem1 (
  	  id serial OPTIONS (rowkey 'true'),
	  a int,
	  b int GENERATED ALWAYS as (a * 2) STORED
	)
	SERVER griddb_svr 
	OPTIONS(
	  table_name 'gloc1'
	);

For more details on generated columns see:

Character set handling

Yet not described.

Examples

Install the extension:

Once for a database you need, as PostgreSQL superuser.

	CREATE EXTENSION griddb_fdw;

Create a foreign server with appropriate configuration:

Once for a foreign datasource you need, as PostgreSQL superuser.

	CREATE SERVER griddb_svr
	FOREIGN DATA WRAPPER griddb_fdw
	OPTIONS(
	  host '239.0.0.1',
	  port '31999',
	  clustername 'ktymCluster',
	  database 'public'
	);

Grant usage on foreign server to normal user in PostgreSQL:

Once for a normal user (non-superuser) in PostgreSQL, as PostgreSQL superuser. It is a good idea to use a superuser only where really necessary, so let's allow a normal user to use the foreign server (this is not required for the example to work, but it's security recomedation).

	GRANT USAGE ON FOREIGN SERVER sqlite_server TO pguser;

Where pguser is a sample user for works with foreign server (and foreign tables).

User mapping

Create an appropriate user mapping:

    	CREATE USER MAPPING
	FOR pguser
	SERVER griddb_svr 
    	OPTIONS (
	  username 'username',
	  password 'password'
	);

Where pguser is a sample user for works with foreign server (and foreign tables).

Create foreign table

All CREATE FOREIGN TABLE SQL commands can be executed as a normal PostgreSQL user if there were correct GRANT USAGE ON FOREIGN SERVER. No need PostgreSQL supersuer for security reasons but also works with PostgreSQL supersuer.

Create a foreign table referencing the griddb table fdw_test:

	CREATE FOREIGN TABLE ft1 (
	  c1 text,
	  c2 float,
	  c3 integer
	)
	SERVER griddb_svr;

Query the foreign table.

	SELECT * FROM ft1;

The container must have rowkey on GridDB in order to execute update and delete query.

Import a GridDB schema:

	IMPORT FOREIGN SCHEMA public
	FROM SERVER griddb_svr
	INTO public
	OPTIONS (
	  recreate 'true'
	);

After schema is imported, we can access tables. To use CREATE FOREIGN TABLE is not recommended.

Limitations

SQL commands

INSERT INTO ft1 VALUES(100, 'AAA');
INSERT INTO ft1 VALUES(100, 'BBB'); -- Same as "UPDATE ft1 SET b = 'BBB' WHERE a = 100;"
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING;
INSERT INTO ft1 (c0, c1) VALUES (1, 2) RETURNING c0, c1;

Other

Limitations related in rowkey-column attribute.

GridDB can set a rowkey attribute to the 1st column. griddb_fdw uses it for identifying a record.

Don't support the query execution which is satisfied with all of following conditions:

If such query is executed, it is no response.

Example1: Foreign table ft1 and ft2 are linked to same container in GridDB.

BEGIN;
SELECT * FROM ft1 FOR UPDATE;
SELECT * FROM ft2 FOR UPDATE; -- No response

Example2:

BEGIN;
SELECT * FROM ft1, ft2 WHERE ft1.a = ft2.a FOR UPDATE; -- No response

This is because GridDB manages a transaction by container unit and griddb_fdw creates GSContainer instances for each foreign tables even if the container is same in GridDB.

Don't support an arbitrary column mapping.

griddb_fdw is assumed that a column structure on PostgreSQL is same as that of griddb. It is recommended to create a schema on PostgreSQL by IMPORT FOREIGN SCHEMA. griddb_fdw might return an error when DML is executed.

For example, container on GridDB has 3 columns. The 1st column is "c1" as integer, the 2nd is "c2" as float, and the 3rd is "c3" as text. Schema must be created as

	CREATE FOREIGN TABLE ft1 (
	  c1 integer,
	  c2 float,
	  c3 text 
	  )
	SERVER griddb_svr;

You should not execute following queries.

Types are not match.

	CREATE FOREIGN TABLE ft1 (
	  c1 text,
	  c2 float,
	  c3 integer
	  )
	SERVER griddb_svr;

There is unknown column. Even if unknown column is dropped, GridDB cannot access ft1 correctly.

	CREATE FOREIGN TABLE ft1 (
	  c0 integer,
	  c1 integer,
	  c2 float,
	  c3 text
	  )
	SERVER griddb_svr;
	
	ALTER FOREIGN TABLE ft1 DROP COLUMN c0;

Tests

Tests for group of PostgreSQL versions are provided. There is no testing for different GridDB versions. See test.sh and expected.

Contributing

Opening issues and pull requests on GitHub are welcome.

Useful links

Source code

Reference FDW realisation, postgres_fdw

General FDW Documentation

Other FDWs

License

Copyright (c) 2018, TOSHIBA CORPORATION
Copyright (c) 2011-2016, EnterpriseDB Corporation

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

See the LICENSE file for full details.