fix: correct connection pool management

[AndresPinerosZen]

Rationale

I'm investigating this issue because I’ve been encountering frequent "Connection Timeout" errors when connecting to a GCP Cloud SQL database.

Upon inspection, I found that the provider was creating and immediately closing hundreds of connections in rapid succession. GCP enforces a rate limit on the number of new connections that can be opened per user, so I believe I was consistently hitting that limit. Many other users have reported similar timeouts when working with GCP-hosted databases.

#572

#257

Problem 1

The first issue I identified is that calling db.SetMaxIdleConns(0) causes every resource to open and close a new connection through the dbRegistry connection pools.

The reasoning behind this setting is documented in the code comments:

// we don't keep opened connection in case of the db has to be dropped in the plan.

This makes sense because in PostgreSQL, a database can only be dropped if there are no active connections to it.

However, I found that the provider already supports performing a "FORCE" drop, which automatically terminates all active connections before dropping the database (available in PostgreSQL ≥ 13.0.0).

Therefore, when the target database supports forced drops, we can safely allow connection reuse by setting a non-zero value for MaxIdleConns. This prevents unnecessary connection churn while maintaining compatibility with database deletion operations.

Problem 2

After enabling connection reuse for resources that belong to the same logical database, I noticed that more connections than expected were being created. Also, the number of active connections would fluctuate unpredictably.

The root cause was that the provider builds DSN keys for its connection pool cache using unsorted Golang maps. Since map iteration order in Go is not deterministic, the generated DSNs varied across executions. As a result, multiple connection pools were being created for the same logical database.

By sorting the map keys before iteration, we ensure that DSN generation is deterministic, which guarantees that there is at most one connection pool per logical database.

[AndresPinerosZen]

@cyrilgdn Could you take a quick glance at this one and provide some feedback? It's a very small change that has a big impact for GCP users. Thanks.

[rogerfar]

@cyrilgdn could you have a look at this, especially in larger tf modules this is a big problem.