Migrate: statement timeout option for postgres

[roopakv]

Is your feature request related to a problem? Please describe.
We recently ran into an issue where there was no statement timeout in a migration. This caused a down time.

We would like to specify a timeout and say that no migration statement should run for more than ~30 seconds say.

Describe the solution you'd like
Add postgres statement timeout to the connection session.

Describe alternatives you've considered
Adding statement timeout to each migration. (hard since we need every engineer to remember this during reviews and writing migrations)

Additional context

[psuresh309]

This would be very useful. However, seems like the library is structured for the drivers to be dumb and the core driver does the heavy lifting. In this case, different DB's and their corresponding drivers may have different ways to support per statement timeout. We'll need to come up with a solution that works across the board. Unless we want to allow per DB driver options. Open to suggestions here.

[dhui]

This is doable with a modification to the migrate postgres driver. e.g. Use context.WithTimeout() and a new x-statement-timeout option to configure the timeout

With a new driver interface, we can make configurable timeouts available for all drivers (DB and source).

[psuresh309]

what time frame are we talking about here? We have an urgent need for statement_timeouts and have a way around it but would like the better solution which is this :)

[dhui]

Feel free to open a PR to add this for postgres

[psuresh39]

@dhui Also context.WithTimeout() would not work because just telling the worker to abandon it's blocking work will not necessarily stop the query on Postgres. We need to make sure Postgres abandons the query gracefully too. What we instead need is a way to optionally append SET statement_timeout TO X to any migration. We can still use a new x-statement-timeout option to configure the timeout. Let me know if that makes sense.

[dhui]

@coolnay309 What you said makes sense

However, I'm wary about adding SET statement_timeout TO ... to migrate since I expect this issue to be handled by pq. Let's only make the context.Context change for now and see if that works.

Using context.Context _should_ work since pq will attempt to cancel the request.
Details about cancel a request in postgres:

• https://www.postgresql.org/docs/12/protocol-message-formats.html
• https://www.postgresql.org/docs/12/libpq-cancel.html

[psuresh39]

This is great. Let me give context a go and test it out.

[psuresh309]

@dhui can i get access to the repo I am getting 403's. github username: coolnay309

[psuresh39]

@dhui can i get access to the repo I am getting 403's. github username: coolnay309

realized I could just fork

[dhui]

@dhui can i get access to the repo I am getting 403's. github username: coolnay309

realized I could just fork

Yep, most open source projects follow the forking workflow

[psuresh309]

This has been merged into the main line. @dhui do you have the next release date?

[dhui]

Just cut a new release w/ your changes: v4.9.0