Re: [COMMITTERS] pgsql: Introduce group locking to prevent parallel processes from deadl

On Mon, Feb 15, 2016 at 12:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Robert Haas <robertmhaas@gmail.com> writes:
>
> > With respect to pg_locks - and for that matter also pg_stat_activity -
> > I think you are right that improvement is needed.
>
> This is really the core of my concern at the moment.  I think that
> isolationtester is probably outright broken for any situation where the
> queries-under-test are being parallel executed, and so will be any other
> client that's trying to identify who blocks whom from pg_locks.
>
> > The simplest thing we could do to make that easier is, in
> > pg_stat_activity, have parallel workers advertise the PID that
> > launched them in a new field; and in pg_locks, have members of a lock
> > group advertise the leader's PID in a new field.

> >

>
> That would be simple for us, but it would break every existing client-side
> query that tries to identify blockers/blockees; and not only would those
> queries need work but they would become very substantially more complex
> and slower (probably at least 4-way joins not 2-way).  We already know
> that isolationtester's query has performance problems in the buildfarm.
> I think more thought is needed here,

Agreed.  I think before deciding what exactly to add or change in pg_locks

or lock monitoring queries, it might be helpful if we define how we want

to convey the information to user/dba when group of parallel processes

are involved as blockers/blockees.  Some of the cases are:

a) a standalone backend waits for group of parallel processes holding

lock in same mode on same resource.

b) group of parallel workers are waiting for lock held by standalone

backend or some other parallel group

c) a standalone backend waits for group of parallel processes holding

lock in different mode on same resource.

and other similar cases.

Before jumping into discussion about solution, I would like to know

do you and or Robert also have above kind of cases in mind where you

want a better way to display information for user or something else?