Project update: 2023-07 backend fixes

[Dominykas Mostauskis]

Project wiki link.

- Discovered that Cannot import large csv tables · Issue #2995 · centerofci/mathesar largely requires frontend work (for this cycle at least); this comment is me asking Sean to take over the frontend aspect, which he agreed to; he'll be posting an issue for that; a summary of what we found is that a wholesome solution to this problem requires a lot of UX, frontend and backend work and we need a quick bandaid fix right now, which will only require frontend work

- Explored the unknown types being unsupported issue; how easy this will be to solve is still unclear, but it's likely that the first iteration of this support will simply have Mathesar not crash when a table has an unknown type, but it will not be able to tell the user what type that actually is (only that it is unknown); afaict, to know what those unknown types are will require additional Postgres queries and it *might* be challenging to manage the performance impact of that, which is why I'd prefer to do this iteratively (make Mathesar not crash first, then implement inspection of unknown types).

[Sean Colsen]

This is not my project, but I’d like to contribute some info to this week’s update:

I’ve been working on making column type inference optional, which is part of this project. I expect this work to be limited to the front end only.

We’ve made the following progress in the past week:

• In discussing the various ways in which we could potentially improve column type inference, we’ve agreed to prioritize optional inference.
• In pursuit of making inference optional, we’ve discussed and agreed to an approach of adding a new question on the first step of the import process.
• I’ve been working to implement those changes, but it does require some refactoring.
• As of yesterday, I’ve paused my work on the Frontend Fixes project so that I can focus my time on making column type inference optional.
• I expect “optional inference” to take me a few more days at least, because it’s unfortunately not a super simple change to make.

​

[Dominykas Mostauskis]

Thanks for the update on column type inference UI, Sean!

Summary of last week's work (I wasn't there to post an update on Friday):

- Submitted a PR for unknown types being unsupported, which doesn't provide full support, but downgrades this issue from a high-priority blocker to a medium-priority UX problem: unknown types don't cause backend panics anymore, but all of them are lumped under the same identifier and thus cannot be differentiated by the frontend: something to fix in the future, potentially in this cycle;

- Saw merged a community member's a PR for explaining permission requirements for the `gunicorn` user (which was part of this project);

- Discovered a few bugs: #3066, #3067;

- Began exploring allowing users to define dynamic defaults: see related user feature request and discussion with Brent and Kriti;

- Began exploring improving backend error messages.

[Dominykas Mostauskis]

Over the last two weeks,

- merged rudimentary support for unknown types (#3040);
  - which also made supporting point (#3007) and citext (#2959) types significantly less important;
  - created an issue to track further improving the support of unknown types (#3095);
- discovered that doing dynamic defaults is not within the time constraints of this cycle (and possibly this project):
  - instead of implementing dynamic default support, outlined an implementation plan (#3128):
  - after discussions with Brent and Kriti, decided on the initial dynamic default support being achieved via a whitelist of allowed dynamic defaults that a user can choose from; we deemed allowing arbitrary SQL as defaults to be too insecure with our current setup;
- submitted for review fixing database list showing all databases in demo mode (#3129);
  - also found that our `demo/README.md` didn't have all of the necessary instructions to run in demo mode and expanded them.

[Kriti Godey]

What is the state of this project overall compared to the original plan?

[Dominykas Mostauskis]

At the start of the cycle we settled on a task list consisting of 4 tasks that seemed of medium-complexity, which I termed "block-prone" tasks, and 5 tasks that seemed simpler, which I termed "block-proof" tasks.

Out of the 4 medium-complexity block-prone tasks, 2 were completed, and 2 were not. Of the 2 that were not completed, 1 was discovered to be significantly greater in scope than initially thought (allowing dynamic defaults) and instead of being implemented, its backend-aspect was spec'ed out to a considerable extent. I didn't have time to complete the remaining 1 task (improving backend error messages).

Out of the 5 simpler block-proof tasks, 2 were completed and 3 were not. Out of the 2 that were completed, 1 is still in review (demo users should see only their db). Out of the 3 that were not completed, 2 have been downgraded in importance: they were part of a different approach to solving the unknown types bug: when it was solved in a more general way, these 2 issues became nice-to-haves. I didn't have time to complete these 2 nice-to-haves and the 1 remaining task (uuid search bug).

In summary:

Completed:

- Fix API handling of multi-column UNIQUE and PK columns #2245

- Support unknown Postgres types #3024

- Demo users should only see their own database #2983
- Make permissions requirements clear for gunicorn user #3013

Discovered to be too big and spec'ed out:
- Allow whitelisted dynamic defaults #3128

Downgraded in importance:
- Add support for citext column type #2959
- Add support for point column type #3007

Unfinished:
- Incorporate field name into error messages when relevant #1370
- StatementError when using the Record Selector to search by a partially-entered UUID #2724

[Kriti Godey]

Thanks for the update!