public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Giovanni Martinez <gio@iqtoolkit.ai>
To: Raj <rajeshkumar.dba09@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Partitioning table - Update on partitioning key
Date: Fri, 22 May 2026 10:42:40 -0400
Message-ID: <CAMELd3Q2pZR9fCGpNu5F-A3G9Qp1Na8hERvci+B7fBjg9kjw3Q@mail.gmail.com> (raw)
In-Reply-To: <CAJk5AtZm2yyS2E-1=ZYNFZhHRMgJyphG7o-841CBU06RvyfKLQ@mail.gmail.com>
References: <CAJk5AtZH+P1m39F4R2XLtzqgne7Za4qmMTgtBDbU8snXQBrZXQ@mail.gmail.com>
<dd7389df31fe3ac9c1eac94d5ddcb2d51f453f90.camel@cybertec.at>
<CAJk5AtYo4hUFwpKwZGpSzPG3ruTgRPLaKXyVOYn=JGoN29u+Qw@mail.gmail.com>
<CANzqJaDCAodT3Fkm3BX=7=KHBxcLuFdKpJeRJpFz3RoKsD541w@mail.gmail.com>
<CAJk5AtZm2yyS2E-1=ZYNFZhHRMgJyphG7o-841CBU06RvyfKLQ@mail.gmail.com>
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
--
[image: logo image]
[image: linkedin icon] <https://www.linkedin.com/in/gmartinez-dbai/;
*Giovanni Martinez*
IQtoolkit.ai | Principal AI Solutions Architect
gio@iqtoolkit.ai
https://www.iqtoolkit.ai
Book a meeting <https://calendar.app.google/JoTjgsJg19xPbzS48;
On Fri, May 22, 2026 at 10:24 AM Raj <rajeshkumar.dba09@gmail.com> 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 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 PM 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 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
>>>> 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!
>>
>
--
Building the future of AI-driven database optimization.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gio@iqtoolkit.ai, rajeshkumar.dba09@gmail.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning table - Update on partitioning key
In-Reply-To: <CAMELd3Q2pZR9fCGpNu5F-A3G9Qp1Na8hERvci+B7fBjg9kjw3Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox