Server closed the connection unexpectedly for Postgres12

[MondeSinxi]

Describe the bug

When running long dbt runs (20 minutes or so) I get an error message that the server has closed the connection unexpectedly after building the last model. The on-run-end hooks also don't run after the last model is built. I'm using dbt to build tables/views for Postgresql 12.

All tables/views are created in the database and there are no errors reported for any of the models run.

This seems to have been fixed for Snowflake and Redshift in #657

Steps To Reproduce

A simple dbt run with the following at the on-run-end in the dbt_project.yml file.

on-run-end:
  - "{{ log_results(results) }}"

Expected behavior

I expect dbt to run and log results at the end of the run

Screenshots and log output

...
 42 of 42 OK created view model store.funds........ [CREATE VIEW in 0.84s]
Database error while running on-run-end
Encountered an error:
   Database Error
      server closed the connection unexpectedly
          This probably means the server terminated abnormally
          before or while processing the request.

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.14.2

The operating system you're using:

The output of python --version:

Python 2.7.13

Additional context

I'm running dbt in a Docker version 19.03.8

[drewbanin]

hey @MondeSinxi - thanks for opening this issue. It sounds like the connection to Postgres is being terminated after some relatively-long timeout. I don't think I've seen reports of this happening before on Postgres!

I do see that dbt is still using the master connection, but I could have sworn that we fixed this for all plugins, not just Redshift and Snowflake as you noted above. The fix here would either be to:

• open a new connection for the on-run-end hooks, or
• refresh the connection before running queries in the on-run-end hooks

Do you happen to know if you have any Postgres settings idle_in_transaction_session_timeout or statement_timeout enabled for your user / database in Postgres?

[MondeSinxi]

Hi @drewbanin, sorry for waiting so long before replying. I've just checked my postgresql.conf file that contains all the default configurations and the defaults for both idle_in_transaction_session_timeout and statement_timeout are set to zero. I've changed these to 100000 milliseconds, run dbt and it still ran with the same error.

Regarding your suggestions above how would I open a new connection for executing the on-run-end hook? I have not bumped into a 'refresh connection' type of command

Would this be a model that would call the macros I want to run at the end?

[fabiopaiva]

I'm having the same problem, any update on this?

[thuyetbao]

I have the same error with this

The screenshot of the problem is here

Describe:
The task is taking a long time and the adapter (in local or cloud) is closed, they release the CPU in the database instance (the runner of this) but they didn't receive any new task [after closed].
So at the bottom, the machine run [cloud, local] is still waiting for the database instance response but this is not any run > they SKIP the long run and returned

My expectation:
After the disconnection, the dbt still checks the remaining queries to the target database instance and retries it.
Or at least flags this errors for developers to know about this issuses

More Information:

• Run as Local, Cloud
• Database Instance: Postgres 12
• Profiles Setup:

<profile>: # run in local  # don't change
  target: dev # don't change
  outputs: # don't change
    dev: # don't change
      type: postgres # don't change
      host: dbname.region.service.provider.com # change here
      port: 5432 # don't change
      user: username # change here
      pass: password # change here
      dbname: dbname # change here
      schema: target_schema # change here
      threads: 8 # don't change
      connect_timeout: 180 # don't change, in second.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[giacomochiarella]

having the same issue. I've set keepalives_idle: 10 and (I don't know if this option is available in the profiles.yml) keepalives_interval: 20 but still gives the same error

[rm-minus-r-star]

This is a chronic problem for me. From one identical dbt run to the next it can run flawlessly, or fail, sometimes quickly, and sometimes only after hanging for ages midway with
20:53:15 Database Error in XXX (SSS/XXX)
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Doubly irritating is that a failure still qualifies as success when running out of a shell script (within a container), and so far I haven't been able implement a re-run or abort of the sequence as a work-around.

I've tried all manner of profile settings following every variation I could find on stack overflow etc. I'm guessing between myself and countless others, we would have hit on the magic configuration by now if connect_timeout, keep_alives_idle etc were the solution.

[mlcruz]

Same problem here, happens a lot, mostly when we are doing full builds of incremental views or something that takes longer. After like 300s it fails pretty much always.