Re: Multicolumn index corruption on 8.4 beta 2

Floris Bos / Maxnet wrote:
> The following settings differ from the defaults:
> 
> -- 
> shared_buffers=3500MB
> maintenance_work_mem = 128MB
> fsync = off
> synchronous_commit = off
> checkpoint_segments = 25
> -- 
> 
> 
> ==
> Table layout
> ==
> 
> -- 
>                                    Table "public.posts_index"
>    Column   |          Type          | Modifiers
> ------------+------------------------+----------------------------------------------------------- 
> 
>  cid        | integer                | not null default 
> nextval('posts_index_cid
> _seq'::regclass)
>  groupid    | integer                | not null
>  startdate  | integer                | not null
>  poster     | character varying(64)  | not null
>  basefile   | character varying(64)  | not null
>  subject    | character varying(255) | not null
>  size       | real                   |
>  nfo        | boolean                |
>  c          | boolean                |
>  parts      | integer                |
>  totalparts | integer                |
>  imdb       | integer                |
>  ng1        | boolean                | default false
>  g2         | integer                | default 0
>  g3         | integer                | default 0
>  data       | bytea                  |
> Indexes:
>     "posts_index5_pkey" PRIMARY KEY, btree (cid) CLUSTER
>     "gr_idx" btree (groupid, (- cid))
>     "pgb_idx" btree (poster, groupid, basefile)
> -- 
> 
> Only noticed problems with the pgb_idx index so far.


I have been trying to reproduce the problem but no success so far.  I 
made myself a table that matches yours, then I wrote a little perl 
script to fill it with random data.  (The script also writes out a text 
file I can use to re-query things).

I fill the db, then add the indexes.  Then I test lookup every record I 
added, and find them all.

So, a few questions:

1) did you dump/restore into 8.4beta1 first and then upgrade the 
program?  Or did you dump/restore into 8.4beta2?

2) did you use any of the concurrent restore options?

3) do you do any updates or deletes to the table after you restore it?

4) do you do any other operations on the table (vacuum, cluster, etc..)?

5) got any triggers or stored procs?

6) To the -hackers: I write the records and then refind them in the 
exact same order, would it be a better test to search for records in a 
more random order?  would it make a difference?  Would searching for 
some but not all make a difference?


-Andy