Why the tax collector hates sequences

09.2012 / Category: / Tags: |

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;

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');
(1 row)

Then a second guy ...

test=# SELECT nextval('seq_invoices');
(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;
test=# SELECT nextval('seq_invoices');
(1 row)

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

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

test=# SELECT nextval('seq_invoices');
(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

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.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram