Thread

View oddness...

Michael Richards <miker@scifair.acadiau.ca> — 1999-08-11T02:53:24Z

Hi.
I've noticed one of my views decided to grow a mind of it's own...

fastmail=> \d validusers
View    = validusers
Query   = SELECT "loginid", "datecreated", "lastused" FROM "users" WHERE
"enabled" = 't'::"bool";
+----------------------------------+----------------------------------+------+
|              Field               |              Type                |Length|
+----------------------------------+----------------------------------+------+
| loginid                          | varchar()                        |16|
| datecreated                      | datetime                         | 8|
| lastused                         | timestamp                        | 4|
+----------------------------------+----------------------------------+--+
fastmail=> select count(*) from users;
count
-----
32620
(1 row)

fastmail=> select count(*) from validusers;
count
-----
41670
(1 row)

validusers claims to have more rows than what it came from...

This all comes from the server being power cycled which corrupted the
users table. We dropped and re-created the users table because it refused
to vacuum. 

fastmail=> vacuum users;
NOTICE:  Index users_pkey: NUMBER OF INDEX' TUPLES (3212) IS NOT THE SAME
AS HEAP' (4246)
ERROR:  Cannot insert a duplicate key into a unique index

The part I can't figure out is why a view (which is supposed to come from
a select) was affected...

Having given this some thought, would it be possible to add a switch to
vacuum, ie vacuum check that would check the integrity of a table and
repair it if there are problems?

-Michael