[ckan-dev] (OperationalError) could not send data to server: Connection timed out

[Alice Heaton]

> I did an investigation and the problem seems correlated to
> PostgreSql 9.1 that closes the connection not used after some time.
> I tried to set parameters for "sqlalchemy" connection pool, adding
> these setting into my production.ini:
>
> sqlalchemy.pool_recycle=3600
> sqlalchemy.pool_size=5
> sqlalchemy.max_overflow=10
> sqlalchemy.pool_timeout=30
>
> but the problem persists.
> The question is: are these setting used by sqlalchemy? There are
> other ways for passing these parameters?
> And most in particular: can this issue resolve my problem?

As of CKAN 2.2, the sqlalchemy settings are honored for the connection to the CKAN database, but not for connections to the datastore database. Using CKAN with the datstore plugin, CKAN will create three SQLAlchemy engines, each of which will manage it's own pool of connections:

- One from CKAN core to the CKAN database. This connection honours the settings in the .ini file, apart from the settings that would require non-textual parameters, such as 'pool' and 'poolclass'. This is actually an issue, as it means you cannot change the type of pool used;

- Two from the datastore plugin, one for read access and one for write access. Neither of those use the settings in the .ini file, they just use the SQLAlchemy defaults.

The problem with this is that it makes it unpractical to use an external pooler, such as pgbouncer, as it means you get two different, stacked, pools. For you problem (time outs) it might help, but for managing numbers of connections it's really not practical.

I guess you could have a plugin that modifies the type of pool used after the engines are created (might require creating a new engine?) though as far as I remember the datastore plugin doesn't really expose it's engine publicly.

That being said, in regards to your particular problem, I have not seen timeouts happen in that way, even after long idle times (days). Could it be a Postgres confiuration setting?

Best,
Alice Heaton