Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Foreign tables

Anton Monakov

Greengage DB partially implements the SQL/MED specification, allowing you to access the data residing outside the databases by using regular SQL queries. Such data is referred to as foreign or external data. You can access foreign data with a foreign data wrapper (FDW) — a library that communicates with a remote data source. This library hides the source-specific connection and data access details.

Accessing foreign data involves the following general steps:

  1. Create a foreign data wrapper object using the CREATE FOREIGN DATA WRAPPER command. If an FDW is supplied as a Greengage DB extension (such as postgres_fdw), use the CREATE EXTENSION command.

  2. Create a foreign server object using the CREATE SERVER command to represent each remote database you want to connect to.

  3. If accessing remote data requires authenticating to the data source, create a user mapping with the CREATE USER MAPPING command for each database user you want to allow access to each foreign server.

  4. Create a foreign table using the CREATE FOREIGN TABLE command for each remote table you want to access.

  5. Work with the created foreign table as you would do with a regular table, for example, use SELECT to access the data stored in its underlying remote table or modify the remote table using INSERT, UPDATE, or DELETE. Whenever a foreign table is accessed, Greengage DB uses the foreign data wrapper to fetch or update the data in the remote source.

NOTE

GPORCA does not support foreign tables. Querying a foreign table always falls back to the Postgres planner.

Create a foreign data wrapper

The CREATE FOREIGN DATA WRAPPER command creates a new foreign data wrapper in the current database. The user who defines the foreign data wrapper becomes its owner. Only superusers can create foreign data wrappers. See Command reference for the complete description of command’s clauses and parameters.

The command general syntax is as follows:

CREATE FOREIGN DATA WRAPPER <name>
    [ HANDLER <handler_function> | NO HANDLER ]
    [ VALIDATOR <validator_function> | NO VALIDATOR ]
    [ OPTIONS (
        [ mpp_execute { 'master' | 'any' | 'all segments' } [, ] ]
        <option> '<value>' [, ... ]
    ) ]

Below are several command usage examples:

  • Create a foreign data wrapper named example_wrapper:

    CREATE FOREIGN DATA WRAPPER example_wrapper;

  • Create a foreign data wrapper named file with a handler function named file_fdw_handler:

    CREATE FOREIGN DATA WRAPPER file HANDLER file_fdw_handler;

  • Create a foreign data wrapper named mywrapper that includes a debug option:

    CREATE FOREIGN DATA WRAPPER mywrapper
    OPTIONS (
        debug 'true'
    );

Create a foreign server

The CREATE SERVER command lets you define a new foreign server. The user who defines the server becomes its owner. A foreign server typically encapsulates connection information that an FDW uses to access an external data source. Additional user-specific connection information is specified in a user mapping. Creating a server requires the USAGE privilege on the foreign data wrapper specified. See Command reference for the complete description of command’s clauses and parameters. For a practical example of using the command, see Usage example.

The command general syntax is as follows:

CREATE SERVER <server_name> [ TYPE '<server_type>' ] [ VERSION '<server_version>' ]
    FOREIGN DATA WRAPPER <fdw_name>
    [ OPTIONS (
        [ mpp_execute { 'master' | 'any' | 'all segments' } [, ] ]
        [ num_segments '<num>' [, ] ]
        [ <option> '<value>' [, ... ]]
    ) ]

In the following example, a foreign server named myserver is created. The server uses the pgsql foreign data wrapper and includes connection options:

CREATE SERVER myserver FOREIGN DATA WRAPPER pgsql
    OPTIONS (
        host 'foo',
        dbname 'foodb',
        port '5432'
    );

Create a user mapping

The CREATE USER MAPPING command lets you define a mapping of a user to a foreign server by specifying the remote user name and password. You must be the owner of the server to define user mappings for it. See Command reference for the complete description of command’s clauses and parameters. For a practical example of using the command, see Usage example.

The command general syntax is as follows:

CREATE USER MAPPING FOR { <username> | USER | CURRENT_USER | PUBLIC }
    SERVER <servername>
    [ OPTIONS (
        <option> '<value>' [, ... ]
    ) ]

In the following example, a user mapping for the user john and server myserver is created:

CREATE USER MAPPING FOR john SERVER myserver
    OPTIONS (
        user 'john',
        password 'password'
    );

Create a foreign table

The CREATE FOREIGN TABLE command creates a new foreign table in the current database. To create a foreign table, you must have the USAGE privilege on the foreign server and on all column types used in the table. The user who creates the foreign table becomes its owner. See Command reference for the complete description of command’s clauses and parameters. For a practical example of using the command, see Usage example.

The command general syntax is as follows:

