Message-ID: <c7a7b116bd28a89231c889c2c827d62568b50f93.camel@cybertec.at>
Subject: Re: ERROR: could not serialize access due to concurrent update
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Date: Wed, 17 May 2023 08:10:08 +0200
In-Reply-To: 
 <CAKDb7aFLkz2btDpnkmefDBa-N=kUJ9HYhPmmD1o5+sLqYewY6A@mail.gmail.com>
References: 
	<CAKDb7aFR1B7wvaLBxBDdark7jx=y31BY74WKdrPF1Zq5xwT+mA@mail.gmail.com>
	 <CAKDb7aHL=NXzcFyL5oM3V6=D9P4VVdx_Gd=FskdbeqCotKFSSg@mail.gmail.com>
	 <580a59b7e6ef605000c5dde862300e4290d9445a.camel@cybertec.at>
	 <CAKDb7aGaZ94nxr1gSw2NfLu5arec9G+SOBDGRV7njxG_C5TZeg@mail.gmail.com>
	 <6d2162718b0af8504c5f85013b78dc97a2046d59.camel@cybertec.at>
	 <CAKDb7aFLkz2btDpnkmefDBa-N=kUJ9HYhPmmD1o5+sLqYewY6A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/c7a7b116bd28a89231c889c2c827d62568b50f93.camel%40cybertec.at>
Precedence: bulk

I'm getting a bit tired of reformatting your replies into the correct order=
...

On Tue, 2023-05-16 at 22:54 +0530, ROHIT SACHDEVA wrote:
> On Tue, 16 May, 2023, 10:05 pm Laurenz Albe, <laurenz.albe@cybertec.at> w=
rote:
> > On Tue, 2023-05-16 at 18:39 +0530, ROHIT SACHDEVA wrote:
> > > I am getting this error in the application logs, and this error is fr=
equently coming.
> > > Could someone suggest what would be the best solution to overcome the=
se types of errors.
> > > Isolation of the database is read committed. Should I change the isol=
ation?
> > >=20
> > > Also foreign table scan is there on query.
> > >=20
> > > It's a postgres_fdw i am using.
> >=20
> > That is normal if you modify any data via FDW, because one local query =
can result in several
> > foreign queries, which have to see the same snapshot of the data.
> >=20
> > Repeat queries that fail that way, and keep your transactions as short =
as you can.
>
> But due to this error our transaction is aborted and application team are=
 getting these errors in their logs.
>
> How can I figure this out?=20
> Can there be work around for not getting this error.

You should be able to avoid these serialization conflicts by using SELECT .=
.. FOR NO KEY UPDATE
whenever you read data that you intend to modify later.

Yours,
Laurenz Albe