On 2025-04-21 18:12:13 +0200, Thiemo Kellner wrote:
> I wonder if that is a corner case. Updating a unique key sounds to me like a
> design flaw in the first place.
I agree that changing a surrogate key is almost always a mistake.
But there might be situations where a column should be unique but
Thanks for the pointer. I feel my doubts reflected. For such reasons, I prefer
the UUID as surrogate key. No point in trying to establish an order or even id
arithmetics.
21.04.2025 18:44:27 Adrian Klaver :
> On 4/21/25 09:12, Thiemo Kellner wrote:
>> I wonder if that is a corner case. Updating
On 4/21/25 09:12, Thiemo Kellner wrote:
I wonder if that is a corner case. Updating a unique key sounds to me like a
design flaw in the first place.
Check out this the thread below for discussion on that topic:
https://www.postgresql.org/message-id/dkbnfi$7g5$1...@sea.gmane.org
--
Adrian Kl
I wonder if that is a corner case. Updating a unique key sounds to me like a
design flaw in the first place.
On 4/21/25 01:47, Peter J. Holzer wrote:
If the hash was the other way around it wouldn't work.
So let's try if we can get the optimizer to flip the plan by changing
the number of updated rows.
[a few minutes later]
#v+
hjp=> explain
with a as (select id from id_update where id > 9 order
On 2025-04-20 08:28:22 -0700, Adrian Klaver wrote:
> On 4/20/25 02:10, Peter J. Holzer wrote:
> > I've just read Laurenz' blog post about the differences between Oracle
> > and PostgreSQL[1].
> >
> > One of the differences is that something like
> >
> > UPDATE tab SET id = id + 1;
> >
> > t
On 4/20/25 02:10, Peter J. Holzer wrote:
I've just read Laurenz' blog post about the differences between Oracle
and PostgreSQL[1].
One of the differences is that something like
UPDATE tab SET id = id + 1;
tends to fail on PostgreSQL because the the primary key constraint is
checked for ev
Might that be a feature of or a flaw in the application design? I opt for the
latter. Any application that needs updates, be it only in emergency cases,
should take that into account.
On Sun, Apr 20, 2025 at 5:35 AM Thiemo Kellner
wrote:
> Very interesting. But is the sort overhead worth it? Why not make the
> constraint deferrable before the update and switch back afterwards?
>
The role which runs the UPDATE might not have the priv to ALTER TABLE ...
ALTER CONSTRAINT.
--
D
Hm, deadlocks preventing order by. Never had that problem. Then again, I mostly
have Oracle experience and no need for complicated updates. If I had, I'd
rather think of chunking updates and orchestrate those before ordering within
updates.
On 2025-04-20 11:34:56 +0200, Thiemo Kellner wrote:
> Very interesting. But is the sort overhead worth it? Why not make the
> constraint deferrable before the update and switch back afterwards?
Mostly idle curiosity whether that's possible at all.
But there might be other reasons why you want to
Very interesting. But is the sort overhead worth it? Why not make the
constraint deferrable before the update and switch back afterwards?
I've just read Laurenz' blog post about the differences between Oracle
and PostgreSQL[1].
One of the differences is that something like
UPDATE tab SET id = id + 1;
tends to fail on PostgreSQL because the the primary key constraint is
checked for every row, so it will stumble over the tempora
13 matches
Mail list logo
