Re:Zero-downtime schema migrations in Postgres using Reshape
by fabianlindfors on Friday January 07, 2022 @09:35AM
(#29839538)
> I'm not very familiar with PostgreSQL, how do you handle the casts during inserts/updates?
In the example, you can see "up" and "down" settings on the migration. These are SQL expressions which specify how to convert between the old and new schema, in this case casting from TEXT to INTEGER and back. So in this case we have:
up = "CAST(reference AS TEXT)" down = "CAST(reference AS INTEGER)"
which is just a standard Postgres function for casting.
> In our case we just delete the offending values if the constraint has "on cascade delete", which most do.
Interesting! I might add some options to a future `add_foreign_key` migration which can achieve behavior like this automatically.
Re:Zero-downtime schema migrations in Postgres using Reshape
by magicalhippo on Friday January 07, 2022 @10:03AM
(#29839877)
> These are SQL expressions which specify how to convert between the old and new schema
Right, I get how that would work with a read-only view, I just didn't know you could do updates against such a view. Nifty!
I'm not very familiar with PostgreSQL, how do you handle the casts during inserts/updates?
> For the cases that require manual handling, that is a bit tricky.
Yeah in our case we just blocked the schema change and complained. Our support team would then take a look, possibly escalating to development. Not often such a change is done though.
> for example adding missing values
In our case we just delete the offending values if the constraint has "on cascade delete", which most do. We do take a backup of the database before doing schema changes though.