tracking: support tableau

[neverchanje]

• Support parameters. We can ignore the parameter values and return a warning note to the client.
  • client_min_messages
  • client_encoding
  • extra_float_digits
  • timezone
• More tests ongoing

[neverchanje]

Please feel free to reassign this issue to someone else. @xiangjinwu @yezizp2012

[xiangjinwu]

We can ignore the parameter values and return a warning note to the client.

We'd better validate the value and reject unsupported values, for example setting encoding to non-utf8

related: #10697

[xiangjinwu]

All 4 parameters listed above can now be set, although 3 of them (except timezone) are not effectively using the value.

[jetjinser]

I tested the connection to RisingWave on Tableau Desktop (2023.2.1), and the data source connector uses PostgreSQL.

The connection was not successful, the error message is as follows:

An error occurred while communicating with the PostgreSQL data source 'Untitled Data Source'
Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.
Error Code: A018C2A1
An I/O error occurred while sending to the backend.
Unable to connect to the PostgreSQL server "localhost". Check that the server is running and that you have access privileges to the requested database.

The following is the sql query part of the log logged when Tableau connects to PostgreSQL@14:

querys

SET extra_float_digits = 3
SET application_name = 'Tableau Desktop/Desktop/jdbc-server 20232.23.0805.0415'

BEGIN
SET TIMEZONE TO 'UTC'
COMMIT

BEGIN
SET client_min_messages TO 'ERROR'
COMMIT

BEGIN
-- x rw x
show "lc_collate"
COMMIT

BEGIN
-- statement does not return rows
-- x rw x
SELECT *
INTO TEMPORARY TABLE "#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_1_Connect_Chec"
FROM (SELECT 1 AS COL) AS CHECKTEMP
LIMIT 1
COMMIT

BEGIN
DROP TABLE "#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_1_Connect_Chec"
COMMIT

BEGIN
-- x rw x
CREATE LOCAL TEMPORARY TABLE "#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_2_Connect_Chec" (
    "COL" INTEGER
) ON COMMIT PRESERVE ROWS
COMMIT

BEGIN
INSERT INTO "#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_2_Connect_Chec" ("COL")
    VALUES ($1)
COMMIT

BEGIN
CREATE INDEX "_tidx_#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_2_Connec" ON
    "#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_2_Connect_Chec" ("COL")
COMMIT

BEGIN
DROP TABLE "#Tableau_30_25B571F1-F906-4D07-8438-3E3A1D353C9E_2_Connect_Chec"
COMMIT

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'p' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'p' THEN 'PARTITIONED TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'P' then 'PARTITIONED INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  WHEN 'm' THEN 'MATERIALIZED VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS,  '' as TYPE_CAT, '' as TYPE_SCHEM, '' as TYPE_NAME, '' AS SELF_REFERENCING_COL_NAME, '' AS REF_GENERATION  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0  and d.classoid = 'pg_class'::regclass)  WHERE c.relnamespace = n.oid  AND (false  OR ( c.relkind = 'f' )  OR ( c.relkind = 'm' )  OR ( c.relkind = 'p' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )  OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 2615 AS oid , 2 AS attnum UNION ALL SELECT 1259, 2 UNION ALL SELECT 2609, 4) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum)

It can be tested that RisingWave does not support the following expressions:

• show "lc_collate" (ref)
• SELECT INTO (ref)
• CREATE LOCAL (ref)

btw lc_collate also needed by pgAdmin 4, #11180.

[neverchanje]

Close as there are too many unnecessary features to support.