CREATE FOREIGN TABLE [ IF NOT EXISTS ] <table_name> ( [
    <column_name> <data_type>
    [ OPTIONS ( <option> '<value>' [, ... ] ) ]
    [ COLLATE <collation> ]
    [ <column_constraint> [ ... ] ]
    [, ... ]
] )
    SERVER <server_name>
    [ OPTIONS (
        [ mpp_execute { 'master' | 'any' | 'all segments' } [, ] ]
        <option> '<value>' [, ... ]
) ]

where column_constraint is:

[ CONSTRAINT <constraint_name> ]
{ NOT NULL | NULL | DEFAULT <default_expr> }

The columns of the foreign table must match those of the referenced remote table. You can use table or column names different from the remote ones if you specify the correct remote names as options of the foreign table object.

NOTE

The CREATE FOREIGN TABLE command automatically creates a data type that represents the composite type corresponding to one row of the foreign table, therefore foreign tables cannot have the same name as any existing data type in the same schema.

If the table name is schema-qualified (for example, CREATE FOREIGN TABLE myschema.mytable …​), Greengage DB creates the table in the specified schema. Otherwise, the foreign table is created in the current schema. The name of the foreign table must be distinct from the name of any other foreign table, table, sequence, index, or view in the same schema.

In the following example, a foreign table named customers is created. The table uses the myserver foreign server:

CREATE FOREIGN TABLE customers (
    customer_id integer NOT NULL,
    first_name varchar(30) NOT NULL,
    last_name varchar(30),
    email varchar(50),
    phone varchar(20)
)
SERVER myserver;

postgres_fdw

The postgres_fdw module is a foreign data wrapper used for accessing data stored in a remote PostgreSQL or Greengage DB database. It is a modified version of the PostgreSQL postgres_fdw module. The module is included in the Greengage DB distribution. It is packaged as an extension, and you must register it in each database where you plan to use it. To do this, install the postgres_fdw extension using the CREATE EXTENSION command. For example, to register the extension in the database named testdb, use the following command:

$ psql -d testdb -c 'CREATE EXTENSION postgres_fdw;'

To remove a module from a database, drop the associated extension. For example, to remove the postgres_fdw extension from the testdb database, use the following command:

$ psql -d testdb -c 'DROP EXTENSION postgres_fdw;'
Greengage DB limitations

postgres_fdw has the following limitations when used to access Greengage DB:

  • The ctid is not guaranteed to uniquely identify the physical location of a row within its table. For example, the following statements may return incorrect results when the foreign table references a Greengage DB table:

    INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
SELECT * FROM ft1, t1 WHERE t1.ctid = '(0,2)';

  • postgres_fdw does not support local or remote triggers when you use it to access a foreign table that references a Greengage DB table.

  • UPDATE or DELETE operations on a foreign table that references a Greengage DB table are not guaranteed to work correctly.

Usage example

This example demonstrates loading and unloading data between Greengage DB and PostgreSQL by using the postgres_fdw foreign data wrapper. To try it out, make sure you have a PostgreSQL server running and accessible in addition to the Greengage DB server.

  1. Connect to the PostgreSQL server using psql, create the customers test database, and connect to it:

    DROP DATABASE IF EXISTS customers;
CREATE DATABASE customers;
\c customers

    Then create the customers table and fill it with sample data as follows:

    CREATE TABLE customers
(
    id INTEGER,
    name VARCHAR(50),
    email VARCHAR(100),
    address VARCHAR(255)
);

INSERT INTO customers (id, name, email, address)
VALUES (1,'John Doe','john.doe@example.com','123 Elm Street'),
       (2,'Jane Smith','jane.smith@example.com','456 Oak Street'),
       (3,'Bob Brown','bob.brown@example.com','789 Pine Street'),
       (4,'Rob Stuart','rob.stuart@example.com','119 Willow Street');

  2. Create a user role and grant it privileges on the customers table:

    CREATE USER "user" WITH PASSWORD 'password';
GRANT ALL ON TABLE customers TO "user";

  3. Connect to the Greengage DB master host as gpadmin using psql as described in Connect to Greengage DB via psql. Then create the customers test database and connect to it:

    DROP DATABASE IF EXISTS customers;
CREATE DATABASE customers;
\c customers

  4. On the Greengage DB master host, view if any foreign data wrappers are installed:

    SELECT * FROM pg_catalog.pg_foreign_data_wrapper;

    The output should look as follows:

     fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
---------+----------+------------+--------------+--------+------------
(0 rows)

  5. List the extensions available for installation:

    SELECT * FROM pg_available_extensions;

    The output should look similar to the following, with postgres_fdw listed among available extensions:

         name     | default_version | installed_version |                       comment

