Zero-downtime schema migrations in Postgres using Reshape

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.

[ Parent | on HN ]

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!

[ Parent | on HN ]