Re: logical decoding and replication of sequences, take 2

Hi,

On 2/16/23 10:50 AM, Tomas Vondra wrote:
> Hi,
> 
> Here's a rebased patch, without the last bit which is now unnecessary
> thanks to c981d9145dea.

Thanks for continuing to work on this patch! I tested the latest version 
and have some feedback/clarifications.

I did some testing using a demo-app-based-on-a-real-world app I had 
conjured up[1]. This uses integer sequences as surrogate keys.

In general things seemed to work, but I had a couple of 
observations/questions.

1. Sequence IDs after a "failover". I believe this is a design decision, 
but I noticed that after simulating a failover, the IDs were replicating 
from a higher value, e.g.

INSERT INTO room (name) VALUES ('room 1');
INSERT INTO room (name) VALUES ('room 2');
INSERT INTO room (name) VALUES ('room 3');
INSERT INTO room (name) VALUES ('room 4');

The values of room_id_seq on each instance:

instance 1:

  last_value | log_cnt | is_called
------------+---------+-----------
           4 |      29 | t

  instance 2:

   last_value | log_cnt | is_called
------------+---------+-----------
          33 |       0 | t

After the switchover on instance 2:

INSERT INTO room (name) VALUES ('room 5') RETURNING id;

  id
----
  34

I don't see this as an issue for most applications, but we should at 
least document the behavior somewhere.

2. Using with origin=none with nonconflicting sequences.

I modified the example in [1] to set up two schemas with non-conflicting 
sequences[2], e.g. on instance 1:

CREATE TABLE public.room (
     id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1) 
PRIMARY KEY,
     name text NOT NULL
);

and instance 2:

CREATE TABLE public.room (
     id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2) 
PRIMARY KEY,
     name text NOT NULL
);

I ran the following on instance 1:

INSERT INTO public.room ('name') VALUES ('room 1-e');

This committed and successfully replicated.

However, when I ran the following on instance 2, I received a conlifct 
error:

INSERT INTO public.room ('name') VALUES ('room 1-w');

The conflict came further down the trigger change, i.e. to a change in 
the `public.calendar` table:

2023-02-22 01:49:12.293 UTC [87235] ERROR:  duplicate key value violates 
unique constraint "calendar_pkey"
2023-02-22 01:49:12.293 UTC [87235] DETAIL:  Key (id)=(661) already exists.

After futzing with the logging and restarting, I was also able to 
reproduce a similar conflict with the same insert pattern into 'room'.

I did notice that the sequence values kept bouncing around between the 
servers. Without any activity, this is what "SELECT * FROM room_id_seq" 
would return with queries run ~4s apart:

  last_value | log_cnt | is_called
------------+---------+-----------
         131 |       0 | t

  last_value | log_cnt | is_called
------------+---------+-----------
          65 |       0 | t

The values were more varying on "calendar". Again, this is under no 
additional write activity, these numbers kept fluctuating:

  last_value | log_cnt | is_called
------------+---------+-----------
         197 |       0 | t

  last_value | log_cnt | is_called
------------+---------+-----------
         461 |       0 | t

  last_value | log_cnt | is_called
------------+---------+-----------
         263 |       0 | t

  last_value | log_cnt | is_called
------------+---------+-----------
         527 |       0 | t

To handle this case for now, I adapted the schema to create sequences 
that we clearly independently named[3]. I did learn that I had to create 
sequences on both instances to support this behavior, e.g.:

-- instance 1
CREATE SEQUENCE public.room_id_1_seq AS int INCREMENT BY 2 START WITH 1;
CREATE SEQUENCE public.room_id_2_seq AS int INCREMENT BY 2 START WITH 2;
CREATE TABLE public.room (
     id int DEFAULT nextval('room_id_1_seq') PRIMARY KEY,
     name text NOT NULL
);

-- instance 2
CREATE SEQUENCE public.room_id_1_seq AS int INCREMENT BY 2 START WITH 1;
CREATE SEQUENCE public.room_id_2_seq AS int INCREMENT BY 2 START WITH 2;
CREATE TABLE public.room (
     id int DEFAULT nextval('room_id_2_seq') PRIMARY KEY,
     name text NOT NULL
);

After building out [3] this did work, but it was more tedious.

Is it possible to support IDENTITY columns (or serial columns) where the 
values of the sequence are set to different intervals on the 
publisher/subscriber?

Thanks,

Jonathan

[1] 
https://github.com/CrunchyData/postgres-realtime-demo/blob/main/examples/demo/demo1.sql
[2] https://gist.github.com/jkatz/5c34bf1e401b3376dfe8e627fcd30af3
[3] https://gist.github.com/jkatz/1599e467d55abec88ab487d8ac9dc7c3