5 ms·

Is there any migration scenario that you anticipate your view strategy won't be able to handle? Maybe add a "Limitations" section? Again..congrats. I am impress

by jfbaro 5y ago

Is there any migration scenario that you anticipate your view strategy won't be able to handle? Maybe add a "Limitations" section? Again..congrats. I am impressed and I hope someday this becomes part of PostgreSQL native feature set.

fabianlindfors 5y ago
That's a great question. I don't anticipate the view part being very limiting, Postgres seems to have great support for updatable views which means they should be able to transparently take the place of tables in most situations. My previous posts's comments here on HN have some details on the limitations of views: https://news.ycombinator.com/item?id=27531934 https://news.ycombinator.com/item?id=27531934. The biggest limitations will probably come down to what changes can actually be automated. There might just be some migrations that require a human touch. I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk I should probably add a limitations section, good idea! There are a ton of limitations now given that Reshape is still experimental. Foreign keys, for example, probably don't work well as I currently just ignore them. Thanks again! I don't know any database which has nice schema migrations as a built-in feature but if you ask me, they should!
- magicalhippo 5y ago
  > I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk We have migrated a few fields from varchar to integer. How would your solution deal with this? And of course in some cases there will be data that requires manual handling. Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
  - fabianlindfors 5y ago
    > We have migrated a few fields from varchar to integer. How would your solution deal with this There is actually an example in the README on how to change a column from TEXT to INTEGER, the technique would be the same the other way around: https://github.com/fabianlindfors/reshape#alter-column https://github.com/fabianlindfors/reshape#alter-column For the cases that require manual handling, that is a bit tricky. I'm not sure Reshape would be able to automate that in any meaningful way so the best thing might just be to fall back to a standard procedure of making the changes in two separate migrations, where the manual changes are done in between. > Another one is adding foreign keys where the existing data does not conform to the foreign key constraint. I have given this some thought before as I wanted to add a migration which can add new foreign keys. I think it can be done by writing some migrations first which update the existing data to conform to the constraint, for example adding missing values. This can be done with `alter_column` right now and there will be more comprehensive migrations for data changes in the future.
    - magicalhippo 5y ago
      > There is actually an example in the README on how to change a column from TEXT to INTEGER 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.
      - fabianlindfors 5y ago
        > 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.
        magicalhippo 5y ago
        > 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!

by jfbaro 5y ago

fabianlindfors 5y ago

That's a great question. I don't anticipate the view part being very limiting, Postgres seems to have great support for updatable views which means they should be able to transparently take the place of tables in most situations. My previous posts's comments here on HN have some details on the limitations of views: https://news.ycombinator.com/item?id=27531934 https://news.ycombinator.com/item?id=27531934. The biggest limitations will probably come down to what changes can actually be automated. There might just be some migrations that require a human touch. I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk I should probably add a limitations section, good idea! There are a ton of limitations now given that Reshape is still experimental. Foreign keys, for example, probably don't work well as I currently just ignore them. Thanks again! I don't know any database which has nice schema migrations as a built-in feature but if you ask me, they should!

magicalhippo 5y ago
> I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk We have migrated a few fields from varchar to integer. How would your solution deal with this? And of course in some cases there will be data that requires manual handling. Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
- fabianlindfors 5y ago
  > We have migrated a few fields from varchar to integer. How would your solution deal with this There is actually an example in the README on how to change a column from TEXT to INTEGER, the technique would be the same the other way around: https://github.com/fabianlindfors/reshape#alter-column https://github.com/fabianlindfors/reshape#alter-column For the cases that require manual handling, that is a bit tricky. I'm not sure Reshape would be able to automate that in any meaningful way so the best thing might just be to fall back to a standard procedure of making the changes in two separate migrations, where the manual changes are done in between. > Another one is adding foreign keys where the existing data does not conform to the foreign key constraint. I have given this some thought before as I wanted to add a migration which can add new foreign keys. I think it can be done by writing some migrations first which update the existing data to conform to the constraint, for example adding missing values. This can be done with `alter_column` right now and there will be more comprehensive migrations for data changes in the future.
  - magicalhippo 5y ago
    > There is actually an example in the README on how to change a column from TEXT to INTEGER 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.
    - fabianlindfors 5y ago
      > 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.
      - magicalhippo 5y ago
        > 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!