2

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?

8
  • 1
    I guess you should create a unique index on (id, customer_id) and use that as the replica identity for the table. Jan 15 at 16:05
  • @LaurenzAlbe Thanks! For my use-case seems I need a user defined function, which aren't allowed (see update). Jan 15 at 19:20
  • "Trying to use Unique index, as suggested" -- that's now what was suggested though, why are you concatenating the columns? Have you tried exactly create index ... (id, customer_id)? With that index, set as the replica identity for the table, your original publication row filter should work.
    – mustaccio
    Jan 15 at 19:47
  • @mustaccio Yes, I tried the create index.. (id, customer_id) syntax first. Same/similar error. I'm guessing the query planner may be deciding to use only one indexed column. I've tried a few ways of attacking this and none get me there.Will try a few more permutations. Jan 15 at 20:07
  • 1
    Not sure what you're doing wrong, but the approach works for me: dbfiddle.uk/-DVUFpLp
    – mustaccio
    Jan 15 at 20:25

1 Answer 1

3

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.

1
  • RTFM, doh! Think I would have missed this implementation detail of how UPDATE works under the hood, even if I had found that line. Jan 15 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.