Thanks for sharing the tip! What adjustments exactly did you have to make? I just make every user to log on with different PG username. Are there any other options?
It's fairly unusual to have a tomcat application of any size login to the db as the user. Could you share the reason why ?
The app is actually middleware for Adobe Flex frontend and PG backend, not a regular web app. The architecture requires PG to know which user has connected (lots of heavy lifting takes place in PG), and we so far havent found any other way how to let PG know which user has connected. The only alternative was to supply user ID in every PG function call but that is messy and introduces it's own limitations as well. If you have any suggestions I'm all ears! ;)
Set a user variable after you've obtained a connection from the pool, and use that to log user-specific values. That way, you maintain the benefits of connection pools, but can still identify individual users.
It would seem to me that if you need to scale this app then you are going to have to set the user in the application somewhere. Having all of the users connect as themselves doesn't lend itself to being scalable.
