max_connections does not work

[vadim-a-nesterov]

It looks like max_connections is not properly implemented and it does limit concurrent connections from provider to a database, and if there is a limit for user for number of connections

you get several

  │ Error: could not start transaction: read tcp 10.100.16.37:52266->172.16.84.24:6432: read: connection reset by peer
  │ 
  │   with postgresql_grant.user_privileges["proceficator_user_proceficator_schema_usage"],
  │   on users.tf line 56, in resource "postgresql_grant" "user_privileges":
  │   56: resource "postgresql_grant" "user_privileges" {

[AndresPinerosZen]

Also seeing this behavior. The provider tries to establish so many connections so quickly during the refresh process that it overwhelms the DB server.

[AndresPinerosZen]

I'm seeing hundreds of connections that open and close very quickly. I see that the DSN Registry should be reusing connections based on database name, but it doesn't look like that's working as expected. I don't have hundreds of databases, I'm dealing with 9 logical dbs right now. I am creating quite a bit of resources in each logical db though.

I think that regardless of the connection pool, this is forcing the pool to close the connections when they become idle:

terraform-provider-postgresql/postgresql/config.go

Line 311 in 8cbf5c7

db.SetMaxIdleConns(0)

So even if there's a pool per database, I think what happens is that the max_idle=0 tells any connection to be terminated as soon as its operation completes. So the provider has pools, but not really. This is just my theory of what's happening, it could be something else.

max_idle=0 does make sense based on the explanation in the comments. DBs can be terminated and those connections could be impacted. Maybe retrying the connection in case of a connection failure could help in that scenario instead. Or validating if the connection is healthy before using it? Maybe max_idle=0 was set for very specific scenarios that wouldn't be covered by the strategies I'm suggesting.

[AndresPinerosZen]

Compiled the provider and tested with:

db.SetMaxIdleConns(2)

It made a really big difference. From ~250 connections to ~25.