Why the tax collector hates sequences

I guess people might wonder why I started to write posts about taxes. Well, I
pay my taxes for my business as a PostgreSQL professional - but,
recently I have seen a case where somebody else faced a false accusation of
not paying taxes the way they should be paid ...

The role of databases ...

How can a simple database bring you into serious trouble? Let us assume for a
second that you are running a billing application in a small shop. Once in a
while somebody comes in and you sell him some stuff - so far so good. Sometimes
it might happen that a person comes to your shop and wants to buy some stuff but
leaves for some reason. Maybe he has no cash in his pocket or he simply finds
out in the last moment that he simply does not want the stuff he has selected
just before.

If you sell stuff you are supposed to provide the customer with an invoice. The
law says that invoice numbers should be ascending and without interruptions.
Sounds simple, no? This is when your favorite PostgreSQL database comes into
play (or in this case an app developer which should not have gotten the job in
the first place):

test=# CREATE SEQUENCE seq_invoices;
CREATE SEQUENCE

The billing application has created a simple sequence to generate numbers for
those invoices. Now, the first guy walks into the shop. What happens is:

test=# SELECT nextval('seq_invoices');
 nextval
---------
       1
(1 row)

Then a second guy ...

test=# SELECT nextval('seq_invoices');
 nextval
---------
       2
(1 row)

Everytime you sell something the counter is incremented.

When it starts to turn against you

But let us imagine for a second now that you are just about to write invoice
number 3 when the customer finds out that he is out of cash:

test=# BEGIN;
BEGIN
test=# SELECT nextval('seq_invoices');
 nextval
---------
       3
(1 row)

test=# -- out of cash
test=# ROLLBACK;
ROLLBACK

So far so good ... your customer has left and you can go on selling to somebody
else.

test=# SELECT nextval('seq_invoices');
nextval
---------
       4
(1 row)

Opps? Where is number 3? It is simply not there and the reason is simple: You
cannot rollback a sequence. A sequence will only provide you with an ascending
number - it does NOT provide you with a sequence of numbers which has no gaps in
it.

As soon as the tax collector comes you will be in deep trouble explaining your
missing receipts so better make sure that you stay away from sequences when are
writing your receipts.