Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wQR34-001YW8-1c for pgsql-admin@arkaria.postgresql.org; Fri, 22 May 2026 14:40:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wQR32-00DLPF-0g for pgsql-admin@arkaria.postgresql.org; Fri, 22 May 2026 14:40:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wQR31-00DLP6-2Q for pgsql-admin@lists.postgresql.org; Fri, 22 May 2026 14:40:24 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wQR30-00000000Haw-4A6c for pgsql-admin@lists.postgresql.org; Fri, 22 May 2026 14:40:23 +0000 Received: by mail-ot1-x334.google.com with SMTP id 46e09a7af769-7dcdd23fcdfso3915005a34.3 for ; Fri, 22 May 2026 07:40:22 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1779460821; cv=none; d=google.com; s=arc-20240605; b=NqIIx0Qf9jaW6mqb5e0bIJhyoaUVVPceoEeXTInl6CE9aIQPocrf/TIuHnxD8UJ1hA cwqVogQx3VTq48FTnPc28dYlMPuGkkMuv3V4x0kSSdOlbv56nOC4LJnNZO5y8L6dSn8o 7skZCEEsrDGN3RtXUg1DAp+TaFfqrBKfaeniC7YMiggvpu4leYwAoc5SUl7W+1mfex2Q I0iM4GZLYVoYoAA0MO1RryYP7LMj7U2arXie3GH0/Ql4Nd5nKRy0YfejRaqiPrs9j7uw PqREKaW+Jgahf46yLbNENElJArrMaGsu8ULsZXhNd1kc3NWhIvWmBgXmZXE+FpuOHZrs SrCg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=M0123hPduIJege/U0BDo54FwSeqpsVmE2ZozLJtASkY=; fh=druxZHa2fk4e6MLibibygn9AWWgeaAPo4m8Gpo2MBXU=; b=OuW4ciKAyUsIZnTLlahi5nLj9nDDhx9QF4f/a2fd5N+XyoDHGoyfVxUerOBqJwC8Je zHbPt3MadjVMy6MME8MeUHtwusyokX6wmt/s1rkW8EacmaXJfj/vcp9DJW5V1flK3m7D 55yeuYBxF0TpiEoHRONgSaUJ5UukGpCLrBY8fF9e9oEjdbiAqFpWzK3bTGVW2qpPGKh3 IycdkBgTDJjloP4z4a2R+3rzdiA8ziW6c3DXrdCKHSj/qodGYZiMqsUSQT5YvPQoGmg+ +eieY6SrCsRXhpljCGEoptjDg5gXid9ot+60vhyfmhwKQppA7KqwKatIUKjjf1hhEsNG fyXg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1779460821; x=1780065621; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=M0123hPduIJege/U0BDo54FwSeqpsVmE2ZozLJtASkY=; b=q/OzrF8Co1h8jOiCuujYo6OUWkssmot4dV4p3PMlSFFgRJCYQ2W6apqwCF9+2YAU83 66wQNq720p7RAjR1Xp03FN4X7CcZknan6QpBuwWry86Qmw19mztURzOa+lbhVa9J9dY7 2LWa4giWwZroTQuIpghgV1zYEl5Kgw7BSHUguUHTsRn6slRuOswsMhaem3c6PXQC99Qz g9+OXPB0UltJWQJk+6Gu8VQUx6O0FGhakU9hVn36nSQ1z+U78xRC/F4lWuLW8zC1YV5z bHeBSIptKHBOpXwtIeRHtFkEeBN6bmZMpsdy+5kYRwthBaymhwhindI0lx1SZRz7yr7g TlQA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1779460821; x=1780065621; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=M0123hPduIJege/U0BDo54FwSeqpsVmE2ZozLJtASkY=; b=EfGiU+JEnait+1IKo84aecD1m4DytgtMsH5mmQwXiVLEDckmv5MZ2FLk7SlD0PySVQ Gr9QqlPLx1pK1sOck651CoDUDKTY9/Q+8eYBaVeMzi6x4eQ2BPRjbPXAOC2xpvYAAhpY X09acP6+KPEsXyWzVt7HPSm3y/VauJhGEq/CcIue7XFkg11QQKnDvUWYDpOHL0Twm1ZX l4+jcxKbh2XtUZlyuNNgdWTD9iDDKH/lX/Bwcj8oIUer/j/OFSLuJO+kYfqKmpbqgjvV /8r7/y+13vzrh/bBpIYsX7pWiKNSz/QNsjrpmDw5+4LXRh4iQG9EJWhVFyI0WPgkVEvm 7i5Q== X-Gm-Message-State: AOJu0YyHsQPc+rktIki/hWqp5q6+urbHXk5kT4ECc+Y5TZ+Kfg15jS73 c7wc8ThmTlXJsVjYD8DxeSwvEsIdjWzY8oHC9kQ8VdpiFaZv+AeJK7a4YPYDMHRBTS0RrF8JXPJ 4yk9DO8QgT7XRcY4jRtvQXuPtR9uiVxI2Iw== X-Gm-Gg: Acq92OGbDhxon9XfZnKBhoOZPFcWiDFjLZobR43q0FVvhbJJmcIs8xL2tXVKxW2ERfA yGaULHO3t6arzJqFlfH85gJG87B/nU0bFl21yc6PsoES2HVunj/Xyxp+5VcbgHIyGMkbPmbI67c 9m4NgTtE2Arkfe1eIUL22p3HAcZOldMS/Uujzw1ar47Yd74Yhj6XxWIPK1r4bGKiGFRSDKb4eL8 vs+4JP0/XVqb3Y9TLh11e8kup/jguxAzqcndEjzyN12xGjhotwPRROc2A3zYcI/XCGNHuaHlrYQ UvRdtk0n X-Received: by 2002:a05:6820:222a:b0:69b:5696:e63f with SMTP id 006d021491bc7-69d7ebbfcb2mr1664448eaf.24.1779460820949; Fri, 22 May 2026 07:40:20 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Fri, 22 May 2026 10:40:08 -0400 X-Gm-Features: AVHnY4IwmLBLGhcQtCMAJbd0sKLAeC1WCnpICDWRjOdqtb9fKlN7kZOqOMR7xKI Message-ID: Subject: Re: Partitioning table - Update on partitioning key To: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000001fe3da06526902e8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001fe3da06526902e8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, May 22, 2026 at 10:24=E2=80=AFAM Raj = wrote: > 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 bes= t > candidtae for partition key. > Absolutely. How is it even wise in Oracle? > With isolation method read committed, if there are concurrent updates on > the same row in different sessions for example( may be updated on differe= nt > 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 i= n > the old partition itself and that's when we get error. > > .. > > On Thu, 21 May 2026, 23:20 Ron Johnson, 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 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 = and >>> we get this error >>> >>> On Thu, 21 May 2026, 17:12 Laurenz Albe, >>> 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 , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001fe3da06526902e8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, May 22, 2026 at 10:24=E2=80=AFAM = Raj <rajeshkumar.dba09@gm= ail.com> wrote:
The e= rror occurs, due to updating the partition key column.
We recently migrated from oracle to postgres.

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

Absolutely.=C2=A0 How is it ev= en wise in Oracle?=C2=A0
=C2=A0
With isolation method read committed,=C2=A0 if there are concurrent = updates on the same row in different sessions for example( may be updated o= n different columns out of which one update is on partition key),=C2=A0 upd= ates on the partition key, may cause the row placed to New partition.
=

Now, because of MVCC the seco= nd 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&g= t; wrote:
We'll need to see a "reproducer" (aka short b= it of code that reproduces your problem), with the output pasted here.
On Th= u, May 21, 2026 at 12:12=E2=80=AFPM Raj <rajeshkumar.dba09@gmai= l.com> wrote:
No, updating the partition key (say colum created_at= )....when u update the date , say change march to April,=C2=A0 this record = is in new partition and 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<= br> of the data it is operating on?

If I had to guess, I would suspect that your UPDATE statement tries to modi= fy
the same row more than once.

Yours,
Laurenz Albe


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000001fe3da06526902e8--