No tables visible when viewing an existing schema

[hydrandt]

Description

When I connect to an existing schema, which was built on the Graphile Starter project, there are no tables.

Expected behavior

Tables should be visible:) They are correctly counted in the list of schemas, but as soon as I enter the schema, I just get "You've created a new schema, now add tables to it."

To Reproduce

1. install using official install script; connect to existing database of the Graphile Starter project
2. open the app_public schema
3. no tables are visible

Environment

• OS: Debian Sid
• Browser: Firefox
• Browser Version: 110.0.1

Additional context

There are following warnings about unrecognized column types:

mathesar_service                | /code/db/tables/operations/select.py:20: SAWarning: Did not recognize type 'citext' of column 'email'                                                                                                
mathesar_service                |   return Table(                                                                                                                                                                                      
mathesar_service                | /code/db/tables/operations/select.py:20: SAWarning: Did not recognize type 'point' of column 'coordinates'                                                                                           
mathesar_service                |   return Table(                                                                                                                                                                                      
mathesar_service                | /code/db/tables/operations/select.py:20: SAWarning: Did not recognize type 'citext' of column 'username'                                                                                             
mathesar_service                |   return Table(                                                                                                                                                                                      
mathesar_service                | /code/db/tables/operations/select.py:20: SAWarning: Did not recognize type 'point' of column 'location_coordinates'                                                                                  
mathesar_service                |   return Table(                                                                                                                                                                                      
mathesar_service                | /code/db/tables/operations/select.py:20: SAWarning: Did not recognize type 'citext' of column 'email'                                                                                                
mathesar_service                |   return Table(                                                                                                                                                                                      
mathesar_service                | /code/db/tables/operations/select.py:20: SAWarning: Did not recognize type 'point' of column 'coordinates'                                                                                           
mathesar_service                |   return Table(  

[kgodey]

@hydrandt Thanks for reporting this! Are there any API calls returning errors? If so, could you provide request / response information?

[pavish]

This is a user reported issue, we need someone from the backend team to prioritize this and pick it up.
cc: @dmos62, @silentninja, @mathemancer, @Anish9901

I'm moving this out of 0.1.2 since there are several unknowns with the issue.

[notramo]

citext is an often used type, mostly used for e-mail addresses, as they are case-insensitive. It can be found in nearly every webapp which uses e-mail addresses. Could you prioritise this issue?

[notramo]

@pavish, @dmos62, @silentninja, @mathemancer, @Anish9901
This issue makes it impossible to use Mathesar to manage the database of a Lucky project, as the default migration creates a citext column for the e-mail address. Probably several other back-end frameworks are also affected by this.

I took a look at SQLAlchemy versions, and it seems that v2.0.7 added support for citext type. Mathesar uses the 1.x.x version of SQLAlchemy currently.

[silentninja]

@notramo I opened #2959 to add support for citext type as this issue reports other types to be missing too. Please use that issue to keep track of the updates to the problem you are facing.

[dmos62]

I presume that this issue is purely about adding support for these types. Does this indeed require work from the frontend?

[dmos62]

Reproduced by creating a table via

CREATE EXTENSION citext;
CREATE TABLE my_table (
  text_column CITEXT,
  point_column POINT
);
INSERT INTO my_table (text_column, point_column)
VALUES ('Row 1', '(1.23, 4.56)'),
       ('Row 2', '(7.89, 0.12)'),
       ('Row 3', '(3.45, 6.78)');

The table does not show up in the interface. The GET tables request fails with a 500. The stacktrace shows this if-else block raising the unhandled UnknownDbTypeId and thus panicing the Column serializer.

[dmos62]

#3040 added rudimentary support for "unknown types" (types that we've not hardcoded). See this comment for an in-depth explanation of what rudimentary means exactly. Basically, tables with "unknown types" shouldn't crash the backend anymore.

[notramo]

When can I expect a new release? That would make it possible to use this project.

[kgodey]

@notramo We'll be doing a new release in ~3 weeks.

[notramo]

Can I find nightly or CI build Docker containers somewhere?

[kgodey]

@notramo We pushed one for you and updated the install script in this branch, which you can install with:

bash <(curl -sfSL https://raw.githubusercontent.com/centerofci/mathesar/2023-06-27/install.sh)

Please test and let us know if you find any issues.

We'll also eventually be pushing develop Docker images to our Docker Hub, that work is tracked in #3076.