Zero-downtime schema migrations in Postgres using Reshape

Re:Zero-downtime schema migrations in Postgres using Reshape by fabianlindfors on Friday January 07, 2022 @03:45AM (#29836655)

Postgres has something called updatable views so INSERTs, UPDATEs, DELETEs can be made against views just as if they were actual tables. For the example in the post, they work differently depending on if the insert is made against the new or old schema:

- For old schema (i.e. full_name is set): full_name is split in two, the first part is assigned to first_name and the second to last_name. This is controlled by the "up" setting in the migration.

- For new schema (i.e. first_name and last_name is set): full_name is set to first_name and last_name concatenated with a space. This is controlled by the "down" setting in the migration.

[ Parent | on HN ]

Re:Zero-downtime schema migrations in Postgres using Reshape by tobias_irmer on Friday January 07, 2022 @03:55AM (#29836739)

I haven't used that, yet. But this has me confused:

"An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column of the underlying base relation; otherwise the column is read-only, and an error will be raised if an INSERT or UPDATE statement attempts to assign a value to it."

Isn't splitting full_name into two columns a complex reference (for lack of a better word)?

[ Parent | on HN ]

Re:Zero-downtime schema migrations in Postgres using Reshape by fabianlindfors on Friday January 07, 2022 @03:58AM (#29836767)

Actually, no! That's because from the perspective of the view, it's simply directly referencing a table and some columns, it doesn't handle any of the updating.

What Reshape does is that it sets up triggers for the underlying table. When an insert is made, the trigger runs and updates the other columns directly on the table, which has no effect on the view.

[ Parent | on HN ]