pg 唯一性约束修复

先说一下背景，之前我用的 pg 镜像是 postgres:11.18-bullseye，后来被升级成了 postgres:11.20-alpine3.18，这个造成的其中一个后果简而言之是 pg 对字符串的排序发生了变化，比如原先认为 A > B，现在则变成了 A < B。由此，就有可能破坏数据的唯一性约束。不仅如此，只要索引列包含字符串类型的字段，也会有问题。

为了方便继续描述，假设有个 test 表，结构如下

字段名	类型	说明
name	varchar(128)	主键
age	int

为了加深理解，假设这个表已经包含了重复数据，重复数据的 name = ‘A’，age 分别是 11 和 21。

思考一下如下这两条 SQL 的结果分别是什么

select name, count(*) from test group by name;

select name, count(*) from test where name = 'A' group by name;

第一条 SQL 语句大概率会返回 ('A', 2)，第二条大概率会返回 ('A', 1)。造成这个现象的原因是第一条语句是全表扫描，第二条语句是走的索引。

如何解决

这种情况除了重建整个数据库的索引外，没有其他办法。但是在重建唯一索引之前，需要先删除重复的数据以维护唯一性约束。

这个问题貌似很简单，但要处理如下几个事情：

1. 找出重复的数据
2. 区分重复的数据
3. 确定数据保留标准，这里假设保留新数据。

找出重复的数据已经有办法了，通过 group by 和 count(*) 可以找出哪些数据重复了。但是剩下两个事情呢，考虑下我们上文提的例子，如何为 test 表确定哪个数据更新呢？test 表可没有自增主键ID，也没有相关的时间字段。建表不规范让这个问题更难处理。

pg 的系统字段

让我们先了解下 pg 中的隐藏字段

1. ctid。他记录的是行的物理存储信息，即使两条数据完全一样，ctid 也会不一样。但是 ctid 是易变的，比如更新之后，ctid 也会变化，所以需要考虑并发问题。
2. xmin。他主要用在 MVCC 中，记录的是行被插入时的事务 ID，pg 通过比较行的事务 ID 和 当前事务 ID 的大小来判断该行对当前事务是否可见。所以事务 ID 一般情况下是递增的。但是也不能完全通过 xmin 的大小判断哪个行是先插入的，原因如下：
   1. MVCC 机制使得在更新记录的时候，其实是写入行的新版本，新版本行的 xmin 是会变的。
   2. 事务 ID 是32位的，在数据库长久运行后，事务ID会产生回卷，即从 3 开始继续递增，详见   防止事务ID回卷失败

解决方案

思虑再三，我没有发现一个普适的方案，能够解决如下这个问题，如果有的话，希望有个好心人告知我一下

在某个表已经破坏了唯一性约束后，若这个表没有 自增ID，创建时间，更新时间等可以标志记录写入先后顺序字段的话，很难去删除旧的重复数据

不过还好，我的工作并不是需要去证明什么，有时候也不需要完美的解决方案，在借助上面提到的 ctid 和 xmin 字段，再加上业务背景，我的解决方案是：

1. 禁用索引
2. 通过 group by 找出重复的记录
3. 获取重复记录的 ctid 和 xmin，有些表还有 created_at 字段，一并获取
4. 通过 order by created_at desc, xmin desc，获取到第一条记录，并认为它是最新的，保留它，通过 ctid 删除其他所有

话外

这个事情是由于升级 pg 版本导致的，对于升级这个事情，我的观点还是保守的，能不升就不升。不过这里还暴露出来了另外一个事情，建表的规范。如果我们的表都满足如下两个要求，这个事情就会简单很多：

1. 使用自增主键。
2. 带上 created_at, updated_at 字段

有时候挺奇怪的，可能每个研发，每个团队的关注点不一样，但是像这种建表的规范不应该是最基础的吗？我们的所有工作都是围绕数据存储展开，那最基本的表的结构，索引的创建，是不是也应该受到更多关注，成为各种评审的重中之重。前人挖坑，后人填坑，后人还有后来人。

在思考方案的过程中，我还想到过另外一个方案，简述如下：

1. 通过 group by 找到重复的数据
2. 通过走对应的唯一索引，查出当前能查到的那条记录。这条记录认为是最新的，删除其他重复记录

但是这个有如下几个问题：

1. 如何确保一定走了唯一索引，有两种方式：
   1. pg 可以通过装个扩展，从而实现类似 MySQL 的索引提示功能
   2. 每次只查一条记录，这个基本上应该是走的索引，除非你的表数据量足够的小。除此之外，若重复记录较多，则效率也慢
2. 即使走了唯一索引，一定会只返回一条记录吗（这个没实验过，等待一个好心人）。之所以有这个顾虑，是考虑到B+树的叶子结点，一般对应的是数据页，数据库页里包含多个数据记录。如果一次查询，返回的数据页里恰好同时包含了重复的记录呢，这个时候不知道是否会返回两条重复的记录。