search

stripe / pg-schema-diff

Go library for diffing Postgres schemas and generating SQL migrations
MIT License
278 stars 20 forks source link

Support FK's switching between non-castable types #51

Open bplunkett-stripe opened 11 months ago

bplunkett-stripe commented 11 months ago

Currently, if switching between incompatible types with a FK, plan validation will error. We need to identify if types are incompatible and drop and re-add the FK

This is probably a pretty rare operation , since the only incompatible type switches we currently support is BIGINT to TIMESTAMP

sidyakinian commented 10 months ago

I'd like to work on this issue!

bplunkett-stripe commented 10 months ago

Sweet! This shouldn't be too much code, but it does get into the nitty gritty of the internal of the library.

There are couple parts to this:

  1. Identifying if types are castable. I think your best bet is to fetch casts from the systems catalog, but you should validate this is true
  2. I think all you need to do is check if the underlying column(s) of the FK are changing between non-castable types. You shouldn't need to check the foreign table, since the data type changes should inherently be identical. If they are non-castable, return requiresRecreate=true from this func

This ticket airs a bit more on the tricky side, so definitely let me know if you have any questions

sidyakinian commented 9 months ago

@bplunkett-stripe Hello, just made this draft PR: https://github.com/stripe/pg-schema-diff/pull/73. It only handles bigint to timestamp for now and the logic should probably be structured a bit differently, but please let me know if I'm going in the right direction! Sorry this took a while, didn't work a lot with Go and SQL so had a bit of a learning curve :)