Column used in the publication WHERE expression is not part of the replica identity
2
votes
2
answers
831
views
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?
Asked by goldfishalpha
(123 rep)
Jan 15, 2024, 03:23 PM
Last activity: Feb 6, 2025, 05:26 PM
Last activity: Feb 6, 2025, 05:26 PM