I made a new release of a module, and soon afterwards an issue was filed that the hook_update_N() used SQL that didn't work on PostgreSQL. In this situation I can see two solutions:
(Let's say the release with the problem is 1.1 and that N=6100.)
Modify update_6100 to have a fixed query and release as 1.2. If a user with Postgres updated to 1.1, got the error, and then updated to 1.2 they still wouldn't have run the query as it wasn't put in a new hook_update_N(). However they should've seen the error when doing the update, and the issue's in the issue queue, and can include instructions on how to fix the problem. It feels ugly to modify an existing (released) update function though.
Add update_6101 that has the fixed query. In the case of this particular query however, it would be destructive to run it after the user's started using some of the new functionality. So if a user with MySQL updated to 1.1, used the module a lot, and then updated to 1.2 there would be a good chance they'd lose data. I could check what DB's being used in update_6101 so that it would only be run for users with Postgres, but that feels ugly too. Also if someone with Postgres ran update_6100, saw the errors and then fixed and ran the query manually, they also could lose data when updating to 1.2.
Note that there's no way to detect if the query has run successfully, or has never been run.
I'd like to know what people think is the best way to deal with this situation?