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 1wQR6W-001YYh-0a for pgsql-admin@arkaria.postgresql.org; Fri, 22 May 2026 14:44:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wQR5U-00DOnG-14 for pgsql-admin@arkaria.postgresql.org; Fri, 22 May 2026 14:42:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wQR5T-00DOn7-2q for pgsql-admin@lists.postgresql.org; Fri, 22 May 2026 14:42:56 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wQR5S-00000000u3l-0irI for pgsql-admin@lists.postgresql.org; Fri, 22 May 2026 14:42:56 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-7ca947f9b00so69706947b3.0 for ; Fri, 22 May 2026 07:42:52 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1779460971; cv=none; d=google.com; s=arc-20240605; b=DUxOWhu3K0TY0uqhEOmke7qpYBDeEqiavyl969nYTGNqhCpvD9PaR/PmdJS1RoAuMs s/wmZb/PrgNdiLGWyFsAbAAQOcWaRcUHgHrN17WnBf9/ZpfwDUoyghVitiXsA/tsWoGc GsxmZMiAXXX/iIQmDmd0oh5FP3kUegzUBRryMqgblQbQbn7AaH382qscrKQNcmF+e8Hm 7Futl1N4fxf2DH3UeB/OfEgwn/nUw/DYsTrD58l3lu6zCi46dxkYx5k1j56xhuHFwosL Qu9eG+tAayh29DjwEXZyVCuyvM34yABcYATypuiPMiaM4JZWYhcAFg3zO4mrHOccIIPj aX3w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=zXqsAArB7FXV29ALBkNF52ERNCcXc5Njo6Ofom10k+w=; fh=k/NhMyyuI3INJYNoa4kwMzWefsFc7T3po4rCotjlEVA=; b=bm2zPtvg547MsFW6kw7Gsy7wotW8VnM4CEulH+a6by2fvh3HfdirY9jVEVAgfPHUx7 IbhgEkLtEIZIpjX/EZKzeUuvCvU2WU1F2IyVbRLfjs60YyQa+fXH4/Z55x0g1l1S9CWk UuoJYxihK02L0Y8TVbn8qGrkbLuboVpQoI3ssyBjMaBO8XACO6xpTzpuT7jRkc/X5vqI DTAe5Rxz6hJT9vvU8cB1XMg5/iEdibFBHAXiJcbFxH61Odn/dn5kcpsVR15eHtHt5237 DuDqya9vZiJQXG6rNuF7KIZeZw36TOIqmRnapdAmemcGPmo2j7bFEsBGdy2Iv5Q5z8qY 8SKQ==; 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=iqtoolkit.ai; s=google; t=1779460971; x=1780065771; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zXqsAArB7FXV29ALBkNF52ERNCcXc5Njo6Ofom10k+w=; b=YZfDtzy/060a9aFIxY9NrN5ML7iLZT2OSot5N+g6p3jkTI6vZIig5xp1zocOFhJPfs lGvGmKN3KNQG/bfe5cRiNDOoqyg8HvZ+Cr6G6malDGiI/w46mZrHjsu+Mle5QKNKVT6V d8qLVT+XDml084Z1NMUhZ97Vanf20aQJaICzsSDwRzrOsPD95r3QkvtoPVWreQVzq1XG G/FefwwyUKUmh39B776mnv0jyde6AXERCDz4F1XMBg0k7nSWHZkoVRf+YBnR5RFcGkCB xSdmrCqYeKonr5s859pCy0IG4yvlw/vC8NGSKVnkEZb9lm8dCexwAyMr4YsCLErwCIlf JOVg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1779460971; x=1780065771; h=cc: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=zXqsAArB7FXV29ALBkNF52ERNCcXc5Njo6Ofom10k+w=; b=jCfSnjBwJYTfwgYfmcI3Y7aZizbUlWb/teMocvvNfy3W5jbDzNfjOOs0QJzS+YRFyR VyMzQLqyOA110x9WwFPSF6JfYzzuVdlNWjgKUUqb8Di6lxdkCQpE4VFdBcVjz+d4Ms1T 9ziDKLK5SKYnbZgvcqNd/T9BfmA1ygvJAPClq9H8tTmgG4yFfvTRagcUlRWEG0xwMIMe +lAqTllyMuM6YZzsTrRnQ18qCoeX822gcHFFlltFcSnthsatADbuflxQMzckzn9L13OA IQ/h7CkG26ZIEVbeSZ2uF9YKwoAx5Z8GBk6szi+sszi8jDk1hVf10aVQwZ3g23Kg51t/ 5trQ== X-Forwarded-Encrypted: i=1; AFNElJ81C0hKhyCj3gtjWLr+VA8kwP9pTeleznMddIBN/3QJeiAQuJPI8OmSnegqlcV/P+sfBN8jHUbjG5NltQ==@lists.postgresql.org X-Gm-Message-State: AOJu0YyAcubf9CA0yeMJP1ikLBHKGDYGtc6RQBZD2gWe6b0s0anMNZot dzZ4n8u1Vgx/AXiJ+garUTL/pPK4TUfWButHHhEG01BQN3iFyMLRNKqdIKOEr6X0Kc3+0QBobLU bdklne4yysYGCqLS7stZcGf67qcO01jePEkN7c/KordWIPY7+6X8aaIvnnMZVcYkFGEBTKR0i3s M0dR3DexjNQZrDQ5Oif6MMHmelXCPKaPUBe6fc8uqTxw== X-Gm-Gg: Acq92OG1wF7MjdWBVgr3CxBQG0XJdrQ4U7dA7TZjGPhXbueRxMG9kPv7K9WdxBrJvbw d/r+TrGIOjR/imVWvnpsZd1The+DSUFMVYHzvlMb0Fk6xtIEFGbeTcW6yTdiZfNmBWxKG7nSAwO MUqD2YBp+p6cvNQ02RmrqMccc/VeVfSmy/rJ4RLq8I9283TJxVzAYsAEyob7C7EebuawUvVXgWq iqLFTsDFs8cM9KtXezOMTl0keO46Sg5qWsLYUuY7l8SBkolgch4r1JdqoeaTIZ3R1zWFG8jwXs6 7fPakULb65LAW9CgjfHp83OWhqkqtn1RwN7S+RPCwHFkjsyYhpYG X-Received: by 2002:a05:690c:9c10:b0:7cf:e22b:fc49 with SMTP id 00721157ae682-7d3356de6camr45607067b3.31.1779460971243; Fri, 22 May 2026 07:42:51 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Giovanni Martinez Date: Fri, 22 May 2026 10:42:40 -0400 X-Gm-Features: AVHnY4IjCQXeo0bBQ8mRvo-IdUZasv0KM5FIh29wyLVoIBwrSY1WQKfRUbzEiz0 Message-ID: Subject: Re: Partitioning table - Update on partitioning key To: Raj Cc: Ron Johnson , Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000153d220652690bd9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000153d220652690bd9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Raj, I encountered a similar situation in the past. To resolve it, I set up an INSTEAD OF trigger that replaces updates on the partition key column with a DELETE followed by an INSERT statement. You might want to try a similar approach to handle the concurrent update errors. Best, Giovanni Martinez --=20 [image: logo image] [image: linkedin icon] *Giovanni Martinez* IQtoolkit.ai | Principal AI Solutions Architect gio@iqtoolkit.ai https://www.iqtoolkit.ai Book a meeting 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. > > > 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 Building the future of AI-driven database optimization. --000000000000153d220652690bd9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Raj,

I encountered a similar situation in the pa= st. To resolve it, I set up an INSTEAD OF trigger that replaces updates on = the partition key column with a DELETE followed by an INSERT statement.
=
You might want to try a similar approach to handle the concurrent updat= e errors.

Best,
Giovanni Martinez

--=C2=A0
3D"logo
3D"linkedin=
Giovanni Martinez
=
IQtoolkit.ai | Principal AI = Solutions Architect



On= Fri, May 22, 2026 at 10:24=E2=80=AFAM Raj <rajeshkumar.dba09@gmail.com> wrote:
The error o= ccurs, 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.
=


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

Now, because of MVCC the second updat= e 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:<= br>
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&= gt; 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 wro= te:
> 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!

Building the future of AI-driven database optim= ization. --000000000000153d220652690bd9--