Re: query locks up when run concurrently

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
);

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