[META] Pre-existing PostgreSQL compatibility #3199
Description
We want to be able to connect Mathesar to an arbitrary pre-existing PostgreSQL database. However, a number of problems may arise when doing so. This meta-issue is to track work associated with these problems.
Versions
Here we track info about which versions of PostgreSQL work with Mathesar.
| Version | Install | Tests | Notes | Easy Fix |
|---|---|---|---|---|
| 9 | fail | N/A | ERROR: cannot cast type jsonb to integer at character 9193. Relevant portion of 0_msar.sql: line 247 WHEN jsonb_typeof(col)='number' THEN msar.get_column_name(rel_id, col::integer) |
maybe |
| 10 | fail | N/A | Same error as version 9 | maybe |
| 11 | fail | N/A | The pg_attribute table doesn't include attgenerated |
maybe |
| 12 | fail | N/A | It seems the rules for type coercion are more strict than in 13. In particular, regtype isn't automatically coerced to text. |
yes |
| 13 | pass | pass | This is the version we develop on | N/A |
| 14 | pass | fail | There seems to be some difference in default timestamp precision invalidating a test | yes |
| 15 | pass | fail | It seems like there's some difference in the error returned by unsupported type options | yes |
Primary key problems
Here we list some info about issues that arise with different primary key setups.
No Primary key
- Table visible
- Data visible
- Deletion not working
- claims success, does not delete anything
- Update not working
- Shows no error, but refreshing the data reverts all changes
- Record page not possible
- handled smoothly on table page; the button isn't there.
- From schema page, the 'find a record' button is there, but goes to a broken record chooser.
- This seems to lock up the UI, requiring CTRL+R to recover.
- Data explorer works surprisingly well
- More investigation needed
Multicol Primary key
- Console throwing errors
- Table header visible, but
- Not showing table data
- Inspector broken, not working
- From schema page, can click 'find a record'
- Somehow, the table data shows in this chooser
- Choosing a record navigates to a 'record' page that contains no data (but the record preview is shown somehow)
- Data explorer works surprisingly well
- More investigation needed
Why can one see the data in the record selector and data explorer, but not the main table page?
Non-generated primary key
- Table loads, data loads
- Can't insert record (can't edit pkey col, but it's null since it's non-generated)
- Can't update pkey value
- No data explorer issues found (or expected)
Most issues stem from our treatment of table records as resources in the RESTful sense
Non-sequential primary keys
- Seems to work fine as far as not breaking goes
- Slightly strange reordering of records on insert, since the new key is not the last in the order.
Foreign key problems
Multicolumn foreign key
- Not shown as fkey on front end
- Not handled when user enters invalid values; throws JSON blob error
- More research needed about 3rd-party referrers and referants.
Open Source Shakespeare set
This section lists issues found while tinkering with the Open Source Shakespeare data set.
- The paragraph table won't load records
- This seems to be an issue with the templating logic for the linked record previews
Composite type support
- Support is surprisingly good!
- Display is acceptable (shown as tuples)
- Input works (ish; requires user to be completely accurate)
- Filtering in table view doesn't work, breaks table view (DEFAULT in all values)
- Grouping in table view works, sort of
- Groups are created, but
- Headers are broken:
{object Object}
- Data explorer filtering also doesn't work
- Summarize and sort work
Scale
Many tables
10 tables
No issues found
100 tables
- UI is pretty wonky for this;
- card display is busy
- Loading / searching slightly slow, but usable
- Drop down makes it tedious to find desired table when used
500 tables
- Same UI issues as 100 more-or-less
- Slower by a lot
- Actions involving a reflection become tedious
1000 tables
- Unusable due to reflection performance
- No pagination? Only 500 tables are visible/usable from the UI, no way to get to others.
Many columns
- There are two things at issue: The total number of columns over all tables, and the max columns in a given table.
- App becomes slow after 1000(ish) columns total. I think this is because reflection happens for the whole database whenever it happens.
- Data explorer column selection screens become cumbersome, there's no way to filter column names
- Choosing columns in filtering, sorting, grouping cumbersome for same reason
- Only way to scroll sideways in record selector seems to be by tabbing through the search boxes
Below are tables with some test results. The method is to create X total columns, split over m tables of n columns each. Then load the tables main page for the schema, and look at the repsonse time of /api/db/v0/tables/. Choose a random table, click it, and load its table page. Observe the response time of /api/db/v0/tables/<id>/columns/. Create a new column for the table, observe the time it takes for the relevant POST request to return. Otherwise, click around and look for strangeness or breakages.
Single table
| # columns | .../tables/ |
.../columns/ |
add column | Notes |
|---|---|---|---|---|
| 30 | 0.2s | 2.5s | 0.5s | Works fine. Reasonable performance |
| 50 | 0.4s | 4.5s | 2.8s | Works okay, slower performance |
| 100 | 1.2s | 10.6s | 0.5s | single table page performance becoming a problem. No breakages |
| 250 | 10s | 55s | 0.6s | slow table page, slow schema page, no breakages |
| 500 | 75s | 363s | 1.5s | Slow schema page, unusably slow table page, no visible breakages (but it's hard to check) |
| 750 | 315s | 870s | ||
| 1000 |
Total columns: 1000
| # tables | # columns per table | .../tables/ |
.../columns/ |
add column | Notes |
|---|---|---|---|---|---|
| 20 | 50 | 6.5s | 4s | 1s | Reasonable, but not great performance. No breakages |
| 10 | 100 | 13s | 10.5s | 1.5s | Same, but slower |
| 5 | 200 | 27s | 36s | 1.8s | Sloooower. No breakages though |
| 2 | 500 | 116s | 370s | 2.1s | Unusably slow. No breakages |
Total columns: 1500
| # tables | # columns per table | .../tables/ |
.../columns/ |
add column | Notes |
|---|---|---|---|---|---|
| 30 | 50 | 9s | 4s | 2.5s | Filter/sort/group work, albeit slowly. No breakages found |
| 15 | 100 | 16s | 9s | 1.1s | Filter/sort/group work, slowly. No breakages |
| 10 | 150 | 30s | 22s | 2.8s | Same as above, but slower |
| 5 | 300 | 85s | 88s | 1.7s | Far slower; curiously, columns endpoint takes longer in single record view |
| 3 | 500 | 180s | 365s | 2.8s | Unusably slow, but no apparent breakages |
| 2 | 750 | 360s | 900s | 8.5s | Extemely slow. Not all columns showed on the single table or any dropdown involving columns, due to limiting to 500 |
Effect of DB Privileges on Mathesar
- Minimum required privileges for Mathesar to run is CONNECT and CREATE on the DB.
- Installation fails when the mathesar installation PG role does not have ownership/usage privileges on any existing schema owned by a different user other than the superuser. Refer error in comment.
- In every other case of privilege errors, we throw errors on the UI. The errors do not contain a valid error message.
Metadata
Metadata
Assignees
Labels
No labels