public inbox for [email protected]
help / color / mirror / Atom feedPartitioning table - Update on partitioning key
7+ messages / 4 participants
[nested] [flat]
* Partitioning table - Update on partitioning key
@ 2026-05-21 11:15 Raj <[email protected]>
2026-05-21 11:42 ` Re: Partitioning table - Update on partitioning key Laurenz Albe <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Raj @ 2026-05-21 11:15 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Hi,
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partitioning table - Update on partitioning key
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
@ 2026-05-21 11:42 ` Laurenz Albe <[email protected]>
2026-05-21 16:11 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2026-05-21 11:42 UTC (permalink / raw)
To: Raj <[email protected]>; Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partitioning table - Update on partitioning key
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 11:42 ` Re: Partitioning table - Update on partitioning key Laurenz Albe <[email protected]>
@ 2026-05-21 16:11 ` Raj <[email protected]>
2026-05-21 17:50 ` Re: Partitioning table - Update on partitioning key Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Raj @ 2026-05-21 16:11 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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, <[email protected]> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partitioning table - Update on partitioning key
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 11:42 ` Re: Partitioning table - Update on partitioning key Laurenz Albe <[email protected]>
2026-05-21 16:11 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
@ 2026-05-21 17:50 ` Ron Johnson <[email protected]>
2026-05-22 14:23 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2026-05-21 17:50 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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 <[email protected]> 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, <[email protected]> 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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partitioning table - Update on partitioning key
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 11:42 ` Re: Partitioning table - Update on partitioning key Laurenz Albe <[email protected]>
2026-05-21 16:11 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 17:50 ` Re: Partitioning table - Update on partitioning key Ron Johnson <[email protected]>
@ 2026-05-22 14:23 ` Raj <[email protected]>
2026-05-22 14:40 ` Re: Partitioning table - Update on partitioning key Ron Johnson <[email protected]>
2026-05-22 14:42 ` Re: Partitioning table - Update on partitioning key Giovanni Martinez <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Raj @ 2026-05-22 14:23 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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, <[email protected]> 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 <[email protected]> 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, <[email protected]>
>> 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!
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partitioning table - Update on partitioning key
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 11:42 ` Re: Partitioning table - Update on partitioning key Laurenz Albe <[email protected]>
2026-05-21 16:11 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 17:50 ` Re: Partitioning table - Update on partitioning key Ron Johnson <[email protected]>
2026-05-22 14:23 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
@ 2026-05-22 14:40 ` Ron Johnson <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2026-05-22 14:40 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Fri, May 22, 2026 at 10:24 AM Raj <[email protected]> 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.
>
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 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, <[email protected]> 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 <[email protected]> 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, <[email protected]>
>>> 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!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Partitioning table - Update on partitioning key
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 11:42 ` Re: Partitioning table - Update on partitioning key Laurenz Albe <[email protected]>
2026-05-21 16:11 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 17:50 ` Re: Partitioning table - Update on partitioning key Ron Johnson <[email protected]>
2026-05-22 14:23 ` Re: Partitioning table - Update on partitioning key Raj <[email protected]>
@ 2026-05-22 14:42 ` Giovanni Martinez <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Giovanni Martinez @ 2026-05-22 14:42 UTC (permalink / raw)
To: Raj <[email protected]>; +Cc: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]>
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
[email protected]
https://www.iqtoolkit.ai
Book a meeting <https://calendar.app.google/JoTjgsJg19xPbzS48;
On Fri, May 22, 2026 at 10:24 AM Raj <[email protected]> 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, <[email protected]> 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 <[email protected]> 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, <[email protected]>
>>> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-05-22 14:42 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-21 11:15 Partitioning table - Update on partitioning key Raj <[email protected]>
2026-05-21 11:42 ` Laurenz Albe <[email protected]>
2026-05-21 16:11 ` Raj <[email protected]>
2026-05-21 17:50 ` Ron Johnson <[email protected]>
2026-05-22 14:23 ` Raj <[email protected]>
2026-05-22 14:40 ` Ron Johnson <[email protected]>
2026-05-22 14:42 ` Giovanni Martinez <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox