et83DVGA

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