Re: query locks up when run concurrently

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?
>
> The table definition for group_history is the following:
>
> CREATE TABLE public.group_history (
> group int4 NOT NULL,
> id int4 NOT NULL,
> sub_category int4 NOT NULL,
> "date" date NOT NULL,
> aa int8 NOT NULL,
> ab int8 NOT NULL,
> bb int8 NOT NULL,
> ba int8 NOT NULL,
> quantity int8 NOT NULL,
> "hour" int2 NOT NULL,
> CONSTRAINT group_history_pk PRIMARY KEY (group,id,sub_category,"date","hour")
> )
> WITH (
> OIDS=FALSE
> );

Me slaps head

Windy night last night + lack of sleep + insufficient caffeine = Not
seeing the table definition before hitting Send on previous post.

>
> Kind regards,
> Sebastian
>
> 2016-11-24 0:28 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
>> On 11/23/2016 01:52 PM, azhwkd wrote:
>>>
>>> Greetings!
>>>
>>> The parallel calls should not be working on the same row. Each query
>>> services a different group ID on it's own and there is no overlap.
>>
>>
>> Except the INSERT query in the trigger function is working on dates not
>> group ids.
>>
>>>
>>> Kind regards,
>>> Sebastian
>>>
>>>
>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> schrieb am Mi.,
>>> 23. Nov. 2016 um 17:47 Uhr:
>>>
>>> azhwkd(at)gmail(dot)com <mailto:azhwkd(at)gmail(dot)com> writes:
>>> > I have a query which if run alone usually completes in about 300ms.
>>> > When run in my application this query constantly locks up and bogs
>>> > down all connections of the connection pool (In the application this
>>> > query is run up to 10 times in parallel with different parameters).
>>> > What's really weird is that I can re-run one of the hung queries
>>> from
>>> > the command line while it's hung and it will complete as expected
>>> > while the hung queries continue to use 100% CPU time.
>>>
>>> Judging from the EXPLAIN timing, most of the work is in the trigger,
>>> which leads me to wonder if the parallel calls are likely to be
>>> fighting
>>> over inserting/updating the same row in the group_history partition
>>> tables. Or are you certain that they should be hitting different
>>> rows?
>>>
>>> regards, tom lane
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com