Tracking: support trino PostgreSQL connector

[wenym1]

The trino PostgreSQL uses PostgreSQL JDBC connector to access PostgreSQL.

However, when we are currently testing query RisingWave via trino PostgreSQL connector, we gets error when we do select from risingwave table.

The following are some blocking bugs and unsupported features

• Failed to getTables with _ in table name: bug: incorrect wildcard handling in LIKE clause compared to pg #10018
• Failed to getColumns: Unsupported column atttypmod in pg internal table pg_attribute #10019
• When trino tries to fetch the columns information of tables, it calls getColumns on DatabaseMetadata of jdbc connector, and for the PostgreSQL JDBC connector, it involves an batch over window, which depends on
  • Tracking: Over Window (a.k.a. Window Function or Over Aggregation) #9124
  • Bypass the over window by fix(frontend): downgrade pg version #10024
  • Frontend panic: bug: frontend panic when querying pg internal tables #10026

[wenym1]

Now we can support running a simple select * from table SQL statement via the trino PostgreSQL connector.

A demo with docker-compose

In the demo workspace, we have the following files:

• docker-compose.yml

version: "3.5"
services:
  risingwave:
    image: risingwavelabs/risingwave:latest
    container_name: risingwave
    ports:
      - "4566:4566"
      - "5691:5691"
    command: ["playground"]
  trino:
    image: trinodb/trino:418
    container_name: trino
    volumes:
      - ./etc/risingwave.properties:/etc/trino/catalog/risingwave.properties
    ports:
      - "48080:8080"
  trino-client:
    image: trinodb/trino:418
    profiles: ["client"]
    entrypoint: ["trino", "--server", "trino:8080", "--catalog", "risingwave", "--schema", "public"]
networks:
  default:
    name: risingwave-bagel

• etc/risingwave.properties

connector.name=postgresql
connection-url=jdbc:postgresql://risingwave:4566/dev
connection-user=root
connection-password=secret
case-insensitive-name-matching=true

1. Run docker compose up -d to start the demo cluster.
2. Create a demo table and insert test data via psql client

psql -h localhost -p 4566 -d dev -U root

# within the psql clent
dev=> create table test_table(id bigint);
CREATE_TABLE
dev=> insert into test_table values(1);
INSERT 0 1
dev=> flush;
FLUSH

3. Query from trino client

docker compose run -it trino-client

# within the trino client
trino:public> show tables;
   Table    
------------
 test_table 
(1 row)

trino:public> select * from test_table;
 id 
----
  1 
(1 row)

[wenym1]

A docker compose demo for both presto and trino is in #10201