Date: Thu, 24 Apr 2025 08:26:09 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Order of update
Message-ID: <20250424062609.uzyq3swxeg5lh54u@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <20250420091033.n437fdrkihtjrncd@hjp.at>
 <c71a961f-506d-43ad-92c6-f23831e408c5@aklaver.com>
 <20250421084717.4edbhu6a7duiqw42@hjp.at>
 <e9bb687d-b7aa-46e2-ad0b-39ada582a545@aklaver.com>
 <a75dc195-2762-4d8e-89bb-d34a557b8c91@gelassene-pferde.biz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="naeisljvozrc53ye"
Content-Disposition: inline
In-Reply-To: <a75dc195-2762-4d8e-89bb-d34a557b8c91@gelassene-pferde.biz>
Archived-At: <https://www.postgresql.org/message-id/20250424062609.uzyq3swxeg5lh54u%40hjp.at>
Precedence: bulk


--naeisljvozrc53ye
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 lik=
e 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 isn't
an id.

For example, many years ago it was a popular[1] programming pattern to
represent trees as nested ranges (i.e. if two children of a parent had
the ranges (a, b) and (b+1, c) then the parent had (a-1, c+1).
Insert-operations then need to update those columns. You want an index
on those columns (since you search for them a lot), and you might want
to make it a unique index, since that covers part of the invariant
(although not the complete invariant). If you do that you run into the
update problem.

There are probably other use-cases. Anything where you need a unique
order which can change, I guess?

Anyway, I don't have a pressing need for this, as I said I was just
curious.

        hjp

[1] Mostly in MySQL I think, since it didn't have recursive queries of
    any kind.

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--naeisljvozrc53ye
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmgJ2XsACgkQ8g5IURL+
KF1hZBAAmxotyX2A0R2LAlJIvUid5CfI5t0PO0aRnv0A/Cw8pmIDc4ZMayy/SyJs
dS6mV2NsTxj0yYlFkfyfHrQ0w7LCCyvfkbathgusNsl+pBM3hqVbb2d6Q349f/cZ
dnalCWnv/clgqi8HD0/2bavFk5tcZR9tpNjB6IBuo1lIk9CTuP+KGHwpBUjEGToP
y8AApX0Lr1mieYEDZ3P5E5y8Tt9zqJiOLmUusIsAe94Lrvoud0MKH87lyE0lWVFx
alcb/bWh3rsa80OUO95Oa41bTLu2S2jqSiLXRv1F1QgskGXyQnQP+AEMDMceOKXe
spWKBLJVGPr75shBHCzBxQSP9DCHB6kiDNY5sHq/MhXhV7/O47KyhAxuslyU766s
2Iq2FPnnhpJPRYqFT7CNH6MM1EidubTh9UlSUheZUX1cwynfqAwdU1VHpx6y7bwH
b2tkMdZoYSdrTx1DtG6p89QEgnNLmuA1QY4RWJuUJEyKAIQYc2/6TSRbaUaOGDMf
KAAMn2/95q82I1JnYFIns+DC5QuY3AIBHbt6VJAqvpFVKfKFmf2f3dBG0rBinJFe
1QzRpgXT+MYJ5Jangem58Sz0zgG+WdcjKOBl36HuhD/jkc4u4rR4EPxgf4MH7ECG
I2IbW2duUtvkJ84zVub0Iqu7oMap4dX8d6yUpLesSO4dzlsoceI=
=BftV
-----END PGP SIGNATURE-----

--naeisljvozrc53ye--