But your background might be sufficient for you to answer my question (and perhaps confirm my suspicions). We use PostgreSQL Plus Advanced Server from EnterpriseDB. If you're unfamiliar with that, it's a commercial product that has an "Oracle Compatibility Layer" on top of PostgreSQL. Another group where I work uses SAP Data Services to populate some databases they've created. The thing is, when these data flows start, they immediately open about 25 connections each and since the "job" contains many of these flows, they quickly eat all the connections available to the PostgreSQL instance. I'm told that SAP has confirmed that Data Services does "a poor job of handling connections" and having connections opened, used and then left open is not unexpected. We tried using PgBouncer to limit how many connections Data Services uses, but the jobs refused to start (we set the pool as high as 50 and apparently Data Services needs around 120 connections for these jobs, even though their use appears to be sequential and the product will not close a connection after its finished with it - so Data Services just hangs if you limit connections either via a connection pooler or in the database instance itself). SAP/BO's support for PostgreSQL is lackluster at best. I know the reporting service (what used to be BO Enterprise, Infoview, etc. but its named something else now) does not fully support PostgreSQL and a VP there shortly before the SAP acquisition told us in a telephone conference to use MS Sql Server. But our CIO will not switch to that product and will simultaneously not switch to another BI tool - or ETL tool for that matter.
I don't know if Data Services' problem with connection handling is limited to PostgreSQL, but I think it must be because I cannot imagine anyone would use the product if even simple ETL flows required hundreds of simultaneous connections. I'm also not convinced that the Data Services authors haven't gotten something completely wrong. I posted here because I thought if you had experience with these tools, you might know something about the purported "connection handling problems". Chiefly, I'm curious to know if we're having this problem because...
1) Our authors don't know what they're doing
2) We're using a backend database that SAP supports only marginally
3) Data Services really does require hundreds of concurrent connections for even simple tasks.
Or a combination of all of those. If you have an opinion on it, I'd be interested in hearing it.
Thanks in advance.