qbEsWBjm

1CREATE TABLE sequence_numbers(
2    level integer,
3    code integer,
4    next_value integer DEFAULT 0 NOT NULL,
5    PRIMARY KEY (level,code),
6    CONSTRAINT level_must_be_one_digit CHECK (level BETWEEN 0 AND 9),
7    CONSTRAINT code_must_be_three_digits CHECK (code BETWEEN 0 AND 999),
8    CONSTRAINT value_must_be_four_digits CHECK (next_value BETWEEN 0 AND 9999)
9);
10
11INSERT INTO sequence_numbers(level,code) VALUES (2,777);
12
13CREATE OR REPLACE FUNCTION get_next_seqno(level integer, code integer)
14RETURNS integer LANGUAGE 'SQL' AS $$
15    UPDATE sequence_numbers 
16    SET next_value = next_value + 1
17    WHERE level = $1 AND code = $2
18    RETURNING (to_char(level,'FM9')||to_char(code,'FM000')||to_char(next_value,'FM0000'))::integer;
19$$;
20	
21INSERT INTO myTable (sequence_number, blah)
22VALUES (get_next_seqno(2,777), blah);
23	
24CREATE TABLE myTable(seq_no integer primary key);
25INSERT INTO sequence_numbers VALUES (1,666)
26	
27SESSION 1                       SESSION 2
28
29BEGIN;
30                                BEGIN;
31
32INSERT INTO myTable(seq_no)
33VALUES(get_next_seqno(2,777));
34                                INSERT INTO myTable(seq_no)
35                                VALUES(get_next_seqno(1,666));
36
37                                INSERT INTO myTable(seq_no)
38                                VALUES(get_next_seqno(2,777));
39
40INSERT INTO myTable(seq_no)
41VALUES(get_next_seqno(1,666));
42	
43ERROR:  deadlock detected
44DETAIL:  Process 16723 waits for ShareLock on transaction 40450; blocked by process 18632.
45Process 18632 waits for ShareLock on transaction 40449; blocked by process 16723.
46HINT:  See server log for query details.
47CONTEXT:  SQL function "get_next_seqno" statement 1
48	
49CREATE OR REPLACE FUNCTION get_next_seqno(level integer, code integer)
50RETURNS integer LANGUAGE 'SQL' AS $$
51
52    -- add a (level,code) pair if it isn't present.
53    -- Racey, can fail, so you have to be prepared to retry
54    INSERT INTO sequence_numbers (level,code)
55    SELECT $1, $2
56    WHERE NOT EXISTS (SELECT 1 FROM sequence_numbers WHERE level = $1 AND code = $2);
57
58    UPDATE sequence_numbers 
59    SET next_value = next_value + 1
60    WHERE level = $1 AND code = $2
61    RETURNING (to_char(level,'FM9')||to_char(code,'FM000')||to_char(next_value,'FM0000'))::integer;
62
63$$;
64	
65ERROR:  duplicate key value violates unique constraint "sequence_numbers_pkey"
66DETAIL:  Key (level, code)=(0, 555) already exists.
67CONTEXT:  SQL function "get_next_seqno" statement 1
68	
69SELECT ID 
70FROM myTable 
71WHERE ID > 27770000 
72  AND ID < 27780000 
73ORDER BY ID DESC 
74LIMIT 1
75	
76select coalesce(max(id), 27770000) + 1
77from myTable
78where id / 10000 = 2777