Re: query locks up when run concurrently

From: azhwkd <azhwkd(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: query locks up when run concurrently
Date: 2016-11-24 21:23:55
Message-ID: CAJ_VqiyyWE1p09ngZZvjrG8WNBg9C9Q04T088MTmyUUU3fZHJw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

It should not be possible because a group does not return to the
update pool before the update hasn't finished.
I watched the queries in a postgres client and there was no overlap I could see.
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
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?
>
>
> After fresh coffee:
>
> In your first post you had this:
> "(In the application this
> query is run up to 10 times in parallel with different parameters)"
>
> where the parameters look to be a timestamp and a group id.
>
> Is it possible that the combination of parameters over the 10 different
> queries is not actually selecting rows in each result set that are unique
> over (group,id,sub_category,"date","hour") between result sets?
>
>
>
>
>>
>> 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
>>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-11-24 21:34:11 Re: query locks up when run concurrently
Previous Message Adrian Klaver 2016-11-24 18:51:52 Re: [SPAM] Re: [SPAM] Re: Best practices to manage custom statistics