Q7s5wXBR

1DROP TABLE IF EXISTS C;
2DROP TABLE IF EXISTS B;
3
4CREATE TABLE B (
5BID bigint NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
6AID bigint NOT NULL REFERENCES A(AID)
7);
8
9CREATE TABLE C (
10AID bigint NOT NULL REFERENCES A(AID),
11COUNT bigint NOT NULL,
12PRIMARY KEY (AID)
13);
14
15INSERT INTO A (AID) SELECT generate_series(1,1000000);
16
17INSERT INTO B (AID) SELECT generate_series(1,1000000);
18INSERT INTO B (AID) SELECT generate_series(1,500000);
19INSERT INTO B (AID) SELECT generate_series(1,300000);
20
21CREATE OR REPLACE FUNCTION F3(AID bigint)
22RETURNS boolean
23LANGUAGE plpgsql
24AS $X$
25BEGIN
26RETURN (SELECT NOT EXISTS (SELECT 1 FROM C WHERE C.AID = F3.AID));
27END;
28$X$;
29
30CREATE OR REPLACE FUNCTION F1()
31RETURNS void
32LANGUAGE sql
33AS $X$
34INSERT INTO C (AID, COUNT)
35SELECT B.AID, COUNT(*) FROM (
36  SELECT AID FROM A
37  WHERE EXISTS (SELECT 1 FROM B WHERE B.AID = A.AID)
38  AND NOT EXISTS (SELECT 1 FROM C WHERE C.AID = A.AID)
39  LIMIT 100000
40  FOR UPDATE OF A
41  SKIP LOCKED
42) AS Q
43JOIN B ON B.AID = Q.AID
44WHERE F3(Q.AID)
45GROUP BY B.AID;
46$X$;
47
48CREATE OR REPLACE PROCEDURE F2()
49LANGUAGE plpgsql
50AS $X$
51DECLARE
52_C bigint;
53_A bigint;
54BEGIN
55SELECT COUNT(*) INTO _A FROM A;
56LOOP
57  PERFORM F1();
58  COMMIT;
59  SELECT COUNT(*) INTO _C FROM C;
60  IF _C = _A THEN
61    RAISE NOTICE '%', CURRENT_TIMESTAMP;
62    RETURN;
63  ELSE
64    RAISE NOTICE 'C=% A=%', _C, _A;
65  END IF;
66END LOOP;
67END;
68$X$;
69
70/*
71
72In separate processed, CALL F2() multiple times:
73
74test=# -- Process 1:
75test=# CALL F2();
76
77test=# -- Process 2:
78test=# CALL F2();
79ERROR:  duplicate key value violates unique constraint "c_pkey"
80DETAIL:  Key (aid)=(1) already exists.
81CONTEXT:  SQL function "f1" statement 1
82SQL statement "SELECT F1()"
83PL/pgSQL function f2() line 4 at PERFORM
84
85*/
86
87
88
89
90