From: Chris White (cjwhite) <cjwhite@cisco.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
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> (raw)
In-Reply-To: <7741.1075166619@sss.pgh.pa.us>
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)" <cjwhite@cisco.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: cjwhite@cisco.com, tgl@sss.pgh.pa.us
Subject: Re: Error seen when vacuuming pg_largeobject table
In-Reply-To: <003501c3e478$87e4c0b0$24976b80@amer.cisco.com>
References: <7741.1075166619@sss.pgh.pa.us> <003501c3e478$87e4c0b0$24976b80@amer.cisco.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox