Dave's PostgreSQL Stuff

 It is much easier to keep bad data out of your database than it is to have to correct it later. Those costs are measured in time, money, and sanity. One of the easier ways of protecting your data is with a constraint check .  I was recently approached by someone who urgently needed a constraint check and could afford to 'fix' the bad data after the constraint was in place. They had to quickly keep new 'bad' data out and could afford to correct the old 'bad' data at leisure.  The Overly Simplified Example To illustrate how to do just that, here is an example of a table and some simple data. demo=# create table x1 (a int); CREATE TABLE demo=# insert into x1 (a) values (10),(20),(30),(-5),(1); INSERT 0 5 demo=# Sometime later, it is discovered that any value of a needs to be greater than five. If we add the constraint, the system will balk and refuse the request. demo=# alter table x1 add constraint x_id_gt_5 check (a > 5); ERROR:  check constraint "x_id_...

In the  last  blog, you saw how PostgreSQL keeps old versions of rows around within a transaction just in case there is a need to unwind or roll back the transaction. So, what happens to those rows that have been replaced after the transaction is complete? The answer is nothing. They sit there, taking up disk space.  How Do You Get That Disk Space Back? PostgreSQL's design keeps the old rows around. In the early days of the PostgreSQL project, the idea was that those old rows could be rolled off to a WORM drive (write-once read many), or you could 'time travel' through the data. Those were grand goals, but sadly, practicality and the cost of disk space set them aside. Our first example shows the creation of a table and the addition of one row of data. demo=# create table v (a int); CREATE TABLE demo=# insert into v (a) values (1); INSERT 0 1 demo=#  The first row of data is stored at CTID(0,1) demo=# select a, ctid from v;  a | ctid   ---+------- ...