API for editing records within a view

[kgodey]

Problem

Users may want to edit data in views.

Proposed solution

If a view is updatable, we want to support record creates, updates, and deletes via the /api/v0/views/<id>/records/ API. This should function similarly to the table records API.

If a view is not updatable, the records API should be read only. We should return whether a view is updatable in the top-level /api/v0/views/ API.

Additional context

• Information about Postgres updatable views.
• Blocked by API for reading views #467
• Support for editing records within a view #456
• Please spec out this API before implementation and get feedback from @kgodey and @mathemancer.
• Mathesar's API Standards

[mathemancer]

We need to work on a good definition of "updatable" for views. For example, an "updatable" view would necessarily:

• contain all non-nullable columns from any involved underlying tables,
• contain only one copy of each contained column (or we'd need to somehow link those columns together so they can only be updated as one),
• Contain only raw selects from underlying tables (or bijections from values resulting from underlying selects, but I suggest we avoid those for the time being). For example, you can't update a view that aggregates column values.

[kgodey]

@mathemancer does the definition of updatable in the tutorial I linked to in the main issue work?

[mathemancer]

@mathemancer does the definition of updatable in the tutorial I linked to in the main issue work?

We could start there, but we'll need to add to the functionality eventually to get as many views as possible to be updatable. The PostgreSQL definition is pretty conservative.

We should also probably check on nullable columns, and duplicate columns, since these aren't shown in the definition and they create problems that make the view impossible to write to (in the former case) and give the possibility of trying to make impossible writes for logical consistency reasons (in the latter case).

BTW, here's the link to the page from the PostgreSQL docs that was cut-and-pasted by that infernal tutorial site: https://www.postgresql.org/docs/13/sql-createview.html

Finally, PostgreSQL doesn't actually mark the view as updatable in any visible way, so we'll need to make some checks ourselves regardless if we want to be able to know whether the view is updatable before trying it.

[kgodey]

@mathemancer Thanks for the link, I've updated the link in the issue description. I did look for the information in the Postgres docs but apparently not very well.

For the scope of this issue, let's use the PostgreSQL definition. I'll create a separate issue for making more views updatable. I'm not sure if it's worth doing for the MVP yet, since there will be significant complexity involved.

[mathemancer]

I did look for the information in the Postgres docs but apparently not very well.

Unfortunately, the PostgreSQL docs' SEO is not as strong as the tutorial site you linked. That site seems to wash the relevant docs page out of the first page of google results pretty regularly for me.

[github-actions]

This issue has not been updated in 90 days and is being marked as stale.

[kgodey]

I'm closing this issue. Backend work will be tracked in this issue once design work has been completed:

• Support for editing records within a view #456