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.klaver@aklaver.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@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> schrieb am Mi.,
>>> 23. Nov. 2016 um 17:47 Uhr:
>>>
>>>     azhwkd@gmail.com <mailto:azhwkd@gmail.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.klaver@aklaver.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com