Using a function based GIST index with a pgbench workload

NOTE:  wal_level = logical

PUBLISHER:

1. setup a small pgbench with:

pgbench -i -s 10 postgres

2. set up table with an index based function to invoke the problem with origin and publication for this table and the modified pgbench_history table with a serial based primary key.

CREATE TABLE t1 (a text primary key);
CREATE or REPLACE FUNCTION cidr_from_text (a text)
  RETURNS cidr as $$
BEGIN
   RETURN CAST($1 as cidr);
   EXCEPTION
     WHEN INVALID_TEXT_REPRESENTATION THEN
         RETURN NULL;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;
CREATE INDEX my_functional_index ON t1 using GIST (public.cidr_from_text(a) inet_ops) WHERE public.cidr_from_text(a) IS NOT NULL;
ALTER TABLE pgbench_history ADD COLUMN key SERIAL;
ALTER TABLE pgbench_history ADD PRIMARY KEY (key);
CREATE PUBLICATION my_publication FOR TABLE t1, pgbench_history;
CREATE USER repl_user WITH REPLICATION PASSWORD '<your_password>';
GRANT CONNECT ON DATABASE postgres TO repl_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;

SUBSCRIBER:

1. create tables on subscriber and setup subscription

CREATE TABLE t1 (a text primary key);
CREATE or REPLACE FUNCTION cidr_from_text (a text)
RETURNS cidr as $$
  BEGIN
      RETURN CAST($1 as cidr);
  EXCEPTION
      WHEN INVALID_TEXT_REPRESENTATION THEN
          RETURN NULL;
  END;
  $$ LANGUAGE PLPGSQL IMMUTABLE;
CREATE INDEX my_functional_index ON t1 using GIST (public.cidr_from_text(a) inet_ops) WHERE public.cidr_from_text(a) IS NOT NULL;

CREATE TABLE public.pgbench_history (
    tid integer,
    bid integer,
    aid integer,
    delta integer,
    mtime timestamp without time zone,
    filler character(22)
);

ALTER TABLE pgbench_history ADD COLUMN key SERIAL;
ALTER TABLE pgbench_history ADD PRIMARY KEY (key);

CREATE SUBSCRIPTION my_subscription CONNECTION '<conninfo>' PUBLICATION my_publication;


This will need multiple sessions.



Session 1 on publisher:

1. start the pgbench workload

pgbench -c 5 -T 180 -n -s 5 postgres



Session 2 on publisher:

1. A good value and a bad value that will enter the exception clause

insert into t1 values ('192.168/24');
insert into t1 values ('bad_value_1');


Session 3 on subscriber:


1. Check origin status, it will have stopped advancing

select * from pg_replication_origin_status;
select * from pg_replication_origin_status;

2. Disable subscription

alter subscription my_subscription disable;

3. Enable subscription

alter subscription my_subscription enable;

4. View the postgresql.log and observe duplicate key violations and replication is now broken

2025-04-09 12:38:18.734 MDT [39789] LOG:  logical replication worker for subscription "my_subscription" will stop because the subscription was disabled
2025-04-09 12:38:22.511 MDT [39859] LOG:  logical replication apply worker for subscription "my_subscription" has started
2025-04-09 12:38:23.764 MDT [39859] ERROR:  duplicate key value violates unique constraint "pgbench_history_pkey"
2025-04-09 12:38:23.764 MDT [39859] DETAIL:  Key (key)=(276668) already exists.
2025-04-09 12:38:23.764 MDT [39859] CONTEXT:  processing remote data for replication origin "pg_16527" during message type "INSERT" for replication target relation "public.pgbench_history" in transaction 4631967, finished at 1/8F25C2E0
2025-04-09 12:38:23.767 MDT [17454] LOG:  background worker "logical replication worker" (PID 39859) exited with exit code 1