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 ...
Table of Contents
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):
|
1 2 |
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:
|
1 2 3 4 5 |
test=# SELECT nextval('seq_invoices'); nextval --------- 1 (1 row) |
Then a second guy ...
|
1 2 3 4 5 |
test=# SELECT nextval('seq_invoices'); nextval --------- 2 (1 row) |
Everytime you sell something the counter is incremented.
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:
|
1 2 3 4 5 6 7 |
test=# BEGIN; BEGIN test=# SELECT nextval('seq_invoices'); nextval --------- 3 (1 row) |
|
1 2 3 |
test=# -- out of cash test=# ROLLBACK; ROLLBACK |
So far so good ... your customer has left and you can go on selling to somebody
else.
|
1 2 3 4 5 |
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.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply