前两天同事问了一个PG的错误，创建一张普通表，insert插入正常，但是执行update和delete时，提示这个错误，
代码语言：javascript复制SQL 错误 [55000]: ERROR: cannot delete from table "temp_tb" because it does not have a replica identity and publishes updates
Hint: To enable updating from the table, set REPLICA IDENTITY using ALTER TABLE.
SQL 错误 [55000]: ERROR: cannot delete from table "temp_tb" because it does not have a replica identity and publishes deletes
Hint: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.PG确实接触少，顺着错误，捋一捋，首先是PG版本，是12，
代码语言：javascript复制SELECT current_setting('server_version_num');从报错来看，为了能执行更新和删除操作，需要用ALTER TABLE设置REPLICA IDENTITY，其实讲的很清楚，根据一些资料，
如果一张表缺少主键，并且涉及到复制，就需要执行这条语句，
代码语言：javascript复制ALTER TABLE temp_tb REPLICA IDENTITY FULL;执行完成，确实能正常更新和删除了。但这是为什么？
首先了解下，逻辑复制的概念。逻辑复制是PostgreSQL V10重量级新特性，支持内置的逻辑复制。在10版本之前，虽然没有内置的逻辑复制，也可以通过其它方式实现，例如触发器、自定义脚本实现表级别同步，另外也可以通过外部工具Londiste3实现。从9.4版本开始，PostgreSQL就支持逻辑复制了，只是一直没有将其引入内核。可以针对同一个数据库实例，同时使用逻辑复制和物理复制，因为他们都是基于REDO的。
逻辑复制的应用场景，可基于表级别复制，是一种粒度可细的复制，主要用在以下场景，
(1) 满足业务上需求，实现某些指定表数据同步。
(2) 报表系统，采集报表数据。
(3) PostgreSQL 跨版本数据同步。
(4) PostgreSQL 大版本升级。
(5) 可从多个上游服务器,做数据的聚集和合并。
逻辑复制原理，使用发布者/订阅者模型，使用订阅复制槽技术，可并行的传输WAL日志，通过在订阅端回放WAL日志中的逻辑条目，保持复制表的数据同步，注意这里不是“SQL”复制，而是复制SQL操作的结果。
逻辑复制的主要组成
(1) publication - 发布
可以在任何物理复制主机上定义发布；定义发布的节点称为发布者。发布是从一个表或一组表中生成的一组更改，也可能被描述为更改集或复制集。每个发布只存在于一个数据库中。发布与模式不同，不影响表格的访问方式；如果需要，每张表可以添加到多个发布。发布目前可能只包含表；对象必须显式添加， 除非为ALL TABLES创建了一个发布。发布可以选择将它们所产生的改变限制在INSERT，UPDATE和DELETE的任意组合上，类似于触发器。默认情况下，复制所有操作类型。
(2) subscription - 订阅
就是订阅变更的内容。
(3) Replication Slots - 复制槽(发布端)
每个(活动)订阅都从远程(发布)端的复制槽接收更改。通常，使用CREATE SUBSCRIPTION创建订阅时会自动创建远程复制槽，使用DROP SUBSCRIPTION删除订阅时会自动删除该槽。复制槽提供了一种自动化的方法来确保主控机在所有的后备机收到WAL段之前不会移除它们，主库随时知道从库应用WAL的情况，哪怕从库掉线，主库依然保留WAL日志。这种机制的缺点是，如果从库掉线很久, 那么主库的WAL日志会一直保留以至于撑暴硬盘，这时监控需要做到位。
关于发布端和订阅端，
(1) 发布端
逻辑复制的前提是将数据库wal_level参数设置成logical。
源库上逻辑复制的用户必须具有replicatoin或superuser角色。
逻辑复制目前仅支持数据库表逻辑复制，其它对象例如函数、视图不支持。
逻辑复制支持DML(UPDATE、INSERT、DELETE)操作，TRUNCATE 和 DDL 操作不支持。
需要发布逻辑复制的表，须配置表的REPLICA IDENTITY特性。
一个数据库中可以有多个publication，通过pg_publication查看。
允许一次发布所有表，语法，
代码语言：javascript复制CREATE PUBLICATION alltables FOR ALL TABLES;(2) 订阅端
订阅节点需要指定发布者的连接信息。
一个数据库中可以有多个订阅者。
可以使用enable/disable启用/暂停该订阅。
发布节点和订阅节点表的模式名、表名必须一致，订阅节点允许表有额外字段。
发布节点增加表名，订阅节点需要执行：
代码语言：javascript复制ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION被复制的表上最好有主键约束；如果没有，必须执行，
代码语言：javascript复制ALTER TABLE reptest REPLICA IDENTITY FULL;REPLICA IDENTITY，复制标识，共有4种配置模式，分别为，
(1) 默认模式(default)：
非系统表采用的默认模式，如果有主键，则用主键列作为身份标识，否则用完整模式。
(2) 索引模式(index)：将某一个符合条件的索引中的列，用作身份标识。
(3) 完整模式(full)：将整行记录中的所有列作为复制标识(类似于整个表上每一列共同组成主键)。
(4) 无身份模式(nothing)：不记录任何复制标识，这意味着UPDATE|DELETE操作无法复制到订阅者上。
表改复制标识可以通过ALTER TABLE进行修改。
代码语言：javascript复制ALTER TABLE tbl REPLICA
IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING };具体有四种形式，
(1) 使用主键，如果没有主键则为FULL，
代码语言：javascript复制ALTER TABLE t_normal REPLICA IDENTITY DEFAULT;(2) 使用整行作为标识，
代码语言：javascript复制ALTER TABLE t_normal REPLICA IDENTITY FULL;(3) 使用唯一索引，
代码语言：javascript复制ALTER TABLE t_normal REPLICA IDENTITY USING INDEX t_normal_v_key;(4) 不设置复制标识，
代码语言：javascript复制ALTER TABLE t_normal REPLICA IDENTITY NOTHING;如果没有任何复制标识，可以将复制标识设置为FULL，也就是把整个行当作复制标识。但是，使用FULL模式的复制标识效率很低，所以这种配置只能是保底方案，或者用于很小的表。因为每一行修改都需要在订阅者上执行全表扫描，很容易将订阅者拖垮。
模拟创建过程，并证明下这个库像上面所说设置了相关的参数配置。
1. 创建表，插入正常，更新和删除，就会提示55000错误，
代码语言：javascript复制CREATE TABLE temp_tb (
code varchar(32) COLLATE pg_catalog.default primary key,
name varchar(32) COLLATE pg_catalog.default,
aliases jsonb
)
;执行如下语句，更新和删除正常，
代码语言：javascript复制ALTER TABLE reptest REPLICA IDENTITY FULL;2. 查询当前表的复制属性，返回值是logical，说明设置了逻辑复制，
代码语言：javascript复制select setting from pg_settings where name='wal_level';3. 查询表当前复制标识，返回值是f，说明这张表确实设置了复制，而且是基于所有列的，
代码语言：javascript复制select relreplident from pg_class where relname='temp_tb';返回值说明，
d = 默认(主键，如果存在)
n = 无
f = 所有列
i = 索引的indisreplident被设置或者为默认
4. 查询视图，temp_tb隶属于test_publication这个发布，
代码语言：javascript复制select * from pg_publication_tables where tablename='temp_tb';
pubname schemaname tablename
test_publication public temp_tb5. 检索这个发布，
代码语言：javascript复制select * from pg_publication;puballtables确实勾选了，
说明这个发布，发布所有表中的所有更改，
代码语言：javascript复制CREATE PUBLICATION test_publication FOR ALL TABLES;pg_publication的字段定义，
因此，可以问创建test_publication的人有什么意图，或者将逻辑复制logical更改成物理复制，如果逻辑复制是必须的，可能有效的方式，就是设置主键，其实从数据库应用设计开发的规范角度，无特殊情况，还是要为表创建一个主键，而且推荐是无意义的类型，例如序列。
参考链接，
http://postgres.cn/docs/12/catalog-pg-publication.html
https://stackoverflow.com/questions/56000702/cannot-update-table-temp-table-because-it-does-not-have-a-replica-identity-and-p
https://www.pgfans.cn/a?id=194
http://postgres.cn/docs/12/logical-replication-publication.html
http://postgres.cn/docs/12/sql-createpublication.html
https://www.bbsmax.com/A/GBJrvBkZJ0/
https://pigsty.cc/zh/blog/2021/03/03/pg复制标识详解replica-identity/
近期更新的文章：
《最近碰到的几个问题》
《Linux的dd指令》
《Oracle、SQL Server和MySQL的隐式转换异同》
《JDK的版本号解惑》
《新增字段在数据块中的体现》
文章分类和索引：
《公众号700篇文章分类和索引》
