6FaBKcPi

1I have been trying for a while to debug some serialization errors I have been encountering.  I can reproduce the error using a client
2application. That application spawns multiple connections at once. However I can't reproduce the error myself by trying to emulate the client application in a single transaction.
3
4I basically have 2 processes.  
5
6Process 1: That serially inserts and updates a row.  Once Process 1 is finished with a row it triggers Process 2.
7Process 2: Spawn multiple subprocess and concurrently updates rows created by Process 1, but in a different column after Process 1 is finished with a given row.  Process 1 doesn't wait for Process 2 to finish, it just continues.  Basically Process 2 is like a background process to do post-processing for Process 1 that is spawned for each row that is inserted by Process 1.
8
9
10Process 2 though is causing serialization errors for subsequent rows in Process 1 even though they never deal with the same row.  In other words Process 1 is impacted by Process 2 even though they never touch the same row.
11
12Process 2 can have serialization errors and they are expected since it can spawn multiple concurrent hits to a row.  What I don't understand is why
13Process 1 is encountering serialization errors and retries don't help.
14
15I have tried setting sequential scan to off as well as increasing the number of allowed transaction locks to no avail.
16
17The following are simplified versions of the commands:
18
19My application grabs the index of the inserted rows so the values below would change at runtime based on the actual index of a inserted row.
20
21Setup:
22
23"""CREATE TABLE "test_table" (
24	index uuid DEFAULT uuid_generate_v4 (),
25	jsb_col JSONB,
26	tsv1 tsvector DEFAULT '',
27	PRIMARY KEY (index)
28);
29CREATE INDEX tsv1_index ON "test_table" USING gin (tsv1);
30CREATE UNIQUE INDEX IF NOT EXISTS uid_index ON "test_table" ((jsb_col#>'{uid}'));"""
31
32Process 1 First Row:
33"""begin;
34INSERT INTO "test_table" (jsb_col) VALUES ('{"uid": 1, "data": {}}') ON CONFLICT DO NOTHING returning index;
35SAVEPOINT savepoint1;
36UPDATE "test_table" SET jsb_col = jsonb_set(jsb_col, '{data}', jsb_col#>'{data}'||'{"newdata":"helloworld1"}', TRUE) WHERE index = 'af22acde-da89-49cc-b7fc-5aefa600c409' returning index;
37SAVEPOINT savepoint2;
38UPDATE "test_table" SET jsb_col = jsonb_set(jsb_col, '{data}', jsb_col#>'{data}'||'{"newdata1a":"helloworld1a"}', TRUE) WHERE index = 'af22acde-da89-49cc-b7fc-5aefa600c409' returning index;  
39SAVEPOINT savepoint3;
40commit;"""
41
42Process 2 First Row:
43Concurrent Subprocess 1 of Process 2
44"""begin;
45UPDATE "test_table" SET tsv1 = tsv1 || to_tsvector('english', 'lorem ipsum') WHERE index = 'af22acde-da89-49cc-b7fc-5aefa600c409' returning index;
46commit;"""
47
48Concurrent Subprocess 2 of Process 2
49"""begin;
50UPDATE "test_table" SET tsv1 = tsv1 || to_tsvector('english', 'dolor sit amet') WHERE index = 'af22acde-da89-49cc-b7fc-5aefa600c409' returning index;
51commit;"""
52
53Process 1 Second Row:
54"""begin;
55INSERT INTO "test_table" (jsb_col) VALUES ('{"uid": 2, "data": {}}') ON CONFLICT DO NOTHING returning index;
56SAVEPOINT savepoint1;
57UPDATE "test_table" SET jsb_col = jsonb_set(jsb_col, '{data}', jsb_col#>'{data}'||'{"newdata":"helloworld2"}', TRUE) WHERE index = 'f350d44d-7d48-4eee-aec8-02cc4d179af7' returning index;
58SAVEPOINT savepoint2;
59UPDATE "test_table" SET jsb_col = jsonb_set(jsb_col, '{data}', jsb_col#>'{data}'||'{"newdata2a":"helloworld2a"}', TRUE) WHERE index = 'f350d44d-7d48-4eee-aec8-02cc4d179af7' returning index;
60SAVEPOINT savepoint3;
61commit;"""
62
63Process 2 Second Row: etc..
64
65Basically Process 2 concurrently/interleaves with Process 1, but Process 2 is never triggered for a given row until Process 1 is done with it.
66
67When I remove Process 2, Process 1 always complete error free with no serialization errors.  It's only when I introduce Process 2 that it messes up Process 1.
68
69Any help is really appreciated!
70