Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Dead lock after the migration from CentOS 7 to RHEL 9
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAB5fag47NNn4T1e-t7YkLkqQbdammAvgDeb423ThaY=VjkSTOQ@mail.gmail.com>
Date: Wed, 14 Aug 2024 21:45:29 -0600
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <07994165-F268-41E0-9975-4FB5DE2EBED1@elevated-dev.com>
References: 
 <CAB5fag444HCM8xONrKbg+rJ5odCdy=0vmianN2Si5K5mWRFKvQ@mail.gmail.com>
 <CAB5fag6QPm1OE7H6roywzovDxb9rT9Wx-MdeiuK=mDvx6NTwtw@mail.gmail.com>
 <CAKFQuwbHt_56dOP-Fjtaj2C9KVJN14-L9LrDUf5AeWyW21g7KQ@mail.gmail.com>
 <CAB5fag7hsyWJUv=KezM=10RZ8hs=3fDkR4vMmJvn-Fjv3O0oeA@mail.gmail.com>
 <CAKFQuwYyFBgKKtJwFAOOsd2NahG6g8ymFKy_KpqhcoHdb_Nd3g@mail.gmail.com>
 <CAB5fag47NNn4T1e-t7YkLkqQbdammAvgDeb423ThaY=VjkSTOQ@mail.gmail.com>
To: Wasim Devale <wasimd60@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/07994165-F268-41E0-9975-4FB5DE2EBED1%40elevated-dev.com>
Precedence: bulk

Given errors like the pair you posted, my next step would be to examine =
the queries being executed by those processes during the deadlock. Now, =
I think by the time the deadlock error is logged, the queries involved =
have been cancelled, so it's possible the processes have gone on to =
another query and so pg_stat_activity might not have useful information, =
or it might--depending on what your app would do after a deadlock error.

If it does not, then the next thing would be to set =
log_min_duration_statement to less than deadlock/statement timeout, so =
that you could look through logs to figure out what are the two queries =
which are deadlocking against each other.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/


> On Aug 14, 2024, at 9:38=E2=80=AFPM, Wasim Devale <wasimd60@gmail.com> =
wrote:
>=20
> Ok thanks for the insights.
> Do we need to do any config changes in the config file?
> We have also configured Datadog agent at operating system level that =
checks postgresql activity it's has the pgpouncer.d config file that is =
unchanged after the migration. This might be the possible reason that it =
is throughing dead lock error.=20
> Please through some insights on the above.
> Thanks for the immediate help.
> Regards,
> Wasim=20
>=20
> On Thu, 15 Aug, 2024, 9:01=E2=80=AFam David G. Johnston, =
<david.g.johnston@gmail.com> wrote:
> On Wednesday, August 14, 2024, Wasim Devale <wasimd60@gmail.com> =
wrote:
> So it's not a postgresql issue. It's from the application side =
correct?
>=20
> That=E2=80=99s the assumption.  The vast majority of locks happen =
because the =E2=80=9Capplication=E2=80=9D executed SQL commands.
>=20
> David J.