bug: schema search path is not followed in SHOW TABLES

[BugenZhao]

Currently when running SHOW TABLES in RisingWave, the schema search path is not followed and the default schema public will always be used.

risingwave/src/frontend/src/handler/show.rs

Lines 253 to 256 in 5be13a4

	// If not include schema name, use default schema name
	ShowObject::Table { schema } => catalog_reader
	.read_guard()
	.get_schema_by_name(session.database(), &schema_or_default(&schema))?

Although SHOW TABLES is not a standard command in Postgres, users may still expect SHOW TABLES to behave the same as \dt. Besides, CockroachDB provide support for SHOW TABLES and it'll follow the search path.

https://www.cockroachlabs.com/docs/stable/show-tables#parameters

SHOW TABLES will attempt to find a schema with the specified name first. If that fails, it will try to find a database with that name instead, and list the tables of its public schema. For more details, see Name Resolution.

---

Furthermore, should we delegate the implementation of 'SHOW' commands directly to the system tables for catalogs (i.e. \dt)?

[tabVersion]

@BugenZhao can you share more on the topic?

Currently when running SHOW TABLES in RisingWave, the schema search path is not followed and the default schema public will always be used.

Do you mean the fallback path should be current schema -> current database -> other database?

[BugenZhao]

Do you mean the fallback path should be current schema -> current database -> other database?

Should follow the behavior described in the manual: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH . We've implemented it before, so the only stuff to do is to utilize it.

risingwave/src/common/src/session_config/search_path.rs

Lines 23 to 40 in 90eb6f1

	/// see <https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-SEARCH-PATH>
	///
	/// 1. when we `select` or `drop` object and don't give a specified schema, it will search the
	/// object from the valid items in schema `rw_catalog`, `pg_catalog` and `search_path`. If schema
	/// `rw_catalog` and `pg_catalog` are not in `search_path`, we will search them firstly. If they're
	/// in `search_path`, we will follow the order in `search_path`.
	///
	/// 2. when we `create` a `source` or `mv` and don't give a specified schema, it will use the first
	/// valid schema in `search_path`.
	///
	/// 3. when we `create` a `index` or `sink`, it will use the schema of the associated table.
	#[derive(Clone, Debug, PartialEq)]
	pub struct SearchPath {
	origin_str: String,
	/// The path will implicitly includes `rw_catalog` and `pg_catalog` if user does specify them.
	path: Vec<String>,
	real_path: Vec<String>,
	}