Considering alternative solutions

However, there is an alternative solution to the problem. Consider the following example: you are asked to write an application generating invoice numbers. The tax office might require you to create invoice numbers without gaps and without duplicates. How would you do it? Of course, one solution would be a table lock. But you can really do better. Here is what I would do:

test=# CREATE TABLE t_invoice (id int PRIMARY KEY); 
CREATE TABLE 
test=# CREATE TABLE t_watermark (id int); 
CREATE TABLE 
test=# INSERT INTO t_watermark VALUES (0); 
INSERT 0 1 
test=# WITH x AS (UPDATE t_watermark SET id = id + 1 RETURNING *) 
         INSERT INTO t_invoice  
         SELECT * FROM x RETURNING *; 
 id  
---- 
  1 
(1 row) 

In this case, I introduced a table called t_watermark. It contains just one row. The WITH will be executed first. The row will be locked and incremented, and the new value will be returned. Only one person can do this at a time. The value returned by the CTE is then used in the invoice table. It is guaranteed to be unique. The beauty is that there is only a simple row lock on the watermark table; no reads will be blocked in the invoice table. Overall, this way is more scalable.