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 TABLEtest=# CREATE TABLE t_watermark (id int);
CREATE TABLEtest=# 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 ...
