pg 唯一性约束修复

2024-09-02 16:36
文章标签 修复 约束 pg 唯一性

本文主要是介绍pg 唯一性约束修复,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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

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

字段名类型说明
namevarchar(128)主键
ageint

为了加深理解,假设这个表已经包含了重复数据,重复数据的 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+树的叶子结点,一般对应的是数据页,数据库页里包含多个数据记录。如果一次查询,返回的数据页里恰好同时包含了重复的记录呢,这个时候不知道是否会返回两条重复的记录。

这篇关于pg 唯一性约束修复的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1130489

相关文章

mss32.dll文件丢失怎么办? 电脑提示mss32.dll丢失的多种修复方法

《mss32.dll文件丢失怎么办?电脑提示mss32.dll丢失的多种修复方法》最近,很多电脑用户可能遇到了mss32.dll文件丢失的问题,导致一些应用程序无法正常启动,那么,如何修复这个问题呢... 在电脑常年累月的使用过程中,偶尔会遇到一些问题令人头疼。像是某个程序尝试运行时,系统突然弹出一个错误提

电脑提示找不到openal32.dll文件怎么办? openal32.dll丢失完美修复方法

《电脑提示找不到openal32.dll文件怎么办?openal32.dll丢失完美修复方法》openal32.dll是一种重要的系统文件,当它丢失时,会给我们的电脑带来很大的困扰,很多人都曾经遇到... 在使用电脑过程中,我们常常会遇到一些.dll文件丢失的问题,而openal32.dll的丢失是其中比较

电脑win32spl.dll文件丢失咋办? win32spl.dll丢失无法连接打印机修复技巧

《电脑win32spl.dll文件丢失咋办?win32spl.dll丢失无法连接打印机修复技巧》电脑突然提示win32spl.dll文件丢失,打印机死活连不上,今天就来给大家详细讲解一下这个问题的解... 不知道大家在使用电脑的时候是否遇到过关于win32spl.dll文件丢失的问题,win32spl.dl

电脑提示msvcp90.dll缺少怎么办? MSVCP90.dll文件丢失的修复方法

《电脑提示msvcp90.dll缺少怎么办?MSVCP90.dll文件丢失的修复方法》今天我想和大家分享的主题是关于在使用软件时遇到的一个问题——msvcp90.dll丢失,相信很多老师在使用电脑时... 在计算机使用过程中,可能会遇到 MSVCP90.dll 丢失的问题。MSVCP90.dll 是 Mic

电脑报错cxcore100.dll丢失怎么办? 多种免费修复缺失的cxcore100.dll文件的技巧

《电脑报错cxcore100.dll丢失怎么办?多种免费修复缺失的cxcore100.dll文件的技巧》你是否也遇到过“由于找不到cxcore100.dll,无法继续执行代码,重新安装程序可能会解... 当电脑报错“cxcore100.dll未找到”时,这通常意味着系统无法找到或加载这编程个必要的动态链接库

mysql8.0无备份通过idb文件恢复数据的方法、idb文件修复和tablespace id不一致处理

《mysql8.0无备份通过idb文件恢复数据的方法、idb文件修复和tablespaceid不一致处理》文章描述了公司服务器断电后数据库故障的过程,作者通过查看错误日志、重新初始化数据目录、恢复备... 周末突然接到一位一年多没联系的妹妹打来电话,“刘哥,快来救救我”,我脑海瞬间冒出妙瓦底,电信火苲马扁.

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

poj 3159 (spfa差分约束最短路) poj 1201

poj 3159: 题意: 每次给出b比a多不多于c个糖果,求n最多比1多多少个糖果。 解析: 差分约束。 这个博客讲差分约束讲的比较好: http://www.cnblogs.com/void/archive/2011/08/26/2153928.html 套个spfa。 代码: #include <iostream>#include <cstdio>#i

poj 3169 spfa 差分约束

题意: 给n只牛,这些牛有些关系。 ml个关系:fr 与 to 牛间的距离要小于等于 cost。 md个关系:fr 与 to 牛间的距离要大于等于 cost。 隐含关系: d[ i ] <= d[ i + 1 ] 解析: 用以上关系建图,求1-n间最短路即可。 新学了一种建图的方法。。。。。。 代码: #include <iostream>#include

基于51单片机的自动转向修复系统的设计与实现

文章目录 前言资料获取设计介绍功能介绍设计清单具体实现截图参考文献设计获取 前言 💗博主介绍:✌全网粉丝10W+,CSDN特邀作者、博客专家、CSDN新星计划导师,一名热衷于单片机技术探索与分享的博主、专注于 精通51/STM32/MSP430/AVR等单片机设计 主要对象是咱们电子相关专业的大学生,希望您们都共创辉煌!✌💗 👇🏻 精彩专栏 推荐订阅👇🏻 单片机