Considering alternative solutions

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. However, 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  
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.