Обсуждение: We find few queries running three times simultaneously with sameparameters on postgres db

Am 26.06.2018 um 12:19 schrieb amandeep singh:
> We have been observing our postgres database from past few days,We 
> found few queries running three times simultaneously with same 
> parameters.I would like to back track how a query is running multiple 
> times.

they are independend each other. You can run those queries with EXPLAIN 
ANALYSE to see the execution plan.

Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Em 26/06/2018 08:49, Saurabh Agrawal escreveu:
>
>
>     Am 26.06.2018 um 12:19 schrieb amandeep singh:
>
>         We have been observing our postgres database from past few
>         days,We found few queries running three times simultaneously
>         with same parameters.I would like to back track how a query is
>         running multiple times.
>
>
> Can you check the requests made by your application/ ORM? This looks 
> like application is making multiple requests, rather than something 
> happening on the database?
>

I agree with Saurabh Agrawal.
Sometimes, users just double (or triple) click a form button, and then 
resulting activity is executed more than once...
What I used is to deactivate buttons after first click. This is even 
more noticeable when working on intranet apps ou high speed internet 
connections.

Users, sometimes, need to be educated that one click is enough :-)

Regards,

Edson.

On 26 June 2018 12:32:44 CEST, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:
>
>
>Am 26.06.2018 um 12:19 schrieb amandeep singh:
>> We have been observing our postgres database from past few days,We
>> found few queries running three times simultaneously with same
>> parameters.I would like to back track how a query is running multiple
>
>> times.
>
>they are independend each other. You can run those queries with EXPLAIN
>
>ANALYSE to see the execution plan.
>
>Regards, Andreas

seems like i misunderstud your query. are you sure, that the parameter $1 is equal across all different connections?

Andreas


--
2ndQuadrant - The PostgreSQL Support Company

Em 26/06/2018 14:26, amandeep singh escreveu:
> Hi Andreas
>
> The value for $1 is same in all queries.
>
> @Edson: number of such running queries are always  3.

I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with 
EclipseLink, didn't test with Hibernate):

1) In persistence.xml, disable all caches (this is very important for 
make reproducible)
2) Load three Person records that live on same city (objects Person and 
City mapped to tables Person and City, being city an attribute of Person 
object):

TypedQuery<Person> qry = em.createQuery("select P from Person P where 
P.city.name = :cityname");
qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List<Person> rs = qry.getResultList();

3) This will cause one query against Person table, and exactly 3 queries 
issued with same parameters to City table.

I really don't know how your code works, and is quite hard to guess, but 
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each 
City key - so, I'll have one query for Person and one query for  only.

Hope this helps to enlighten your problem.

:-)

Regards,

Edson