I will preface with saying that I'm no SQL expert.
I have tables that exists in multiple databases in a hub and spoke style architecture where there are Machine DBs which logically publishes their data into their relevant Site DB which logically publishes its data into a single National DB where data is reported on.
At the Machine and Site levels, records are inserted into the tables at both levels i.e. Machines insert records into their own tables, and Sites insert records into their own tables. Site tables should contain both the records inserted by the site as well as the records inserted by the Machines, hence subscribing to the Machine databases to replicate this up into the site DB. Then the National DB should contain all of the records from all sites (and by extension, all machines), hence they replicate the data from all sites.
One of these tables has the following cutdown definition:
CREATE TABLE IF NOT EXISTS public.dat_part_roll_log
(
job_run_uid character(16) COLLATE pg_catalog."default" NOT NULL,
part_uid character(18) COLLATE pg_catalog."default",
special_part_type_id integer,
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 20000 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
uid character varying(60) COLLATE pg_catalog."default" NOT NULL GENERATED ALWAYS AS (get_part_log_uid((job_run_uid)::text, (part_uid)::text, special_part_type_id, id)) STORED
)
The top four fields job_run_uid, part_uid, special_part_type_id, and id universally uniquely define a record, however the fields part_uid and special_part_type_id are mutually exclusive with each other; only one will ever have a value and the other will be NULL. Herein lies the issue, since a primary key cannot contain a NULLable field, and neither can a replica identity. Hence I was using the uid which is an assembly of these four fields within a generator expression as the primary key, since this will never be NULL and will always be universally unique. This works fine for replicating INSERTs but it fails for DELETE and UPDATE statements and faults the replication due to the error:
publisher did not send replica identity column expected by the logical replication target relation
This table itself logs parts produced (part_uid) against job runs (job_run_uid). Parts are attached to Jobs, but there are also special parts that are standard which are not attached to jobs (hence the two fields part_uid and special_part_type_id).
I can't work out how I can replicate the destructive statements (DELETE/UPDATE) with this table. Please help
