Received: from mail_exchange.westcountrypublications.co.uk ([195.171.163.135]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBHFNtm69690 for ; Mon, 17 Dec 2001 10:23:56 -0500 (EST) (envelope-from SHenshall@westcountrypublications.co.uk) Received: by MAIL_EXCHANGE with Internet Mail Service (5.5.2650.21) id ; Mon, 17 Dec 2001 15:20:54 -0000 Message-ID: From: "Henshall, Stuart - WCP" To: "'Carolyn Lu Wong'" , pgsql-sql@postgresql.org Subject: Re: weird duplicate data problem Date: Mon, 17 Dec 2001 15:20:52 -0000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200112/179 X-Sequence-Number: 6002 -----Original Message----- From: Carolyn Lu Wong [mailto:carolyn@greatpacific.com.au] Sent: 14 December 2001 07:29 To: pgsql-sql@postgresql.org Subject: weird duplicate data problem a table was created with: create table bills ( id serial not null, account_no int4 not null, date_issued date not null, ..... ); The following SQL script returns only 1 row of data: select oid, * from bills where id = xxxxx' order by oid; -- Seems reasonable -- However with the following SQL: select oid, * from bills where date_issued = '01/01/2001' order by oid; some of the data is duplicated, including the OID. -- OIDs are not guaranteed unique and can wraparound (unlesss there's a unique index). However this seems a little unlikely in the same day so the only thing I can think of is that there is a corrupt index on date_issued. Try dropping and recreating any indexes.However I havn't really used 6.5 so couldn't be sure. -- When trying to 'vaccum' the database, get NOTICE: Index bills_id_key: number of index tupples (4755) is not the same as heap (7800). What is happening? What could have caused this? Database version used is V6.50. I know it's old, but i hope it's not the version that's causing this problem. Thanks in advance for any suggestions.