--------------+-----------------+-------------------+----------------------------------------------------
 ...
 postgres_fdw | 1.0             |                   | foreign data wrapper for remote PostgreSQL servers
 ...
(37 rows)

  6. Create an extension to register the postgres_fdw FDW in the customers database:

    CREATE EXTENSION postgres_fdw;

    Then, make sure the postgres_fdw FDW is available:

    SELECT * FROM pg_catalog.pg_foreign_data_wrapper;

    The output should look as follows:

       fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
--------------+----------+------------+--------------+--------+------------
 postgres_fdw |       10 |      25188 |        25189 |        |
(1 row)

  7. Create a foreign server that uses the postgres_fdw FDW and points to the PostgreSQL server. In the OPTIONS clause, specify the server’s hostname or IP address (host), the port to connect to (port), and the database used (dbname):

    CREATE SERVER postgres
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'postgres', port '5432', dbname 'customers');

  8. Create a user mapping that will handle authentication on the PostgreSQL server. In the SERVER clause, specify the earlier created postgres server; in the OPTIONS clause, provide the authentication parameters (user and password):

    CREATE USER MAPPING FOR gpadmin
    SERVER postgres
    OPTIONS (user 'user', password 'password');

  9. Create the customers foreign table, whose name and structure matches the one of the customers table initially created on the PostgreSQL server. In the SERVER clause, specify the earlier created postgres server:

    CREATE FOREIGN TABLE customers
(
    id INTEGER,
    name VARCHAR(50),
    email VARCHAR(100),
    address VARCHAR(255)
)
SERVER postgres;

  10. Query the created foreign table:

    SELECT * FROM customers;

    The output should look as follows:

    id |    name    |         email          |      address
----+------------+------------------------+-------------------
1 | John Doe   | john.doe@example.com   | 123 Elm Street
2 | Jane Smith | jane.smith@example.com | 456 Oak Street
3 | Bob Brown  | bob.brown@example.com  | 789 Pine Street
4 | Rob Stuart | rob.stuart@example.com | 119 Willow Street
(4 rows)

  11. Insert new values into the customers foreign table:

    INSERT INTO customers (id, name, email, address)
VALUES (5, 'Alice Johnson', 'alice.johnson@example.com', '221 Maple Drive'),
       (6, 'David Lee', 'david.lee@example.com', '333 Birch Lane'),
       (7, 'Emily Wilson', 'emily.wilson@example.com', '444 Cedar Court'),
       (8, 'Michael Garcia', 'michael.garcia@example.com', '555 Walnut Place');

  12. On the PostgreSQL server, query the original customers table:

    SELECT * FROM customers;

    The output should look as follows:

     id |      name      |           email            |      address
----+----------------+----------------------------+-------------------
  1 | John Doe       | john.doe@example.com       | 123 Elm Street
  2 | Jane Smith     | jane.smith@example.com     | 456 Oak Street
  3 | Bob Brown      | bob.brown@example.com      | 789 Pine Street
  4 | Rob Stuart     | rob.stuart@example.com     | 119 Willow Street
  5 | Alice Johnson  | alice.johnson@example.com  | 221 Maple Drive
  6 | David Lee      | david.lee@example.com      | 333 Birch Lane
  7 | Emily Wilson   | emily.wilson@example.com   | 444 Cedar Court
  8 | Michael Garcia | michael.garcia@example.com | 555 Walnut Place
(8 rows)

Command reference

Parameter Description

<name>

The name of the foreign data wrapper to create. The name must be unique within the database

HANDLER <handler_function>

The name of a previously registered function that Greengage DB calls to retrieve the execution functions for foreign tables. handler_function must take no arguments, and its return type must be fdw_handler.

It is possible to create a foreign data wrapper with no handler function, but you can only declare, not access, foreign tables using such a wrapper

VALIDATOR <validator_function>

The name of a previously registered function that Greengage DB calls to check the options provided to the foreign data wrapper. This function also checks the options for foreign servers, user mappings, and foreign tables that use the foreign data wrapper. If no validator function or NO VALIDATOR is specified, Greengage DB does not check options at creation time.

validator_function must take two arguments: one of type text[], which contains the array of options as stored in the system catalogs, and the other of type oid, which identifies the OID of the system catalog containing the options.

The return type is ignored; validator_function should report invalid options using the ereport(ERROR) function

OPTIONS ( option 'value' [, …​] )

The options for the new foreign data wrapper. Option names must be unique. The option names and values are foreign data wrapper-specific and are validated using the foreign data wrappers' validator_function

mpp_execute { 'master' | 'any' | 'all segments' }

Identifies the host from which the foreign data wrapper reads or writes data:

  • master (default) — read or write data from the master host.

  • any — read data from either the master host or any one segment, depending on which path costs less.

  • all segments — read or write data from all segments.

