Still investigating, but ... CREATE INDEX problem in v6.5.x ...

Working on a database that has a table that looks like:

Table = daily
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| n | int4 | 4 |
| date | date | 4 |
| cookie | char() | 1 |
| id | text | var |
+----------------------------------+----------------------------------+-------+
Indices: zdaily_cookie
zdaily_date_n

Want to create a third index on id, so run:

CREATE INDEX zdaily_id ON daily (id);

Eventually, the CREATE INDEX just crashes...

daily is big:

-rw------- 1 postgres postgres 979255296 Jan 6 02:04 daily

But, the other two indices are fine:

-rw------- 1 postgres postgres 241123328 Jan 6 02:26 zdaily_date_n
-rw------- 1 postgres postgres 229220352 Jan 6 02:13 zdaily_cookie

We're currently using v6.5.1, since it used to work there, but have tried
with with v6.5.3 also, same results...

I've thought about out of disk space problems, but the file system that
its on has over 2gig free on it, and the table is <1gig to start with...

I'm running it again right now, after running a vacuum on it, just in case
that picked up something, but the vacuum looks clean:

webusers=> vacuum verbose daily;
NOTICE: --Relation daily--
NOTICE: Pages 119538: Changed 0, Reapped 0, Empty 0, New 0; Tup 11358404: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 53, MaxLen 3959; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 9/2 sec.
NOTICE: Index zdaily_date_n: Pages 29434; Tuples 11358404. Elapsed 2/18 sec.
NOTICE: Index zdaily_cookie: Pages 27981; Tuples 11358404. Elapsed 3/18 sec.

The index is being created on disk, but doesn't grow beyond the 16k shown
here:

ls -lt *daily*
-rw------- 1 postgres postgres 16384 Jan 6 13:32 zdaily_id
-rw------- 1 postgres postgres 241123328 Jan 6 02:26 zdaily_date_n
-rw------- 1 postgres postgres 229220352 Jan 6 02:13 zdaily_cookie
-rw------- 1 postgres postgres 979255296 Jan 6 02:04 daily

We have no "verbose" mode for a create index, do we? Something that would
narrow down a record whose 'id' field has bad data in it?

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org