Support psql tab-completion for variables & system parameters

[BugenZhao]

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

Like this

The actual query is:

-- SET 
SELECT name FROM (SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings WHERE context IN ('user', 'superuser') UNION ALL SELECT 'constraints' UNION ALL SELECT 'transaction' UNION ALL SELECT 'session' UNION ALL SELECT 'role' UNION ALL SELECT 'tablespace' UNION ALL SELECT 'all') AS ss WHERE SUBSTRING(name FROM 1 FOR 4) = 'wal_' LIMIT 1000;

-- ALTER SYSTEM SET
SELECT name FROM (SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings WHERE context <> 'internal' UNION ALL SELECT 'all') AS ss WHERE SUBSTRING(name FROM 1 FOR 0) = '' LIMIT 1000;

Describe the solution you'd like

The main part is to provide a correct schema and content of the system table (view) pg_catalog.pg_settings. We should record all variables (session variables and system parameters) here.

Describe alternatives you've considered

No response

Additional context

No response

[Gun9niR]

We should record all variables (session variables and system parameters) here.

SET is for session variables, ALTER SYSTEM SET is for system parameters. So the auto-completion should be two different sets of variables.

[BugenZhao]

So the auto-completion should be two different sets of variables.

Yes. This is filtered by the context field from the pg_settings table.

context IN ('user', 'superuser')
context <> 'internal'

[hzxa21]

tab completion used to work after #8839 but it doesn't work now. I suspect it is because #9237 removed the max one row check hack for pg_namespace. Is it possible to add it back? cc @BugenZhao @xxchan

[xxchan]

We have it here

risingwave/src/frontend/src/optimizer/plan_visitor/cardinality_visitor.rs

Lines 44 to 57 in 20101cd

	// We don't have UNIQUE key now. So we hack here to support some complex queries on
	// system tables.
	// TODO(card): remove this after we have UNIQUE key. https://github.com/risingwavelabs/risingwave/issues/12514
	if let Some(scan) = input.as_logical_scan()
	&& scan.is_sys_table()
	&& scan.table_name() == PG_NAMESPACE_TABLE_NAME
	{
	if let Some(nspname) = scan
	.output_col_idx()
	.iter()
	.find(|i| scan.table_desc().columns[**i].name == "nspname") {
	unique_keys.push([*nspname].into_iter().collect());
	}
	}

[hzxa21]

We have it here

risingwave/src/frontend/src/optimizer/plan_visitor/cardinality_visitor.rs

Lines 44 to 57 in 20101cd

	// We don't have UNIQUE key now. So we hack here to support some complex queries on
	// system tables.
	// TODO(card): remove this after we have UNIQUE key. https://github.com/risingwavelabs/risingwave/issues/12514
	if let Some(scan) = input.as_logical_scan()
	&& scan.is_sys_table()
	&& scan.table_name() == PG_NAMESPACE_TABLE_NAME
	{
	if let Some(nspname) = scan
	.output_col_idx()
	.iter()
	.find(|i| scan.table_desc().columns[**i].name == "nspname") {
	unique_keys.push([*nspname].into_iter().collect());
	}
	}

Something must be broken. I think tab completion has stopped working for a while. I see Scalar subquery might produce more than one row when running the following query on the latest main:

SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,0)='' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,0)='' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,0) = substring('',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,0)='' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,0) = substring('',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,0) = substring('',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000;

[xxchan]

I think it's because of #11278

[BugenZhao]

tab completion used to work after #8839 but it doesn't work now

I'm unsure, but it appears to be a separate issue. Tab-completion for variables and system parameters has never been supported because of the missing column.

QueryError: Bind error: failed to bind expression: context <> 'internal'

Caused by:
  Item not found: Invalid column: context

[xxchan]

Yes, the problem is reported to the wrong issue 🤪

…

On Tue, Oct 17, 2023 at 3:39 PM Bugen Zhao ***@***.***> wrote: tab completion used to work after #8839 <#8839> but it doesn't work now I'm unsure, but it appears to be a separate issue. Tab-completion for *variables and system parameters* has never been supported because of the missing column. QueryError: Bind error: failed to bind expression: context <> 'internal' Caused by: Item not found: Invalid column: context — Reply to this email directly, view it on GitHub <#8850 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJBQZNPDDGSZQUINQCITRQTX7YY23AVCNFSM6AAAAAAWLQDG72VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONRVHA2DEMZUGY> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[hzxa21]

Yes, the problem is reported to the wrong issue 🤪
…
On Tue, Oct 17, 2023 at 3:39 PM Bugen Zhao @.> wrote: tab completion used to work after #8839 <#8839> but it doesn't work now I'm unsure, but it appears to be a separate issue. Tab-completion for variables and system parameters has never been supported because of the missing column. QueryError: Bind error: failed to bind expression: context <> 'internal' Caused by: Item not found: Invalid column: context — Reply to this email directly, view it on GitHub <#8850 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJBQZNPDDGSZQUINQCITRQTX7YY23AVCNFSM6AAAAAAWLQDG72VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONRVHA2DEMZUGY . You are receiving this because you were mentioned.Message ID: @.>

My bad. I thought this is the issue to track tab completion. Let me repost my comments in #8837