Spoc extension unable to resolve for Upserts

[SID2025GIT]

Hi,

When an UPDATE statement from the provider arrives, the Spock subscriber side row does not exist, expectation is it should convert update into an insert statement . which is not happening , in the exception_status logging as below.
Could please help , how to resolve this automatically.

select * from exception_status; | [{"value": 102, "attname": "id", "atttype": "int4"}, {"value": "sid2", "attname": "name", "a
tttype": "varchar"}, {"value": "sid102", "attname": "last_modified_by", "atttype": "varchar"}] | | | logical replication did not find row to be update
d in replication target relation (siddu.t3)
(7 rows)

-Sid

[danolivo]

Typically, for us, an UPDATE before INSERT indicates that there is a race condition, broken topology, or some other issue.
You're proposing an interesting idea. What is your case? Why does it happen? When may it be beneficial in production?

[SID2025GIT]

Hi ,

Thanks for the response, this feature available in Oracle goldengate(GG). This is is very beneficial for prod use cases..
Also, How to exclude replication from a particular user. ? in GG we can exclude particular user. This will help in deploying DDL changes idependently.

-Sid

[danolivo]

This is is very beneficial for prod use cases

Thanks. I would like to realise specific use cases, at least one, just to understand how it is intended to work and how to implement it.

How to exclude replication from a particular user

Right now we don't have such feature. I've never heard about it before. What is the real-life use case?

[mason-sharp]

@SID2025GIT, while we convert an INSERT into an UPDATE if the row exists and the insert is newer than the existing row, we currently do not convert an UPDATE into an INSERT when the row does not exist.

In terms replicating based on user, if you have the userid in a table, you can try using row filters: https://docs.pgedge.com/spock-v5/spock_functions/functions/spock_repset_add_table/

[SID2025GIT]

create table siddu,t3 (id int , name varchar(20), last_modified_by varchar(20)) ));
alter table siddu.t3 add constraint p1 primary key (id);

SELECT spock.repset_add_table(
'rset_default_bi',
'siddu.t3',
row_filter := 'last_modified_by <> ''siduser'''
);

siddb=# insert into t3 values (102,'sid2','siduser');
INSERT 0 1
siddb=# select * from t3;
id | name | last_modified_by
-----+------+------------------
100 | sid1 |
101 | sid1 | sid
102 | sid2 | siduser

2. Noticed that id=102 not replicated to other replicated node N2 which is expected as per row filter , this fine working as expected .
3. in N2 we dont have record for ID=102
4. on N1 , I am going to update
   update t3 set last_modified_by='diffuser' where id=102;
5. expectation is , the update should be converted into Insert and inserted a record on N2 which is not happening. this is the one of the Prod use case.

[danolivo]

5. expectation is , the update should be converted into Insert and inserted a record on N2 which is not happening. this is the one of the Prod use case.

Now I got it, thanks. If I understand correctly, UPDATE -> INSERT conversion is coupled with replication filters. This behaviour may actually be controlled by the publisher, avoiding the uncertainty for the subscriber. That makes sense.

[SID2025GIT]

Any solution for this. ? is it possible to make it in near future releases. ?

[danolivo]

It needs a design beforehand. Currently, it is unclear how much time and effort are required to implement.