MIME-Version: 1.0
References: 
 <CAJk5AtZH+P1m39F4R2XLtzqgne7Za4qmMTgtBDbU8snXQBrZXQ@mail.gmail.com>
 <dd7389df31fe3ac9c1eac94d5ddcb2d51f453f90.camel@cybertec.at>
 <CAJk5AtYo4hUFwpKwZGpSzPG3ruTgRPLaKXyVOYn=JGoN29u+Qw@mail.gmail.com>
 <CANzqJaDCAodT3Fkm3BX=7=KHBxcLuFdKpJeRJpFz3RoKsD541w@mail.gmail.com>
In-Reply-To: 
 <CANzqJaDCAodT3Fkm3BX=7=KHBxcLuFdKpJeRJpFz3RoKsD541w@mail.gmail.com>
From: Raj <rajeshkumar.dba09@gmail.com>
Date: Fri, 22 May 2026 19:53:51 +0530
Message-ID: 
 <CAJk5AtZm2yyS2E-1=ZYNFZhHRMgJyphG7o-841CBU06RvyfKLQ@mail.gmail.com>
Subject: Re: Partitioning table - Update on partitioning key
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fb2c25065268c746"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJk5AtZm2yyS2E-1%3DZYNFZhHRMgJyphG7o-841CBU06RvyfKLQ%40mail.gmail.com>
Precedence: bulk

--000000000000fb2c25065268c746
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

The error occurs, due to updating the partition key column.

We recently migrated from oracle to postgres.

According to my research, columns that are updated frequently are not best
candidtae for partition key.


With isolation method read committed,  if there are concurrent updates on
the same row in different sessions for example( may be updated on different
columns out of which one update is on partition key),  updates on the
partition key, may cause the row placed to New partition.

Now, because of MVCC the second update may be looking for row to update in
the old partition itself and that's when we get error.

..

On Thu, 21 May 2026, 23:20 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> We'll need to see a "reproducer" (aka short bit of code that reproduces
> your problem), with the output pasted here.
>
> On Thu, May 21, 2026 at 12:12=E2=80=AFPM Raj <rajeshkumar.dba09@gmail.com=
> wrote:
>
>> No, updating the partition key (say colum created_at)....when u update
>> the date , say change march to April,  this record is in new partition a=
nd
>> we get this error
>>
>> On Thu, 21 May 2026, 17:12 Laurenz Albe, <laurenz.albe@cybertec.at>
>> wrote:
>>
>>> On Thu, 2026-05-21 at 16:45 +0530, Raj wrote:
>>> > Update happing on partition key.
>>> >
>>> > And we get error 'Tuple to be locked was already moved to another
>>> partition due to concurrent update error..
>>> >
>>> > What's the best solution to handle it?
>>>
>>> Could you give us more context, like the exact statement and a
>>> description
>>> of the data it is operating on?
>>>
>>> If I had to guess, I would suspect that your UPDATE statement tries to
>>> modify
>>> the same row more than once.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

--000000000000fb2c25065268c746
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">The error occurs, due to updating the partition key colum=
n.<div dir=3D"auto"><br></div><div dir=3D"auto">We recently migrated from o=
racle to postgres.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Accor=
ding to my research, columns that are updated frequently are not best candi=
dtae for partition key.</div><div dir=3D"auto"><br></div><div dir=3D"auto">=
<br></div><div dir=3D"auto">With isolation method read committed,=C2=A0 if =
there are concurrent updates on the same row in different sessions for exam=
ple( may be updated on different columns out of which one update is on part=
ition key),=C2=A0 updates on the partition key, may cause the row placed to=
 New partition.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Now, bec=
ause of MVCC the second update may be looking for row to update in the old =
partition itself and that&#39;s when we get error.</div><div dir=3D"auto"><=
br></div><div dir=3D"auto">..</div></div><br><div class=3D"gmail_quote gmai=
l_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 21 May 202=
6, 23:20 Ron Johnson, &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">ronljo=
hnsonjr@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><d=
iv dir=3D"ltr"><div>We&#39;ll need to see a &quot;reproducer&quot; (aka sho=
rt bit of code that reproduces your problem), with the output pasted here.<=
/div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">O=
n Thu, May 21, 2026 at 12:12=E2=80=AFPM Raj &lt;<a href=3D"mailto:rajeshkum=
ar.dba09@gmail.com" target=3D"_blank" rel=3D"noreferrer">rajeshkumar.dba09@=
gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"auto">No, updating the partition key (say colum crea=
ted_at)....when u update the date , say change march to April,=C2=A0 this r=
ecord is in new partition and we get this error</div><br><div class=3D"gmai=
l_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 21 May 2026, 17:12 L=
aurenz Albe, &lt;<a href=3D"mailto:laurenz.albe@cybertec.at" target=3D"_bla=
nk" rel=3D"noreferrer">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex">On Thu, 2026-05-21 at 16:45 =
+0530, Raj wrote:<br>
&gt; Update happing on partition key.<br>
&gt; <br>
&gt; And we get error &#39;Tuple to be locked was already moved to another =
partition due to concurrent update error..<br>
&gt; <br>
&gt; What&#39;s the best solution to handle it?<br>
<br>
Could you give us more context, like the exact statement and a description<=
br>
of the data it is operating on?<br>
<br>
If I had to guess, I would suspect that your UPDATE statement tries to modi=
fy<br>
the same row more than once.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>
</blockquote></div>

--000000000000fb2c25065268c746--