X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 914CCD1E0FD for ; Tue, 27 Jan 2004 02:00:03 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 30481-04 for ; Mon, 26 Jan 2004 21:59:34 -0400 (AST) Received: from sj-iport-5.cisco.com (sj-iport-5.cisco.com [171.68.10.87]) by svr1.postgresql.org (Postfix) with ESMTP id 62876D1E1D3 for ; Mon, 26 Jan 2004 21:54:40 -0400 (AST) Received: from sj-core-4.cisco.com (171.68.223.138) by sj-iport-5.cisco.com with ESMTP; 26 Jan 2004 17:56:56 -0800 Received: from cjwhitew2k02 (dhcp-128-107-151-36.cisco.com [128.107.151.36]) by sj-core-4.cisco.com (8.12.6/8.12.6) with ESMTP id i0R1sc5k018480; Mon, 26 Jan 2004 17:54:39 -0800 (PST) Reply-To: From: "Chris White (cjwhite)" To: "'Tom Lane'" Cc: Subject: Re: Error seen when vacuuming pg_largeobject table Date: Mon, 26 Jan 2004 17:54:38 -0800 Message-ID: <003501c3e478$87e4c0b0$24976b80@amer.cisco.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4024 In-Reply-To: <7741.1075166619@sss.pgh.pa.us> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/248 X-Sequence-Number: 12111 Here is the info. I am running 7.2.1. bash-2.05b$ ./postgres -P -O -D /t/data/sql aesop POSTGRES backend interactive interface $Revision: 1.245.2.2 $ $Date: 2002/02/27 23:17:01 $ backend> reindex index pg_largeobject_loid_pn_index; backend> bash-2.05b$ ./psql -d aesop -U chris Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit aesop=# vacuum full; NOTICE: Index pg_largeobject_loid_pn_index: NUMBER OF INDEX' TUPLES (0) IS NOT THE SAME AS HEAP' (1390). Recreate the index. ERROR: Cannot insert a duplicate key into unique index pg_largeobject_loid_pn_index aesop=# \q It is slightly different in that the number of tuples in the index is (0) compared with (1044) before. Tried again, this time I tried to reindex the pg_largeobject table first. This caused even problems: bash-2.05b$ ./postgres -D /t/data/sql -P -O aesop POSTGRES backend interactive interface $Revision: 1.245.2.2 $ $Date: 2002/02/27 23:17:01 $ backend> reindex table pg_largeobject; backend> reindex index pg_largeobject_loid_pn_index; backend> bash-2.05b$ ./psql -d aesop -U voicemail Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit aesop=# vacuum full; NOTICE: RelationBuildDesc: can't open pg_largeobject_loid_pn_index: No such file or directory ERROR: _mdfd_getrelnfd: cannot open relation pg_largeobject_loid_pn_index: No such file or directory aesop=# \q -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, January 26, 2004 5:24 PM To: cjwhite@cisco.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table "Chris White (cjwhite)" writes: > VACUUM: DEBUG sql sql sql DEBUG: Index pg_largeobject_loid_pn_index: > Pages 14; Tuples 1044: Deleted 232. ... > VACUUM: WARNING sql sql sql ERROR: Cannot insert a duplicate key into > unique index pg_largeobject_loid_pn_index > When I run postgres with the -P and -O options to reindex the index, > everything seem to run okay, I see no error messages. However, when I > restart postmaster and try and do a vacuum again, I get the same error > messages. I don't think you can have reindexed the index. With only 1044 rows in it, it shouldn't take up more than 3 or 4 pages when freshly created; certainly not 14. Are you sure you reindexed in the right database? If you're sure it's not pilot error, let's see the exact transcript of what you did with the standalone postgres, and also the exact transcript (no "sql sql sql") of the failing VACUUM afterwards. Also, what PG version is this? regards, tom lane