"table does not have PRIMARY KEY"... but it does

[Xof]

The output here is reasonably self-expanatory. We're trying to add a table to a replication set, and pglogical 2.1.1 is complaining that it does not have a primary key... but it does.

staging=# \d+ t_1
                                                            Table "t_1"
   Column   |            Type             |                                   Modifiers                                    | Storage | Stats target | Description 
------------+-----------------------------+--------------------------------------------------------------------------------+---------+--------------+-------------
 signup_id  | integer                     | not null                                                                       | plain   |              | 
 mailing_id | integer                     | not null                                                                       | plain   |              | 
 created_at | timestamp without time zone |                                                                                | plain   |              | 
 id         | integer                     | not null default nextval('t_1_id_seq'::regclass)                               | plain   |              | 
Indexes:
    "t_1_pkey" PRIMARY KEY, btree (id)
    "index_t_1_on_created_at" btree (created_at)
    "index_t_1_on_mailing_id" hash (mailing_id)
    "index_t_1_on_signup_id" hash (signup_id)
Replica Identity: FULL

staging=# select * from pglogical.replication_set_add_table('abc_replication_set', 't_1');
ERROR:  table t_1 cannot be added to replication set abc_replication_set
DETAIL:  table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT:  Add a PRIMARY KEY to the table

[mpasquini]

Hi, I've found the same issue, set replica identity to "DEFAULT" and the error will disappear.

I do not understand why it happen, replica FULL add full "OLD", "NEW" data to the wal file... I'm waiting an answer from 2ndQuadrant.

[remyduthu]

Hi, I have the exact same problem and I need Replica Identity: FULL for some tables... Is there any other solution ?

[ravula2108]

I have landed on the same problem, any update on this?

[atsu85]

same here, any update on this?

[atsu85]

Based on the documentation, "REPLICA IDENTITY FULL is not supported yet"

PRIMARY KEY or REPLICA IDENTITY required UPDATEs and DELETEs cannot be replicated for tables that lack a PRIMARY KEY or other valid replica identity such as using an index, which must be unique, not partial, not deferrable, and include only columns marked NOT NULL. Replication has no way to find the tuple that should be updated/deleted since there is no unique identifier. REPLICA IDENTITY FULL is not supported yet.

Perhaps the the error message should be improved (until FULL becomes supported), because currently it is misleading:

DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs

[gowthamakanthan]

Is there any update on this?

[kyletruong]

Any known workarounds to capture INSERT and DELETE operations for tables configured with REPLICA IDENTITY FULL?

[rotten]

alter table mytable replica identity default worked to get past this issue for one table, but it isn't working for another. Both tables clearly have primary keys. Is there some other property that is causing the primary key check to fail when it shouldn't?

[rotten]

alter table mytable replica identity default worked to get past this issue for one table, but it isn't working for another. Both tables clearly have primary keys. Is there some other property that is causing the primary key check to fail when it shouldn't?

My bad - actually it did work. The tables I'm running into this issue on also have native replication configured to another they all require full so setting replica identity to default may break my other replication. :-(

SELECT 
    relname,
    CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class

^ shows me the current replica identity values.

[rotten]

Since tables can only have one Replica Identity value, and since my other service requires it be set to full, I have to scrap the idea of using pglogical. I don't see any obvious work around. At a minimum I'd suggest updating the error message that complains a table doesn't have a primary key to instead reflect that the table has an unsupported replica identity setting.

[diegoful-fr]

I ran into the same issue on the latest version of pglogical. I agree @rotten on this one.