feat: a lot of tables listed by default in Postgres

[lostmygithubaccount]

Is your feature request related to a problem?

a lot (247) of tables are listed in con.list_tables() for a default postgres connection after running just up postgres:

[ins] In [1]: import ibis

[ins] In [2]: con = ibis.postgres.connect(host="localhost", user="postgres", password="postgres", database="ibis_testing")

[ins] In [3]: len(con.list_tables())
Out[3]: 247

most of these seem to be system tables (though I'm not that familiar w/ Postgres details):

See all tables

```python [ins] In [4]: con.list_tables() Out[4]: ['_pg_foreign_data_wrappers', '_pg_foreign_servers', '_pg_foreign_table_columns', '_pg_foreign_tables', '_pg_user_mappings', 'addr', 'addrfeat', 'administrable_role_authorizations', 'applicable_roles', 'attributes', 'bg', 'character_sets', 'check_constraint_routine_usage', 'check_constraints', 'collation_character_set_applicability', 'collations', 'column_column_usage', 'column_domain_usage', 'column_options', 'column_privileges', 'column_udt_usage', 'columns', 'constraint_column_usage', 'constraint_table_usage', 'county', 'county_lookup', 'countysub_lookup', 'cousub', 'data_type_privileges', 'direction_lookup', 'domain_constraints', 'domain_udt_usage', 'domains', 'edges', 'element_types', 'enabled_roles', 'faces', 'featnames', 'foreign_data_wrapper_options', 'foreign_data_wrappers', 'foreign_server_options', 'foreign_servers', 'foreign_table_options', 'foreign_tables', 'geocode_settings', 'geocode_settings_default', 'geography_columns', 'geometry_columns', 'information_schema_catalog_name', 'key_column_usage', 'layer', 'loader_lookuptables', 'loader_platform', 'loader_variables', 'pagc_gaz', 'pagc_lex', 'pagc_rules', 'parameters', 'pg_aggregate', 'pg_am', 'pg_amop', 'pg_amproc', 'pg_attrdef', 'pg_attribute', 'pg_auth_members', 'pg_authid', 'pg_available_extension_versions', 'pg_available_extensions', 'pg_backend_memory_contexts', 'pg_cast', 'pg_class', 'pg_collation', 'pg_config', 'pg_constraint', 'pg_conversion', 'pg_cursors', 'pg_database', 'pg_db_role_setting', 'pg_default_acl', 'pg_depend', 'pg_description', 'pg_enum', 'pg_event_trigger', 'pg_extension', 'pg_file_settings', 'pg_foreign_data_wrapper', 'pg_foreign_server', 'pg_foreign_table', 'pg_group', 'pg_hba_file_rules', 'pg_ident_file_mappings', 'pg_index', 'pg_indexes', 'pg_inherits', 'pg_init_privs', 'pg_language', 'pg_largeobject', 'pg_largeobject_metadata', 'pg_locks', 'pg_matviews', 'pg_namespace', 'pg_opclass', 'pg_operator', 'pg_opfamily', 'pg_parameter_acl', 'pg_partitioned_table', 'pg_policies', 'pg_policy', 'pg_prepared_statements', 'pg_prepared_xacts', 'pg_proc', 'pg_publication', 'pg_publication_namespace', 'pg_publication_rel', 'pg_publication_tables', 'pg_range', 'pg_replication_origin', 'pg_replication_origin_status', 'pg_replication_slots', 'pg_rewrite', 'pg_roles', 'pg_rules', 'pg_seclabel', 'pg_seclabels', 'pg_sequence', 'pg_sequences', 'pg_settings', 'pg_shadow', 'pg_shdepend', 'pg_shdescription', 'pg_shmem_allocations', 'pg_shseclabel', 'pg_stat_activity', 'pg_stat_all_indexes', 'pg_stat_all_tables', 'pg_stat_archiver', 'pg_stat_bgwriter', 'pg_stat_database', 'pg_stat_database_conflicts', 'pg_stat_gssapi', 'pg_stat_progress_analyze', 'pg_stat_progress_basebackup', 'pg_stat_progress_cluster', 'pg_stat_progress_copy', 'pg_stat_progress_create_index', 'pg_stat_progress_vacuum', 'pg_stat_recovery_prefetch', 'pg_stat_replication', 'pg_stat_replication_slots', 'pg_stat_slru', 'pg_stat_ssl', 'pg_stat_subscription', 'pg_stat_subscription_stats', 'pg_stat_sys_indexes', 'pg_stat_sys_tables', 'pg_stat_user_functions', 'pg_stat_user_indexes', 'pg_stat_user_tables', 'pg_stat_wal', 'pg_stat_wal_receiver', 'pg_stat_xact_all_tables', 'pg_stat_xact_sys_tables', 'pg_stat_xact_user_functions', 'pg_stat_xact_user_tables', 'pg_statio_all_indexes', 'pg_statio_all_sequences', 'pg_statio_all_tables', 'pg_statio_sys_indexes', 'pg_statio_sys_sequences', 'pg_statio_sys_tables', 'pg_statio_user_indexes', 'pg_statio_user_sequences', 'pg_statio_user_tables', 'pg_statistic', 'pg_statistic_ext', 'pg_statistic_ext_data', 'pg_stats', 'pg_stats_ext', 'pg_stats_ext_exprs', 'pg_subscription', 'pg_subscription_rel', 'pg_tables', 'pg_tablespace', 'pg_timezone_abbrevs', 'pg_timezone_names', 'pg_transform', 'pg_trigger', 'pg_ts_config', 'pg_ts_config_map', 'pg_ts_dict', 'pg_ts_parser', 'pg_ts_template', 'pg_type', 'pg_user', 'pg_user_mapping', 'pg_user_mappings', 'pg_views', 'place', 'place_lookup', 'referential_constraints', 'role_column_grants', 'role_routine_grants', 'role_table_grants', 'role_udt_grants', 'role_usage_grants', 'routine_column_usage', 'routine_privileges', 'routine_routine_usage', 'routine_sequence_usage', 'routine_table_usage', 'routines', 'schemata', 'secondary_unit_lookup', 'sequences', 'spatial_ref_sys', 'sql_features', 'sql_implementation_info', 'sql_parts', 'sql_sizing', 'state', 'state_lookup', 'street_type_lookup', 'tabblock', 'tabblock20', 'table_constraints', 'table_privileges', 'tables', 'topology', 'tract', 'transforms', 'triggered_update_columns', 'triggers', 'udt_privileges', 'usage_privileges', 'user_defined_types', 'user_mapping_options', 'user_mappings', 'view_column_usage', 'view_routine_usage', 'view_table_usage', 'views', 'zcta5', 'zip_lookup', 'zip_lookup_all', 'zip_lookup_base', 'zip_state', 'zip_state_loc'] ```

Describe the solution you'd like

if possible, avoid listing system tables

What version of ibis are you running?

main

What backend(s) are you using, if any?

Postgres; would be nice to do this for others if it applies

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[gforsyth]

Can you try out my no schema branch? I think I fixed this over there

[lostmygithubaccount]

mostly fixed! down to 3:

[ins] In [1]: import ibis

[ins] In [2]: con = ibis.postgres.connect(host="localhost", user="postgres", password="postgres", database="ibis_testing")

[ins] In [3]: con.list_tables()
Out[3]: ['geography_columns', 'geometry_columns', 'spatial_ref_sys']

if it's possible/you want to remove those you can leave this open, or perhaps just mark your PR as closing this issue?

[gforsyth]

I'll self-assign this and tackle it as a followup after #8655 is merged.

[gforsyth]

Hmm, well, I'm not sure what to do about those tables -- it's two views and one table and they are in the public "schema" (grrr).

ibis_testing=# \dt public.*
              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | spatial_ref_sys | table | postgres
(1 row)

ibis_testing=# \dv public.*
              List of relations
 Schema |       Name        | Type |  Owner   
--------+-------------------+------+----------
 public | geography_columns | view | postgres
 public | geometry_columns  | view | postgres
(2 rows)

We can add special handling to remove them from the output of list_tables but I think we'd want to know that users wouldn't ever need to see them.

[jcrist]

We can add special handling to remove them from the output of list_tables but I think we'd want to know that users wouldn't ever need to see them.

IMO I think the optimal behavior for "system" tables like these would be:

• They don't show up in things like list_tables cluttering the output
• Users can still explicitly get a reference to them by doing con.table(name) to access them if they need to

Filtering them from list_tables seems fine to me.

[gforsyth]

Currently on the kill-schema branch, if no catalog or database is provided, we use the default values of self.current_catalog and self.current_database -- which for the Ibis testing container should be ibis_testing.public.

I think filtering out the above system tables for a "clean" con.list_tables() sounds good.

Do we want to be less prescriptive if the user is explicitly asking for things in ibis_testing.public?

e.g. do we want something like this? ( I genuinely don't know -- I think I don't like this but I'm waffling)

>>> con.list_tables()
[]
>>> con.list_tables(database=("ibis_testing", "public"))
['geography_columns', 'geometry_columns', 'spatial_ref_sys']

[cpcloud]

I think if we consider non-system tables to be any table that wasn't created by postgres itself, that was created using a CREATE TABLE statement or the corresponding C API, then we should include tables created by extensions in the output of list_tables().

[jcrist]

I think we should be uniform in how we're filtering, so if "system" tables can appear in other databases/catalogs then we should also filter there.

I could also go with Phillip's definition of system tables above, although I think the geo-extension is common enough (and creates some known tables) so special-casing and excluding those here also seems fine to me.

[cpcloud]

It seems like extension tables are probably placed into the public schema for a reason, so listing them doesn't strike me as a horrible choice

[cpcloud]

Postgres' default behavior is not as simple as "look in public", it's got something analogous to PATH but for the schema search space, called search_path, further complicating things.

[lostmygithubaccount]

from a UX perspective, I mainly just want to easily see the tables I create in the database. that's hard with 247 but fine with 3 in there by default. no strong opinion from me on whether those 3 should be listed or not but it's fine for my use cases at least

[NickCrews]

I vote don't do anything clever, show everything in public. If you don't want clutter (which is reasonable) then this should get fixed at the geospatial extension level.

[gforsyth]

I vote don't do anything clever that's a good guiding principle.

Ok, I'm going set #8655 to close this