Postgres存在匹配行时DELETE WHERE语句未返回结果的原因及解决方法

无法匹配删除的常见原因 #

• 事务未提交：手动在命令行插入的测试数据如果未执行COMMIT，变更仅对当前命令行会话可见，pg模块对应的独立数据库会话无法访问未提交的数据，因此匹配不到目标行。
• 参数传递异常：
  • 传参顺序颠倒：代码调用时将song_id传入$1占位符、playlist_id传入$2占位符，WHERE条件完全不匹配
  • 参数类型不匹配：字段为整数类型时，代码传入了带多余空格、引号的字符串类型值，隐式转换后无法匹配到正确数值
• 连接环境不对：pg模块连接的数据库、schema与命令行操作的不是同一套环境，两边数据不互通
• 行级安全策略限制：如果表开启了PostgreSQL的行级安全(RLS)，当前连接使用的数据库角色没有对应行的访问/删除权限，会直接返回无匹配数据，不会提示权限不足
• 空值匹配问题：如果两个外键字段允许为NULL，等值运算符=无法匹配NULL值，需要改用IS NULL做判断

正确排查处理步骤 #

1. 命令行插入测试数据后执行COMMIT提交事务，再重试删除操作
2. 打印代码中DELETE语句传入的两个参数的实际值、数据类型，和命令行查询到的playlist_id、song_id值做对比，确认顺序、类型、值完全一致
3. 在代码中先执行同条件的SELECT语句，验证代码连接的会话是否能查到目标数据：

SELECT id, playlist_id, song_id FROM playlistsongs WHERE playlist_id = $1 AND song_id = $2

4. 检查表是否开启了行级安全，执行以下SQL确认：

SELECT relrowsecurity FROM pg_class WHERE relname = 'playlistsongs';

如果返回结果为t说明已开启RLS，需要调整安全策略或者给对应角色授予访问权限。
5. 如果字段允许为NULL，调整WHERE条件对应NULL值的判断逻辑。

内容的提问来源于stack exchange，提问作者Audi