From the Department of Redundancy Department:
Attached is a perl script called 'pguniqchk'. It checks the uniqueness
of unique constraints on tables in a PostgreSQL database using the
PG_TABLES and PG_INDEXES system "tables".
Why would this be useful?
If you're planning to dump and restore the database, this might be a
good sanity check to run before doing it.
Apparently, when such an impossible event occurs, the unique index on
the table only "sees" one of the duplicate rows. In order to even query
both rows, one must run this SQL command (via psql) to turn off index
scans:
=> set enable_indexscan = off;
The attached script does this, then verifies the uniqueness of the
unique index by scanning the entire table.
It is probably useless for 99.999% of PostgreSQL users, but I thought
I'd share it just in case someone finds it useful, even if only
as a simple example of querying system tables.
How I found the problem:
I had a need to alter the data types of a column on two different tables
(VARCHAR(32) -> VARCHAR(128) and VARCHAR(128) -> TEXT) and drop a column
from another table. The only way to do this in v7.1.x is to perform a
full dump and then restore. When I tried to reload the data, I got
unique key violation errors, and data for two other tables did not load.
As it turns out, one table had a single pair of duplicate keys while the
other table had five pair of duplicates and one set of triplicates.
The incident happened around April 05, 2002 (from what I can tell of
the duplicated data), but hasn't happened since. I was having SCSI
disk errors around that time on my production server, which is the prime
suspect.
NOTES:
- Only tested on PostgreSQL 7.1.3.
- When a UNIQUE INDEX is put on a NULLABLE column, duplicates with NULL
values are possible. This is a feature, though the script does not
check for this case (so don't be alarmed if it finds something).
7.4. Unique Indexes
http://www.postgresql.org/idocs/index.php?indexes-unique.html
Dave