PostgreSQL逻辑复制中关于发布表的两个小知识

作为发布的无主键表更新和删除报错

创建无主键表，并添加到发布列表，在执行update时报错

postgres=# select * from pg_publication_tables ;
 pubname  | schemaname | tablename 
----------+------------+--------------
 test_pub | public     | test      
(1 row)

postgres=# update test set id =2 where id=1;
ERROR:  cannot update table "test" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
postgres=# delete from test where id=1;
ERROR:  cannot delete from table "test" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

根据报错信息搜索源码

        /* If relation has replica identity we are always good. */
        if (rel->rd_rel->relreplident == REPLICA_IDENTITY_FULL ||
                OidIsValid(RelationGetReplicaIndex(rel)))
                return;

        /*
         * This is either UPDATE OR DELETE and there is no replica identity.
         *
         * Check if the table publishes UPDATES or DELETES.
         */
        pubactions = GetRelationPublicationActions(rel);
        if (cmd == CMD_UPDATE && pubactions->pubupdate)
                ereport(ERROR,
                                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
                                 errmsg("cannot update table \"%s\" because it does not have a replica identity and publishes updates",
                                                RelationGetRelationName(rel)),
                                 errhint("To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.")));
        else if (cmd == CMD_DELETE && pubactions->pubdelete)
                ereport(ERROR,
                                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
                                 errmsg("cannot delete from table \"%s\" because it does not have a replica identity and publishes deletes",
                                                RelationGetRelationName(rel)),
                                 errhint("To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.")));

在backend/executor/execReplication.c中，可以看到首先判断表的复制标识（pg_class->relreplident）是否为full或者复制标识为索引和主键时对应索引是否有效，如果符合条件则直接返回，否则当执行的SQL是update或delete时会报错

RelationGetIndexList(Relation relation)
    ......
     /* remember explicitly chosen replica index */
                if (index->indisreplident)
                        candidateIndex = index->indexrelid;
    ......
        relation->rd_pkindex = pkeyIndex;
        if (replident == REPLICA_IDENTITY_DEFAULT && OidIsValid(pkeyIndex))
                relation->rd_replidindex = pkeyIndex;
        else if (replident == REPLICA_IDENTITY_INDEX && OidIsValid(candidateIndex))
                relation->rd_replidindex = candidateIndex;
        else
                relation->rd_replidindex = InvalidOid;

为什么说上边RelationGetReplicaIndex(rel)返回的是复制标识为索引和主键时对应索引，在backend/utils/cache/relcache.c中可以看到对应逻辑，复制标识为default时返回主键索引，复制标识为index时返回作为发布标识的索引（pg_index->indisreplident）

创建发布且发布列表为所有表时，新创建的表是如何添加到发布列表的

postgres=# create publication  test_pub_all for all tables ;
CREATE PUBLICATION
postgres=# select * from pg_publication;
  oid  |   pubname    | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+--------------+----------+--------------+-----------+-----------+-----------+-------------+------------
 32869 | test_pub_all |       10 | t            | t         | t         | t         | t           | f
(1 row)
postgres=# select count(*) from pg_publication_tables;
 count
-------
    37
(1 row)
postgres=# create table ttt1(id int,name varchar);
CREATE TABLE
postgres=# select count(*) from pg_publication_tables;
 count
-------
    38
(1 row)

根据现象可以看到新创建一个表后，发布列表的行数也增加了一条，那新创建的表是如何添加到发布列表中去的呢？

\d+查看"pg_catalog.pg_publication_tables"，发现视图的数据通过函数pg_get_publication_tables()返回，并不是一个普通表。

is_publishable_class(Oid relid, Form_pg_class reltuple)
{
        return reltuple->relkind == RELKIND_RELATION &&
                !IsCatalogRelationOid(relid) &&
                reltuple->relpersistence == RELPERSISTENCE_PERMANENT &&
                relid >= FirstNormalObjectId;
}

根据函数名搜索代码，在backend/catalog/pg_publication.c中可以看到发布列表是如何判断出来的

1.表类型是r，普通表

2.不是系统表

3.是永久表，不是unlogged表或临时表

4.是普通对象，initdb之后创建的表

即：符合这几个条件的表都被认为是需要发布的表

知识总结

1.作为发布的无主键表更新和删除会报错，解决办法是

• 创建主键
• 创建非空列唯一索引，并指定唯一索引为复制标识
• 指定全列为复制标识

2.当发布列表为所有表时，新创建的普通表会自动被认为是需要发布的表，在视图pg_catalog.pg_publication_tables可以查看