Date: Sun, 20 Apr 2025 11:10:33 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@postgresql.org
Subject: Order of update
Message-ID: <20250420091033.n437fdrkihtjrncd@hjp.at>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="57ch5nvq32ure6x7"
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/20250420091033.n437fdrkihtjrncd%40hjp.at>
Precedence: bulk


--57ch5nvq32ure6x7
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 =3D id + 1;

tends to fail on PostgreSQL because the the primary key constraint is
checked for every row, so it will stumble over the temporary conflicts.

The solution is to define the constraint as deferrable.

But that got me to thinking about different ways ...

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?

I came up with

    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;

which works in my simple test case, but it doesn't look like it's
guaranteed to work. The implicit join in =ABupdate t ... from a=BB could
produce rows in any order, especially for large tables.

So, is there a better way?

        hjp


[1] https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-sy=
stems-of-oracle-and-postgresql/

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmgEugMACgkQ8g5IURL+
KF3ToA//XxLYvC/SrBkh8rivrtrlTGDr7P6KXXOOM7n/jkRW40xgls2sYNtOrCCv
kSS/tb8DKUZmwNOoPA32WS/ynzhX3C4r7NgBRIcbr9JAgVQgfmsv651mv4HKqEgr
5TkqLeELFoABzBW3zAWvayNweKMfSf25uo0qrt1HNFHNuZyORgC6Pc/i6i6XxZT2
m0LEKRF2C8Xqiw0DJ+2vR7otmeKu13pkqPsi3udbnEPBUitPxSxZvTZEBDjjfAIJ
BgtH5j2SN9HQV8WaCKTvuy5MkE4d8ognYOYkOLjac6T/jLo2ua32k71WBa30mpuQ
xUOmCn+S91hThDFUlVqrDZwGAmh0Mn8JucXkbPW4awSXAizXfZVj549K1DSUD5QO
XgWmFzyp3FLv04cAYn8tU0BarjV+i+/5XfnZ7OtD5kDoCzmwmbLgJd2ouIssl2kf
3kBz1KUf6PDn+lzly13qeA+ILWpqAPH5D/jDFA40Kr2UhJGOpjxXIuN13e0ypc6K
shYxxnnu51bJsjdzeVL0Y01FOzhuqr7gCm4ttaPkDjpConyyW8m80JobcY3Jl+yh
Nu3w7QLq7q0TUM1YD6gXQHbysno7dT7T7ZOrmIqGvb/7AYSATlIp+BZcDrL7w0Tu
YhLOXVBRAQmMGTqDyLc7e1MNrF+y5/unFNp9ATwbeR20C6Ijlc0=
=Pvia
-----END PGP SIGNATURE-----

--57ch5nvq32ure6x7--