Improve or clarify connection pool handling

[iby]

@porsager Taking conversation in #206 separate.

I've run into an issue with integration tests started failing with PostgresError: relation "…" does not exist error. This happened on every 10th query. What's interesting, after the error the next 9 queries ran fine and then the same thing would happen with 10th. I'm on v2.0.0-beta.6.

After reading closely about the connection pool I fixed it with increasing max (from default 10) and setting idle_timeout=0.001. Two things I found odd:

1. Query connection doesn't get disposed right away and it doesn't get reused for consecutive queries. What's the purpose keeping it around then?
2. idle_timeout=0 doesn't work because of the falsy check, but I'd expect it to act as an indicator that already used query connections should get disposed right away.

Hence, it's not clear what's the right way to set up the connection in order to run an unlimited number of consecutive queries. Even with the current setup it seems I'm relying on idle_timeout clearing connections. I understand that I probably will never hit the limit with such a timeout, but it feels like a race to me. Can you elaborate on this?

P.S. Thanks for this awesome package! Absolutely fantastic! ❤️

[porsager]

Hi @iby

Thanks for the kind words and good description of your issue.

That error seems odd, and the fix even more so. I would guess you are running in to race conditions because you're running some things in parallel, so some query is executed before the relation it's referring is created?

1. You're right, in most cases, the default of no idle timeout might not be the best choice, but it very much depends on your usage. If you have bursts of requests it's nice to have idle connections ready to handle a queue of requests rather than having to start a connection first.

2. Yes - It makes much more sense to have idle_timeout = 0 work as you expected.

Are you perhaps starting/accepting queries in your integration tests before your initial migration has finished running? In that case I would run with default settings, and isolate the creation and not accept other queries until that is done.

[iby]

Sorry for the lagged reply. This is pretty interesting indeed. I tried to isolate it and couldn't reproduce with synthetic tests. I create a separate database for each test and all test setups are awaited. Pretty confident the issue had nothing to do with that.

However, I had some unrelated issues recently with unwaited test teardowns, which is no longer the case. And now I also can't reproduce the above issue with removed customized connection configuration… 🤷‍♂️ Gonna close the issue and will report if something interesting comes up.