On Thu, Aug 4, 2022 at 10:03 AM Jim Mlodgenski <jimmy76@xxxxxxxxx> wrote:
I don't think you can make that assumption with the UUID data. Try this simple example and you can see the sort order changes on the newer OS and can lead to duplicates on your primary key if you don't reindex after the upgrade.
CREATE TABLE t1 (c1 varchar PRIMARY KEY);
INSERT INTO t1 VALUES ('1-a'), ('1a'), ('1-aa');
SELECT * FROM t1 ORDER BY c1;
Looks like you're right. Testing with PG 12.11 pgdg packages for their respective Ubuntu versions:
18.04:
testdb=# SELECT * FROM t1 ORDER BY c1;
c1
------
1a
1-a
1-aa
(3 rows)
testdb=# SELECT * FROM t1 ORDER BY c1;
c1
------
1a
1-a
1-aa
(3 rows)
22.04:
testdb=# SELECT * FROM t1 ORDER BY c1;
c1
------
1-a
1a
1-aa
(3 rows)
testdb=# SELECT * FROM t1 ORDER BY c1;
c1
------
1-a
1a
1-aa
(3 rows)
So it looks like we'd have to rebuild any UUID index to be safe anyway, which are used a lot for identifiers. *sadness intensifies*
Don Seiler
www.seiler.us
www.seiler.us
