Simplest query takes 30+ seconds when move from anorm to postgresql-async

[tpraizler]

Hey,

I am not sure if this is the right place for this, but I am hopeless. need your help!
I am trying to move my app to postgresql-async from anorm, and encountered a major problem on the first refactor.
I changed one of my daos, to be fully async using postgresql-async.

This is a message I get from postgres:
[PURPLE] duration: 41353.017 ms execute 1/1: DELETE FROM audit_log WHERE company_id = $1 Detail: parameters: $1 = '30'

This delete is basically doing nothing, I tested it on an empty set.
With anorm it work perfectly, but when move to postgresql-async, it takes 30 -40 seconds.
This is even when there is nothing to delete.

I configured the connection as follow:

val url = configuration.getString("db.default.url").get
val asyncConf = URLParser.parse(url)
val factory = new PostgreSQLConnectionFactory(asyncConf.copy(ssl = SSLConfiguration(SSLConfiguration.Mode.Require)))
val poolConfiguration = PoolConfiguration.Default.copy(maxObjects = 10, maxQueueSize = 50)
new ConnectionPool(factory, poolConfiguration)

Do have an idea what is missing?
Have you encounter such behavior?
Do you have any suggestion to how to approch this?

Thank you very much!

[mauricio]

@tpraizler can you provide a self contained example that shows this behavior? without an example database it's really hard to understand what's going on.

[tpraizler]

I am working on it. creating a repo and application on heroku for this.
Will update once I have all the details.

Just to make sure. what do you need?
The code, and also access to the database?

[tpraizler]

@mauricio So I manage to focus the problem a little (can't believe I just tested it now).
Looks like the regular query work perfectly, but the prepared statement is the problematic one.
Does this help? any idea what's missing?

[mauricio]

@tpraizler there shouldn't be an issue with prepared statements, were you able to setup a database that fails to execute it? A simple .sql file with code that breaks would be really useful.

[tpraizler]

@mauricio ok. problem solved. but maybe with your help I will understand the logic behind it.
It looks like running a ANALYZE VACUUM on this table, fixed the problem.
I am not a db expert so it took a while to find that this was the issue.

My question is why prepared statement got stuck while regular query worked perfectly.
Is this related to the way you set a prepared statement?

Thanks!

[mauricio]

@tpraizler shouldn't be, there's only one way to setup prepared statements on a table so anything that sets up prepared statements should cause the same issue to happen.

[gabfssilva]

Probably this is caused because of the numeric type mapping. This PR fixes the issue: #231.

@mauricio, can you merge this PR?

[oshai]

I fixed/reverted this in jasync-sql(fork) 0.8.30: jasync-sql/jasync-sql#16 . more details in the issue: jasync-sql/jasync-sql#15