Re: query locks up when run concurrently

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> schrieb am Do., 24. Nov. 2016 um
22:34 Uhr:

> On 11/24/2016 01:23 PM, azhwkd wrote:
> > It should not be possible because a group does not return to the
> > update pool before the update hasn't finished.
>
> So what is this 'update pool' and what is driving/using it?
>
> In other words how is the determination of the parameters done?
>
> To be more specific, the implication is that a group id can be reused so
> what determines that?
>

The application is written in go. Every group ID has its own go routine and
the routine is blocked until the SQL statement returns.
The update process starts with a check to an external API endpoint and if
there is new data available the routine is downloading it, parsing it and
inserting the data into 2 tables. Once that is done, the routine continues
to execute the statement in question using the data it inserted before for
the calculation. Only once this finishes will the routine start over again.

>
> > I watched the queries in a postgres client and there was no overlap I
> could see.
>
> Was this a visual inspection or did you dump the results of the various
> query/parameter combinations into tables and do an SQL comparison?
>

I inspected it visually and also dumped all variables into a file directly
from the application.

>
> > I don't really know what to make from this behavior, sometimes when I
> > start the application a few updates go through and eventually it will
> > lock up completely and sometimes it locks up immediately - always with
>
> Is there a common thread with regard to the parameters in use when
> things lock up?
>

Do you mean if it always locks on the same parameters? If so then it does
not, sadly

>
> > heap_hot_search_buffer using ~20 of all CPU time on the system.
> >
> > 2016-11-24 19:14 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
> >> On 11/23/2016 10:41 PM, azhwkd wrote:
> >>>
> >>> The group ID is part of the primary key of the group_history table. My
> >>> understanding is that two INSERTs with different group IDs should not
> >>> collide in this case, or am I wrong in thinking this?
> >>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>