pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: could not serialize access due to concurrent update
9+ messages / 4 participants
[nested] [flat]
* ERROR: could not serialize access due to concurrent update
@ 2023-05-16 12:38 ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: ROHIT SACHDEVA @ 2023-05-16 12:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
I am getting this error in the application logs, and this error is
frequently coming. Could someone suggest what would be the best solution to
overcome these types of errors.
Isolation of the database is read committed. Should I change the
isolation?
--
Have a Good day !!!
Regards
Rohit Sachdeva
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 13:09 ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
parent: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: ROHIT SACHDEVA @ 2023-05-16 13:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Also foreign table scan is there on query.
On Tue, May 16, 2023 at 6:08 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
wrote:
> Hi All,
> I am getting this error in the application logs, and this error is
> frequently coming. Could someone suggest what would be the best solution to
> overcome these types of errors.
> Isolation of the database is read committed. Should I change the
> isolation?
>
>
> --
> Have a Good day !!!
>
> Regards
> Rohit Sachdeva
>
--
Have a Good day !!!
Regards
Rohit Sachdeva
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 13:44 Ron <ronljohnsonjr@gmail.com>
parent: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ron @ 2023-05-16 13:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Scan, you say? Add an index to support the FK lookup.
On 5/16/23 08:09, ROHIT SACHDEVA wrote:
> Also foreign table scan is there on query.
>
> On Tue, May 16, 2023 at 6:08 PM ROHIT SACHDEVA
> <sachdeva.rohit648@gmail.com> wrote:
>
> Hi All,
> I am getting this error in the application logs, and this error is
> frequently coming. Could someone suggest what would be the best
> solution to overcome these types of errors.
> Isolation of the database is read committed. Should I change the
> isolation?
>
>
> --
> Have a Good day !!!
>
> Regards
> Rohit Sachdeva
>
>
>
> --
> Have a Good day !!!
>
> Regards
> Rohit Sachdeva
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 15:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2023-05-16 15:33 UTC (permalink / raw)
To: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>; pgsql-admin@lists.postgresql.org
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 frequently coming.
> > Could someone suggest what would be the best solution to overcome these types of errors.
> > Isolation of the database is read committed. Should I change the isolation?
>
> Also foreign table scan is there on query.
We need more infrmation, like what foreign data wrapper you are using.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 16:21 ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: ROHIT SACHDEVA @ 2023-05-16 16:21 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
It's a postgres_fdw i am using.
On Tue, 16 May, 2023, 9:03 pm Laurenz Albe, <laurenz.albe@cybertec.at>
wrote:
> 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
> frequently coming.
> > > Could someone suggest what would be the best solution to overcome
> these types of errors.
> > > Isolation of the database is read committed. Should I change the
> isolation?
> >
> > Also foreign table scan is there on query.
>
> We need more infrmation, like what foreign data wrapper you are using.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 16:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2023-05-16 16:35 UTC (permalink / raw)
To: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
>
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 frequently coming.
> Could someone suggest what would be the best solution to overcome these types of errors.
> Isolation of the database is read committed. Should I change the isolation?
>
> Also foreign table scan is there on query.
>
> It's a postgres_fdw i am using.
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.
Repeat queries that fail that way, and keep your transactions as short as you can.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 17:24 ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 9+ messages in thread
From: ROHIT SACHDEVA @ 2023-05-16 17:24 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Okay ..
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?
Can there be work around for not getting this error.
On Tue, 16 May, 2023, 10:05 pm Laurenz Albe, <laurenz.albe@cybertec.at>
wrote:
> >
> 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
> frequently coming.
> > Could someone suggest what would be the best solution to overcome these
> types of errors.
> > Isolation of the database is read committed. Should I change the
> isolation?
> >
> > Also foreign table scan is there on query.
> >
> > It's a postgres_fdw i am using.
>
> 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.
>
> Repeat queries that fail that way, and keep your transactions as short as
> you can.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-16 18:08 Scott Ribe <scott_ribe@elevated-dev.com>
parent: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2023-05-16 18:08 UTC (permalink / raw)
To: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
> On May 16, 2023, at 11:24 AM, ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
>
> 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?
> Can there be work around for not getting this error.
As suggested previously, repeat the query.
It would be good, of course, to reduce the frequency of the errors by figuring out what is conflicting--are the queries generally taking too long compared to their frequency? But repeating a query when a lock conflict blocks it would still be the ultimate fallback, and is pretty standard practice.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: could not serialize access due to concurrent update
@ 2023-05-17 06:10 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2023-05-17 06:10 UTC (permalink / raw)
To: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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> wrote:
> > 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 frequently coming.
> > > Could someone suggest what would be the best solution to overcome these types of errors.
> > > Isolation of the database is read committed. Should I change the isolation?
> > >
> > > Also foreign table scan is there on query.
> > >
> > > It's a postgres_fdw i am using.
> >
> > 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.
> >
> > 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?
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-05-17 06:10 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-16 12:38 ERROR: could not serialize access due to concurrent update ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
2023-05-16 13:09 ` ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
2023-05-16 13:44 ` Ron <ronljohnsonjr@gmail.com>
2023-05-16 15:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-16 16:21 ` ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
2023-05-16 16:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-16 17:24 ` ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
2023-05-16 18:08 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-05-17 06:10 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox