Trino ADB connector overview

Konstantin Alpashkin

Connecting to an ADB cluster through the Trino connector begins with creating a new Trino catalog. The catalog configuration must use the greengagedb connector type and must contain the following set of properties (at minimum):

connector.name=greengagedb (1)
connection-url=jdbc:postgresql://<adb_master>:5432/database (2)
connection-user=<adb_username> (3)
connection-password=<password>
1 Sets the connector type to work with ADB. Starting with ADH 4.2.0, two interchangeable connector names are allowed: connector.name=greengagedb or connector.name=adb.
2 Sets the JDBC connection string to connect to the ADB server. The connector uses the PostgreSQL JDBC driver and supports all its configuration properties as well as inherits its limitations. For more information, see Trino PostgreSQL connector.
3 Specifies the ADB user to connect to ADB. The user must be pre-created on the ADB side and must have sufficient permissions to perform operations in the ADB cluster.

Connector operating modes

The Trino connector can interact with an ADB cluster in several modes described below.

Mode Read Write Description

JDBC

Yes

Yes

The default mode for communication between the connector and an ADB cluster via a JDBC driver. Does not support parallel read/write operations and other optimizations. This is the slowest, however, the most reliable mode, which works when other modes cannot be used

gpfdist

Yes

Yes

gpfdist is a file distribution utility that allows loading and unloading data in parallel from external ADB tables. Under the hood, the Trino connector spins up gpfdist servers on ADH hosts, and ADB segments use gpfdist clients to exchange data with the Trino connector in parallel.

This communication mode is enabled by setting the greengagedb.gpfdist.enabled=true parameter (gpfdist_enabled session parameter)

Parallel cursor

Yes

No

The gp_parallel_retrieve_cursor module is an enhanced cursor implementation that allows fetching query results in parallel directly from ADB segments. This cursor type provides faster parallel reads, adds extra flexibility by fetching data in chunks, and generally is more efficient for retrieving large result sets. The steps to enable this mode are described below

Enable the parallel cursor mode

To activate the parallel cursor mode, do the following:

  1. In the ADB cluster, create the gp_parallel_retrieve_cursor extension:

    CREATE EXTENSION gp_parallel_retrieve_cursor;

  2. When creating a new Trino catalog for ADB, set the greengagedb.read-mode=PARALLEL_CURSOR parameter (greengagedb.read_mode=PARALLEL_CURSOR session parameter).

IMPORTANT
To use the parallel cursor mode, the greengagedb.scan-parallelism parameter value must be SEGMENTS (default value).

Mode selection logic and JDBC fallback

Read mode selection

The connector read mode is specified using the greengagedb.read-mode parameter which accepts the following values:

  • PARALLEL_CURSOR

  • GPFDIST

  • JDBC

  • AUTO

The following table provides implementation details for each mode.

Read mode Implementation details

PARALLEL_CURSOR

Used only if specified explicitly

GPFDIST

If any selected column is not gpfdist-supported, reading through gpfdist is skipped and JDBC is used

JDBC

Used as a fallback communication mode. The fallback rules are as follows:

  • zero-projected columns (no_columns);

  • MERGE_ROW_ID projection;

  • pushed sort order (sort_order) to preserve global ordering semantics;

  • update assignments in table handle (update_assignments);

  • non-named relations;

  • risky arithmetic constraint expressions containing % or / (arithmetic_constraint).

AUTO

If greengagedb.gpfdist.enabled=true is set and all projected columns are gpfdist-compatible, the GPFDIST mode is used. Otherwise, JDBC is used. The AUTO option never engages the parallel cursor mode, since it should be set explicitly

Write mode selection

The base logic for selecting a write mode is as follows:

  • If gpfdist_enabled=true is set, INSERT and CTAS operations use gpfdist. Otherwise, insert operations are performed via JDBC.

  • The UPDATE/DELETE/MERGE operations always use JDBC.

Operations support

SQL support

The following table lists SQL operations supported on the connector level.

Operation Is supported Note

SELECT

Yes

The read mode is selected based on the read_mode parameter and JDBC fallback rules

INSERT / CTAS

Yes

Insert using gpfdist if gpfdist_enabled=true; otherwise JDBC is used

UPDATE / DELETE / MERGE

Yes

DML commands are executed via JDBC

CREATE/DROP TABLE, CREATE/DROP SCHEMA, RENAME SCHEMA

Yes

DDL commands are executed via JDBC

ALTER TABLE, ADD/DROP/RENAME COLUMN

Yes

DDL commands are executed via JDBC

ALTER COLUMN TYPE / DROP NOT NULL

Yes

DDL commands are executed via JDBC

Supported modes for SELECT

The following table describes supported read modes for SELECT operations.

Operation type JDBC Parallel cursor gpfdist

Named table scan

Yes

Yes

Yes

Query relation handle (pushed query/join relation)

Yes

JDBC is forced

JDBC is forced

ORDER BY + LIMIT requiring global order

Yes

JDBC is forced

JDBC is forced

LIMIT pushdown

Yes

Yes (single split)

Yes (single split)

Unsupported gpfdist column types

N/A

N/A

Fallback to JDBC (greengagedb.read-fallback=ALLOW) or fail (greengagedb.read-fallback=FORBID)

MERGE_ROW_ID, empty projection, risky %// constraints

Yes

JDBC is forced

JDBC is forced

Pushdown support

Feature JDBC read Parallel cursor read gpfdist read

Predicate/projection

Yes

Yes

Yes

Aggregation

Yes

Yes

Yes

Join

Yes

Yes

If planning produces query-relation handles, the connector falls back to JDBC for correctness

Yes

If planning produces query-relation handles, the connector falls back to JDBC for correctness

ADB migration to Greengage

In ADH 4.2.0, the Trino ADB connector was updated due to the migration of ADB to GreengageDB. This update resulted in following major changes:

  • A new Trino ADB connector name is used — connector.name=greengagedb. The connector.name=adb can also be used for backward compatibility.

  • Connector configuration parameters were changed.

If you use Trino ADB connector and plan to upgrade to ADH 4.2.0, perform the upgrade steps in following order:

  1. Stop the Trino service before upgrading.

  2. Update the connector configuration according to the new configuration parameters.

  3. Run the cluster upgrade.

Limitations and features

The Trino ADB connector has the following features and limitations:

  • Parallel cursor requires the gp_parallel_retrieve_cursor GreengageDB extension.

  • The gpfdist mode requires network reachability from all active segments to the Trino gpfdist endpoint.

  • The gpfdist health endpoint is available at http://<external-host>:<port>/health.

  • ALTER TABLE …​ ADD COLUMN does not support FIRST/AFTER and column comments.

  • Cross-schema table rename is not supported.

  • __trino_row_id is optional and is disabled by default. To enable generation of this ID for tables created by the connector, set greengagedb.trino-row-id-enabled=true.

  • ADB 7.x (Greengage DB 7.x) is temporarily unsupported.

Found a mistake? Seleсt text and press Ctrl+Enter to report it