[CT-2958] [Feature] Cancel open connections on SIGTERM (in addition to SIGINT)

[rchui]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

When running with an orchestrator like Airflow, users can set DAG timeouts that will issue SIGTERM to running processes. The SIGTERM does not signal to DBT to cancel a running query like SIGINT will and will cause the query to continue running even if it has exceeded the Airflow specified timeout.

This can cause a thundering herd situation when you have an incremental model that runs very frequently. The task will timeout killing the DBT process, restart and run the query again with the old one running. On and on and on.

It would be useful to specify a timeout for a Postgres query to be killed if it exceeds a certain amount of time. This can be achieved by injecting a SQL header to set statement_timeout like:

"SET statement_timeout TO '15min' ; "

This could be configured globally in profiles.yml or as a model property

type: postgres
...
execution_timeout: 15min

{{
  config(
    execution_timeout='15min'
  )
}}

Describe alternatives you've considered

• Handle SIGTERM correctly and treat it like SIGINT. Upon receiving a SIGTERM cancel running queries and exit gracefully

Who will this benefit?

Anyone who uses a Postgres data base and an external orchestrator that allows setting timeouts external to DBT.

Are you interested in contributing this feature?

No response

Anything else?

No response