tracking: support for Grafana

[neverchanje]

Is your feature request related to a problem? Please describe.

The remaining works:

• information_schema.columns https://www.postgresql.org/docs/current/infoschema-columns.html
  • ordinal_position
  • udt_name
• Support functions: array_upper and array_lower #10135
• Support current_setting #10051

The original queries:

SELECT quote_ident(table_name) FROM information_schema.tables WHERE
quote_ident(table_schema) IN (
  SELECT
    CASE WHEN trim(s[i]) = '"$user"' THEN user ELSE trim(s[i]) END
  FROM
    generate_series(
      array_lower(string_to_array(current_setting('search_path'),','),1),
      array_upper(string_to_array(current_setting('search_path'),','),1)
    ) as i,
    string_to_array(current_setting('search_path'),',') s
) ORDER BY table_name limit 100;

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[neverchanje]

Another query:

SELECT
        quote_ident(table_name) as table_name,
        ( SELECT
            quote_ident(column_name) as column_name
          FROM information_schema.columns c
    WHERE
      c.table_schema = t.table_schema AND
      c.table_name = t.table_name AND
      udt_name IN ('timestamptz','timestamp')
    ORDER BY ordinal_position LIMIT 1
  ) AS time_column,
  ( SELECT
      quote_ident(column_name) AS column_name
    FROM information_schema.columns c
    WHERE
      c.table_schema = t.table_schema AND
      c.table_name = t.table_name AND
      udt_name='float8'
    ORDER BY ordinal_position LIMIT 1
  ) AS value_column
FROM information_schema.tables t
WHERE
quote_ident(table_schema) IN (
  SELECT
    CASE WHEN trim(s[i]) = '"$user"' THEN user ELSE trim(s[i]) END
  FROM
    generate_series(
      array_lower(string_to_array(current_setting('search_path'),','),1),
      array_upper(string_to_array(current_setting('search_path'),','),1)
    ) as i,
    string_to_array(current_setting('search_path'),',') s
) AND
  EXISTS
  ( SELECT 1
    FROM information_schema.columns c
    WHERE
      c.table_schema = t.table_schema AND
      c.table_name = t.table_name AND
      udt_name IN ('timestamptz','timestamp')
  ) AND
  EXISTS
  ( SELECT 1
    FROM information_schema.columns c
    WHERE
      c.table_schema = t.table_schema AND
      c.table_name = t.table_name AND
      udt_name='float8'
  )
LIMIT 1
;

[yezizp2012]

SELECT quote_ident(table_name) FROM information_schema.tables WHERE
quote_ident(table_schema) IN (
  SELECT
    CASE WHEN trim(s[i]) = '"$user"' THEN user ELSE trim(s[i]) END
  FROM
    generate_series(
      array_lower(string_to_array(current_setting('search_path'),','),1),
      array_upper(string_to_array(current_setting('search_path'),','),1)
    ) as i,
    string_to_array(current_setting('search_path'),',') s
) ORDER BY table_name limit 100;

In this SQL, we can see that PostgreSQL can bind string_to_array as a table with a single value under column s. And actually PostgreSQL will bind the alias in two different ways if provided, take generate_series as example:

postgres=# select a from generate_series(1, 5) a; // a will bind to column.
 a
---
 1
 2
 3
 4
 5
(5 rows)

postgres=# select a.a from generate_series(1, 5) a; // a will bind to a table a with column a.
 a
---
 1
 2
 3
 4
 5
(5 rows)

But in rw, we don't allow to bind scalar function in from section, and the infer column name will always be the function name. To achieve that, two major changes are needed in the frontend:

1. be able to bind scalar function as table function, it will return a table with one row and one column named after the alias (if provided) or the function name.
2. dynamically bind the column names of the query, bind to the alias if provided.

Cc @xxchan , Could you help with this if you have time? Since you are more familiar with the table function piece. 🙏

[xiangjinwu]

  SELECT
    CASE WHEN trim(s[i]) = '"$user"' THEN user ELSE trim(s[i]) END
  FROM
    generate_series(
      array_lower(string_to_array(current_setting('search_path'),','),1),
      array_upper(string_to_array(current_setting('search_path'),','),1)
    ) as i,
    string_to_array(current_setting('search_path'),',') s

Just FYI this subquery has a simpler form for PostgreSQL >= 14, thanks to the introduction of string_to_table function. However grafana would not use this even when the server version is newer.

SELECT
    CASE WHEN trim(s) = '"$user"' THEN user ELSE trim(s) END
FROM
    string_to_table(current_setting('search_path'),',') s;

[neverchanje]

Another issue found: cc @yezizp2012

• Add user column in information_schema.tables
• tracking: support for Grafana #10134 (comment) failed due to "Subquery can not be unnested". I feel this is less urgent if the implementation is not straightforward.

[neverchanje]

Close this issue together with #9097. I've tested the Grafana integration and I didn't experience any errors and the ComputeNode as well as Frontend didn't output errors.