Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u6moI-0050Xt-Li for pgsql-general@arkaria.postgresql.org; Mon, 21 Apr 2025 08:47:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u6moF-00307t-5u for pgsql-general@arkaria.postgresql.org; Mon, 21 Apr 2025 08:47:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u6moE-00307k-Ol for pgsql-general@lists.postgresql.org; Mon, 21 Apr 2025 08:47:23 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u6mo9-001CDO-2R for pgsql-general@lists.postgresql.org; Mon, 21 Apr 2025 08:47:23 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 7E8F6AFF2; Mon, 21 Apr 2025 10:47:17 +0200 (CEST) Date: Mon, 21 Apr 2025 10:47:17 +0200 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Order of update Message-ID: <20250421084717.4edbhu6a7duiqw42@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <20250420091033.n437fdrkihtjrncd@hjp.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="25qiflte44uo5cja" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --25qiflte44uo5cja Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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]. > >=20 > > One of the differences is that something like > >=20 > > UPDATE tab SET id =3D id + 1; > >=20 > > tends to fail on PostgreSQL because the the primary key constraint is > > checked for every row, so it will stumble over the temporary conflicts. > >=20 > > The solution is to define the constraint as deferrable. > >=20 > > But that got me to thinking about different ways ... > >=20 > > There won't be a conflict if the ids are updated in descending order. > > Is there a way to force PostgreSQL to update the rows in a specific > > order? > >=20 > > I came up with > >=20 > > with a as (select id from t where id > 50 order by id desc) > > update t set id =3D a.id+1 from a where t.id =3D a.id; > >=20 > > which works in my simple test case, but it doesn't look like it's > > guaranteed to work. The implicit join in =C2=ABupdate t ... from a=C2= =BB could > > produce rows in any order, especially for large tables. >=20 > My read of this Your read of the query, the PostgreSQL source or the SQL standard? > is that for the duration of the query a temporary table a is create > that is ordered on `id desc` and that '... from a where t.id =3D a.id' > will apply that order to the selection of t.id. Yes, that's the intention. In as I wrote it did work in my simple tests. But is it guaranteed to work? Is there anything in the standard that says that the order has to be preserved? Or failing that, is that the way it's currently implemented and there are reasons to assume that it will never be changed? > As example: >=20 > create table id_update(id integer primary key); >=20 > insert into id_update select a from generate_series(1, 100000) as t(a); > INSERT 0 100000 >=20 > -- id(s) are temporarily in order. >=20 > update id_update set id =3D id where id between 50000 and 60000; > UPDATE 10001 >=20 > -- The above move the 10001 values to 'end' of id_update >=20 > with a as (select id from id_update where id > 100 order by id desc) upda= te > id_update as t set id =3D a.id + 1 from a where t.id =3D a.id; > UPDATE 99900 I note that this produces a hash join: #v+ =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=97 =E2=95=91 QUERY PLAN = =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2 =E2=95=91 Update on id_update t (cost=3D3179.42..8662.63 rows=3D0 width=3D= 0) =E2=95=91 =E2=95=91 -> Hash Join (cost=3D3179.42..8662.63 rows=3D99899 width=3D38= ) =E2=95=91 =E2=95=91 Hash Cond: (a.id =3D t.id) = =E2=95=91 =E2=95=91 -> Subquery Scan on a (cost=3D0.42..4971.64 rows=3D9989= 9 width=3D32) =E2=95=91 =E2=95=91 -> Index Only Scan Backward using id_update_pkey o= n id_update (cost=3D0.42..3972.65 rows=3D99899 width=3D4) =E2=95=91 =E2=95=91 Index Cond: (id > 100) = =E2=95=91 =E2=95=91 -> Hash (cost=3D1929.00..1929.00 rows=3D100000 width=3D= 10) =E2=95=91 =E2=95=91 -> Seq Scan on id_update t (cost=3D0.00..1929.00 = rows=3D100000 width=3D10) =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D #v- 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=3D> explain with a as (select id from id_update where id > 90000 order by id desc) update id_update as t set id =3D a.id + 1 from a where a.id =3D t.id; =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=97 =E2=95=91 QUERY PLA= N =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=95=A2 =E2=95=91 Update on id_update t (cost=3D732.53..2675.61 rows=3D0 width=3D0= ) =E2=95=91 =E2=95=91 -> Hash Join (cost=3D732.53..2675.61 rows=3D10006 width=3D38)= =E2=95=91 =E2=95=91 Hash Cond: (t.id =3D a.id) = =E2=95=91 =E2=95=91 -> Seq Scan on id_update t (cost=3D0.00..1443.00 rows= =3D100000 width=3D10) =E2=95=91 =E2=95=91 -> Hash (cost=3D607.46..607.46 rows=3D10006 width=3D32)= =E2=95=91 =E2=95=91 -> Subquery Scan on a (cost=3D0.29..607.46 rows= =3D10006 width=3D32) =E2=95=91 =E2=95=91 -> Index Only Scan Backward using id_update_= pkey on id_update (cost=3D0.29..507.40 rows=3D10006 width=3D4) =E2=95=91 =E2=95=91 Index Cond: (id > 90000) = =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=9D #v- Looks like we got it. And indeed: #v+ hjp=3D> with a as (select id from id_update where id > 90000 order by id de= sc) update id_update as t set id =3D a.id + 1 from a where a.id =3D t.id; ERROR: duplicate key value violates unique constraint "id_update_pkey" DETAIL: Key (id)=3D(90002) already exists. #v- So, obviously that isn't guaranteed to work. hjp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --25qiflte44uo5cja Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmgGBg4ACgkQ8g5IURL+ KF3aZA/+Jg9NHGMYc4Dbp26b5EXXOjgYiEscmAx0lSCk95nW/ZTK4Si7ibwtJqyo 7rIpCQ8IGLJxASHCzNQz8qceCoTraVUFGA+31fQ1b7aQOV96yDiXTURmpG+TgrSj B74aJNivpYnFSZPF9dzM+8YQj+t93IYInRxy+mWTX2THVk8EynrWEX3wQq1UBFGp xlSJ14M2gTugV4PxnzoilT6wPSAWbqYxBK3+Mcmdk2A+fCIl5i4kWwqo/6WR3KsN v56ndkRoMHFRB6PZPvKaUYCReMc6Sj7S5MvFZ10heydXa95kcBfPruLK7VCp4FkH hlAAL1SXTTvRrJF6Ao6tMkrU1st/QG/Vx7ZnX69U00sjIXtPyWsIpPCdW1bDUlSy F+x/2jrB1F2e1+4XUDRggGldknAK3NdTdL6+h3++MAwhx8MGDl7jRJs79SpwPUgd +TUe2k8kHXCE3baQZBmM9b20xHETUnSvVOgah1pg4fAQW4lly2ST6qiO/rnno/Am vSgFukIVbX6qiyGgiKAivTUwlsKipKXZXcv8RCKvdh1E29BIuL8k5dFG0Azx55xd xwsQe6MSVcgaLvS99cJGVdx9Dx5m5SCaWBs6xopLJmRtv0nGyNeABrWOnf2QJz6K 4riCMtj3uU4cRKPgKRnnLjhUBbSln7FdCQZN8tVdKh+cf8MdDw4= =TrtH -----END PGP SIGNATURE----- --25qiflte44uo5cja--