The value is overridden if also specified on the foreign server or the foreign table level. Learn more in mpp_execute option
Parameter Description

<server_name>

The name of the foreign server to create. The server name must be unique within the database

<server_type>

Optional server type, potentially useful to foreign data wrappers

<server_version>

Optional server version, potentially useful to foreign data wrappers

<fdw_name>

Name of the foreign data wrapper that manages the server

OPTIONS ( option 'value' [, …​ ] )

The options for the new foreign server. The options typically define the connection details of the server, but the actual names and values are dependent upon the server’s foreign data wrapper

mpp_execute { 'master' | 'any' | 'all segments' }

Identifies the host from which the foreign data wrapper reads or writes data:

  • master (default) — read or write data from the master host.

  • any — read data from either the master host or any one segment, depending on which path costs less.

  • all segments — read or write data from all segments.

The value is overridden if also specified on the foreign table level. Learn more in mpp_execute option

num_segments '<num>'

When mpp_execute is set to all segments, the num_segments option defines the number of query executors that Greengage DB spawns on the source Greengage DB cluster. If no value is provided, num defaults to the number of segments in the source cluster. Support for this option is foreign data wrapper-specific
Parameter Description

<username>

The name of an existing user that is mapped to the foreign server. CURRENT_USER and USER match the name of the current user. PUBLIC is used to match all present and future user names in the system

<servername>

The name of an existing server for which the user mapping is created

OPTIONS ( <option> '<value>' [, …​ ] )

The options for the new user mapping. The options typically define the actual user name and password of the mapping. Option names must be unique. The option names and values are specific to the server’s foreign data wrapper
Parameter Description

IF NOT EXISTS

Do not throw an error if a relation with the same name already exists. Greengage DB issues a notice in this case. Note that there is no guarantee that the existing relation is anything like the one that would have been created

<table_name>

The name (optionally schema-qualified) of the foreign table to create

<column_name>

The name of the column to create in the new foreign table

<data_type>

The data type of the column, including array specifiers

NOT NULL

Defines that the column is not allowed to contain NULL values

NULL

Defines that the column is allowed to contain NULL values. This is the default behavior.

This clause is provided only for compatibility with non-standard SQL databases. Its use is discouraged in new applications

DEFAULT <default_expr>

Assigns a default value for the column whose definition it appears within. The value is any variable-free expression; Greengage DB does not allow subqueries and cross-references to other columns in the current table. The data type of the default expression must match the data type of the column.

Greengage DB uses the default expression in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is NULL

<server_name>

The name of an existing server to use for the foreign table

OPTIONS ( option 'value' [, …​] )

The options for the new foreign table or one of its columns. While option names must be unique, a table option and a column option may have the same name. The option names and values are foreign data wrapper-specific. Greengage DB validates the options and values using the foreign data wrapper’s validator_function

mpp_execute { 'master' | 'any' | 'all segments' }

Identifies the host from which the foreign data wrapper reads or writes data:

  • master (default) — read or write data from the master host.

  • any — read data from either the master host or any one segment, depending on which path costs less.

  • all segments — read or write data from all segments.

The specified value overrides those specified on the foreign server or the foreign data wrapper level. Learn more in mpp_execute option

mpp_execute option

Most PostgreSQL FDWs should work with Greengage DB. However, PostgreSQL FDWs connect only through the Greengage DB master by default and do not access the Greengage DB segment instances directly.

To handle this, Greengage DB provides the mpp_execute option to FDW-related SQL commands. If a foreign data wrapper supports it, you can specify mpp_execute 'value' in the OPTIONS clause when creating an FDW, a server, or a foreign table to identify the host from which the foreign data wrapper reads or writes data.

One of the following values can be set:

  • master (default) — read or write data from the master host.

  • any — read data from either the master host or any one segment, depending on which path costs less.

  • all segments — read or write data from all segments.

The mpp_execute option can be specified in the CREATE FOREIGN TABLE, CREATE SERVER, and CREATE FOREIGN DATA WRAPPER commands. When specified in multiple commands, the following order of precedence applies (highest to lowest):

  • Foreign table

  • Foreign server

  • Foreign data wrapper

Greengage DB supports parallel writes to foreign tables only when mpp_execute is set to all segments.

NOTE

Support for the foreign table’s mpp_execute option and the specific modes is foreign data wrapper-specific. If a foreign data wrapper supports this option, for correct results it should have a policy that matches segments to data. A PostgreSQL foreign data wrapper may work with various mpp_execute option settings, but the results are not guaranteed to be correct. For example, a segment may not be able to connect to a foreign server, or segments may receive overlapping results thus producing duplicate rows.

Previous
Use custom formats and protocols
Next
PostgreSQL