To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

原因是发布订阅的表没有主键，不能进行update和delete操作。

Q

MogDB=#update pub_sub set name = 'e' where name = 'd';
ERROR:  cannot update table "pub_sub" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

pub端

MogDB=#\d+ pub_sub
                                                  Table "public.pub_sub"
 Column |       Type        |                      Modifiers                      | Storage  | Stats target | Description
--------+-------------------+-----------------------------------------------------+----------+--------------+-------------
 i      | integer           | not null default nextval('pub_sub_i_seq'::regclass) | plain    |              |
 name   | character varying |                                                     | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

sub端

MogDB=#\d pub_sub
                              Table "public.pub_sub"
 Column |       Type        |                      Modifiers
--------+-------------------+-----------------------------------------------------
 i      | integer           | not null default nextval('pub_sub_i_seq'::regclass)
 name   | character varying |

A

pub，sub两端添加主键

MogDB=#create unique index concurrently i_pk on pub_sub(name);
CREATE INDEX
MogDB=#alter table pub_sub add CONSTRAINT  pk_pub_sub primary key using index i_pk;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "i_pk" to "pk_pub_sub"
ALTER TABLE

pub端

MogDB=#select * from pub_sub;
 i | name
---+------
 1 | a
 1 | b
 6 | c
 7 | d
(4 rows)

MogDB=#update pub_sub set i=8 where name = 'd';
UPDATE 1
MogDB=#select * from pub_sub;
 i | name
---+------
 1 | a
 1 | b
 6 | c
 8 | d
(4 rows)

sub端

MogDB=#select * from pub_sub;
 i | name
---+------
 1 | a
 1 | b
 6 | c
 8 | d
(4 rows)

MogDB=#