Column used in the publication WHERE expression is not part of the replica identity

Question

I'm trying to create a logical replication setup where I have a single large database with smaller read replicas branching of filtered on a sub id.

For example, if I wanted a subset of items pushed to read replicas for a specific customer, I might have a table on the replication node defined by:

CREATE TABLE public.item (
       id text PRIMARY KEY, 
       data text, 
       customer_id integer
       );

I may or may not have sub_id on the subscription node (not a deal breaker detail). On the replication node, I can add data to the table as I please until I create the PUBLICATION like so:

CREATE PUBLICATION customer_items FOR TABLE public.item (
       id, 
       data
       ) WHERE (customer_id = 3);

At this point, I try something like

UPDATE public.item SET
    customer_id = 3 WHERE 
    id = 'GLAMDRING';

I get the error I get an SQL state: 42P10 error, or..:

ERROR:  cannot update table "item"
DETAIL:  Column used in the publication WHERE expression is not part of the replica identity.
STATEMENT: UPDATE public.item SET
    customer_id = $1::integer WHERE 
    id = 'GLAMDRING';

The issue here looks similar. I've recreating the table to remove any ALTER TABLE statements on id, as suggested there, but I have the same result.

Most examples I can find that use this logical replication function never make use of the WHERE clause. I'm thinking I've either misunderstood the meaning behind this feature, or I've found a bug/limitation.

Please note: I am aware that my example seems trivial and should be solved some other way. This is a simplified version of my problem for the sake of brevity.

---

Update

Trying to use Unique index, as suggested almost got me there.

CREATE UNIQUE INDEX id_customerid_idx ON public.item ((customer_id || id));

But when I try creating a useful PUBLICATION..

CREATE PUBLICATION customer_items FOR TABLE public.item (id, data) WHERE (customer_id || id LIKE '3%');

New error:

ERROR:  User-defined or built-in mutable functions are not allowed.invalid publication WHERE expression 

ERROR:  invalid publication WHERE expression
SQL state: 0A000
Detail: User-defined or built-in mutable functions are not allowed.
Character: 377

Maybe my use-case is a little wild or I need to rethink my application logic to work with these constraints. Am I missing an obvious work around here?

Answer 1

This is Working As Designed™

A row filter expression (i.e., the WHERE clause) must contain only columns that are covered by the REPLICA IDENTITY, in order for UPDATE and DELETE operations to be published.

I think the restriction is there to accommodate the UPDATE transformations:

Whenever an UPDATE is processed, the row filter expression is evaluated for both the old and new row

where "old" and "new" are the replica identity values, so the filter column must be a part of them.