Re: Partitioning table - Update on partitioning key

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(at)iqtoolkit(dot)ai
https://www.iqtoolkit.ai
Book a meeting <https://calendar.app.google/JoTjgsJg19xPbzS48>

On Fri, May 22, 2026 at 10:24 AM Raj <rajeshkumar(dot)dba09(at)gmail(dot)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(at)gmail(dot)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(dot)dba09(at)gmail(dot)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(dot)albe(at)cybertec(dot)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.