Error seen when vacuuming pg_largeobject table

[Chris White (cjwhite)]

Following a power fail restart, when I vacuum the pg_largeobject table I
see the following error messages:

VACUUM: DEBUG sql sql sql DEBUG: --Relation pg_largeobject-- blocks 450

VACUUM: DEBUG sql sql sql DEBUG: Pages 450: Changed 78, reaped 277,
Empty 0, New 37; Tup 1390: Vac 232, Keep/VTL 0/0, UnUsed 220, MinLen 98,
MaxLen 2092; Re-using: Free/Avail. Space 1464136/1463348;
EndEmpty/Avail. Pages 0/445.

VACUUM: CPU 0.05s/0.01u sec elapsed 0.56 sec.

VACUUM: DEBUG sql sql sql DEBUG: Index pg_largeobject_loid_pn_index:
Pages 14; Tuples 1044: Deleted 232.

VACUUM: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM: INFO sql sql sql
NOTICE: Index pg_largeobject_loid_pn_index: NUMBER OF INDEX' TUPLES
(1044) IS NOT THE SAME AS HEAP' (1390).

VACUUM: Recreate the index.

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.

What can I do to correct the problem?

Thanks

Chris White

[Tom Lane]

Post by Chris White (cjwhite)
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Chris White (cjwhite)]

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:***@sss.pgh.pa.us]
Sent: Monday, January 26, 2004 5:24 PM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Chris White (cjwhite)
Here is the info. I am running 7.2.1.

7.2.1 is a bit long in the tooth; you really ought to be running 7.2.4
if you are still in the 7.2 series. However I don't think that has much
to do with your immediate problem.

The only thing that struck me about your transcript is that you didn't
show shutting down and restarting the postmaster. You didn't try to run
a standalone backend concurrently with the postmaster did you? (There
are supposed to be interlocks against that, but maybe they failed.)
The failures sort of look like the standalone backend's changes did not
completely propagate back to the regular database, and that's the only
very plausible mechanism I can think of for that ...

Also, I really wanted to see the output of vacuum verbose. Just
"vacuum" doesn't tell anything much.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Chris White (cjwhite)]

Cut and pasted from my screen to the e-mail. I did shutdown postmaster
prior to running postgres standalone. I assume that quit from postgres
backend stops it. I did restart postmaster prior running the psql vacuum
full.

Sorry, forgot to run verbose mode on the commands. I have re-initialized
the DB and restored from a backup. Will send info if it happens again.

Thanks for the help

Chris

-----Original Message-----
From: Tom Lane [mailto:***@sss.pgh.pa.us]
Sent: Tuesday, January 27, 2004 1:16 PM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

Post by Chris White (cjwhite)
Here is the info. I am running 7.2.1.

7.2.1 is a bit long in the tooth; you really ought to be running 7.2.4
if you are still in the 7.2 series. However I don't think that has much
to do with your immediate problem.

The only thing that struck me about your transcript is that you didn't
show shutting down and restarting the postmaster. You didn't try to run
a standalone backend concurrently with the postmaster did you? (There
are supposed to be interlocks against that, but maybe they failed.) The
failures sort of look like the standalone backend's changes did not
completely propagate back to the regular database, and that's the only
very plausible mechanism I can think of for that ...

Also, I really wanted to see the output of vacuum verbose. Just
"vacuum" doesn't tell anything much.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Chris White (cjwhite)]

Tom,

Got it to happen again after a power fail. Here is the logs this time
with verbose.

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 index pg_largeobject_loid_pn_index;
backend>
bash-2.05b$ ./postmaster -D /t/data/sql -i &
[1] 324
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 verbose;
NOTICE: --Relation pg_type-- blocks 3
NOTICE: Pages 3: Changed 1, reaped 1, Empty 0, New 0; Tup 154: Vac 0,
Keep/VTL 0/0, UnUsed 17, MinLen 106, MaxLen 106; Re-using: Free/Avail.
Space 7200/7200; EndEmpty/Avail. Pages 0/3.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_type_oid_index: Pages 2; Tuples 154: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_type_typname_index: Pages 2; Tuples 154: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.48 sec.
NOTICE: Rel pg_type: Pages: 3 --> 3; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_attribute-- blocks 16
NOTICE: Pages 16: Changed 5, reaped 4, Empty 0, New 0; Tup 947: Vac 97,
Keep/VTL 0/0, UnUsed 156, MinLen 98, MaxLen 98; Re-using: Free/Avail.
Space 31252/6760; EndEmpty/Avail. Pages 3/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_attribute_relid_attnam_index: Pages 16; Tuples 947:
Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
NOTICE: Index pg_attribute_relid_attnum_index: Pages 7; Tuples 947:
Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_attribute: Pages: 16 --> 13; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_class-- blocks 6
NOTICE: Pages 6: Changed 3, reaped 5, Empty 0, New 0; Tup 122: Vac 19,
Keep/VTL 0/0, UnUsed 145, MinLen 116, MaxLen 152; Re-using: Free/Avail.
Space 33476/1292; EndEmpty/Avail. Pages 4/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_class_oid_index: Pages 2; Tuples 122: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_class_relname_index: Pages 4; Tuples 122: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_class: Pages: 6 --> 2; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_group-- blocks 0
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_group_name_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_group_sysid_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_database-- blocks 1
NOTICE: Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 3: Vac 0,
Keep/VTL 0/0, UnUsed 2, MinLen 92, MaxLen 92; Re-using: Free/Avail.
Space 7876/7876; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_database_datname_index: Pages 2; Tuples 3: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_database_oid_index: Pages 2; Tuples 3: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_database: Pages: 1 --> 1; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_inherits-- blocks 0
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_inherits_relid_seqno_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
NOTICE: --Relation pg_index-- blocks 2
NOTICE: Pages 2: Changed 1, reaped 1, Empty 0, New 0; Tup 57: Vac 0,
Keep/VTL 0/0, UnUsed 16, MinLen 160, MaxLen 160; Re-using: Free/Avail.
Space 6932/6796; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_index_indrelid_index: Pages 2; Tuples 57: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_index_indexrelid_index: Pages 2; Tuples 57: Deleted 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_index: Pages: 2 --> 2; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_operator-- blocks 10
NOTICE: Pages 10: Changed 0, reaped 0, Empty 0, New 0; Tup 623: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 116, MaxLen 116; Re-using: Free/Avail.
Space 6960/6852; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_operator_oid_index: Pages 4; Tuples 623.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_operator_oprname_l_r_k_index: Pages 8; Tuples 623.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_operator: Pages: 10 --> 10; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_opclass-- blocks 1
NOTICE: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 51: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 80, MaxLen 80; Re-using: Free/Avail.
Space 3888/3888; EndEmpty/Avail. Pages 0/1.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_opclass_am_name_index: Pages 2; Tuples 51.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_opclass_oid_index: Pages 2; Tuples 51.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_opclass: Pages: 1 --> 1; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_am-- blocks 1
NOTICE: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 4: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 120, MaxLen 120; Re-using: Free/Avail.
Space 7676/7676; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_am_name_index: Pages 2; Tuples 4.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_am_oid_index: Pages 2; Tuples 4.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_am: Pages: 1 --> 1; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_amop-- blocks 2
NOTICE: Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 180: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 44; Re-using: Free/Avail.
Space 7704/7692; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_amop_opc_opr_index: Pages 2; Tuples 180.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_amop_opc_strategy_index: Pages 2; Tuples 180.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_amop: Pages: 2 --> 2; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_amproc-- blocks 1
NOTICE: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 57: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 44; Re-using: Free/Avail.
Space 5436/5436; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_amproc_opc_procnum_index: Pages 2; Tuples 57.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_amproc: Pages: 1 --> 1; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_language-- blocks 1
NOTICE: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 3: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 79, MaxLen 84; Re-using: Free/Avail.
Space 7912/7912; EndEmpty/Avail. Pages 0/1.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_language_name_index: Pages 2; Tuples 3.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_language_oid_index: Pages 2; Tuples 3.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Rel pg_language: Pages: 1 --> 1; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: --Relation pg_largeobject-- blocks 637
NOTICE: Pages 637: Changed 0, reaped 396, Empty 0, New 8; Tup 2380: Vac
517, Keep/VTL 0/0, UnUsed 22, MinLen 70, MaxLen 2092; Re-using:
Free/Avail. Space 1946640/1946008; EndEmpty/Avail. Pages 0/621.
CPU 0.05s/0.01u sec elapsed 0.05 sec.
NOTICE: Index pg_largeobject_loid_pn_index: Pages 1; Tuples 0: Deleted
0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_largeobject_loid_pn_index: NUMBER OF INDEX' TUPLES (0)
IS NOT THE SAME AS HEAP' (2380).
Recreate the index.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

aesop=#
aesop=# vacuum verbose pg_largeobject;
NOTICE: Index pg_largeobject_loid_pn_index: Pages 1; Tuples 0: Deleted
0.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
NOTICE: Removed 517 tuples in 376 pages.
CPU 0.05s/0.12u sec elapsed 1.62 sec.
NOTICE: Pages 637: Changed 0, Empty 1; Tup 2380: Vac 517, Keep 0,
UnUsed 22.
Total CPU 0.11s/0.12u sec elapsed 1.68 sec.
VACUUM
aesop=# vacuum full verbose pg_largeobject;
NOTICE: Pages 637: Changed 0, reaped 396, Empty 7, New 1; Tup 2380: Vac
0, Keep/VTL 0/0, UnUsed 539, MinLen 70, MaxLen 2092; Re-using:
Free/Avail. Space 1946640/1946008; EndEmpty/Avail. Pages 0/621.
CPU 0.05s/0.00u sec elapsed 0.05 sec.
NOTICE: Index pg_largeobject_loid_pn_index: Pages 1; Tuples 0: Deleted
0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_largeobject_loid_pn_index: NUMBER OF INDEX' TUPLES (0)
IS NOT THE SAME AS HEAP' (2380).
Recreate the index.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed. !# \q
bash-2.05b$ pg_ctl stop -D /t/data/sql -m fast waiting for postmaster to
shut down......done postmaster successfully shut down
[1]+ Done ./postmaster -D /t/data/sql -i
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 index pg_largeobject_loid_pn_index;
bash-2.05b$ ./postmaster -D /t/data/sql -i &
[1] 359
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 verbose pg_largeobject;
NOTICE: --Relation pg_largeobject-- blocks 637
NOTICE: Pages 637: Changed 0, reaped 396, Empty 7, New 1; Tup 2380: Vac
0, Keep/VTL 0/0, UnUsed 539, MinLen 70, MaxLen 2092; Re-using:
Free/Avail. Space 1946640/1946008; EndEmpty/Avail. Pages 0/621.
CPU 0.05s/0.00u sec elapsed 0.05 sec.
NOTICE: Index pg_largeobject_loid_pn_index: Pages 1; Tuples 0: Deleted
0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_largeobject_loid_pn_index: NUMBER OF INDEX' TUPLES (0)
IS NOT THE SAME AS HEAP' (2380).
Recreate the index.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed. !# \q
bash-2.05b$

-----Original Message-----
From: Tom Lane [mailto:***@sss.pgh.pa.us]
Sent: Tuesday, January 27, 2004 1:16 PM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

Post by Chris White (cjwhite)
Here is the info. I am running 7.2.1.

7.2.1 is a bit long in the tooth; you really ought to be running 7.2.4
if you are still in the 7.2 series. However I don't think that has much
to do with your immediate problem.

The only thing that struck me about your transcript is that you didn't
show shutting down and restarting the postmaster. You didn't try to run
a standalone backend concurrently with the postmaster did you? (There
are supposed to be interlocks against that, but maybe they failed.) The
failures sort of look like the standalone backend's changes did not
completely propagate back to the regular database, and that's the only
very plausible mechanism I can think of for that ...

Also, I really wanted to see the output of vacuum verbose. Just
"vacuum" doesn't tell anything much.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Chris White (cjwhite)
Got it to happen again after a power fail. Here is the logs this time
with verbose.

Hm, those backend crashes should have left core dumps; can you get a
stack trace from the core?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Chris White (cjwhite)]

Sorry no core files. The system is running with cores turned off. Next
time I will turn on cores prior to trying to debug this.

Chris

-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, January 28, 2004 11:52 AM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

Post by Chris White (cjwhite)
Got it to happen again after a power fail. Here is the logs this time
with verbose.

Hm, those backend crashes should have left core dumps; can you get a
stack trace from the core?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Gaetano Mendola]

Post by Chris White (cjwhite)
Sorry no core files. The system is running with cores turned off. Next
time I will turn on cores prior to trying to debug this.

This is not the first time that a usefull core go wasted.
Happen to me twice till I realized to modify the script

/etc/init.d/postgres

in order to have te core file.
May be is a good idea ship this file with the core file "on" ?


Regards
Gaetano Mendola














---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Chris White (cjwhite)]

Tom,

It happened again. After doing the reindex, I did a vacuum full on the
pg_largeobject table. This time I did not get the TUPLE INDEX error but
the server terminated at the end of the vacuum command. The backend is
still running even though I get the error message:

aesop=# vacuum full verbose pg_largeobject;
NOTICE: --Relation pg_largeobject-- blocks 21
NOTICE: Pages 21: Changed 0, reaped 19, Empty 0, New 0; Tup 69: Vac
749, Keep/VTL 0/0, UnUsed 181, MinLen 16, MaxLen 2092; Re-using:
Free/Avail. Space 146752/146752; EndEmpty/Avail. Pages 0/21.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2; Tuples 69: Deleted
0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

It consistently happens when I try to vacuum full the pg_largeobject
table. Is there anything I can get from the system to determine what is
happening?
Chris

-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, January 28, 2004 11:52 AM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

Post by Chris White (cjwhite)
Got it to happen again after a power fail. Here is the logs this time
with verbose.

Hm, those backend crashes should have left core dumps; can you get a
stack trace from the core?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Chris White (cjwhite)
It happened again. After doing the reindex, I did a vacuum full on the
pg_largeobject table. This time I did not get the TUPLE INDEX error but
the server terminated at the end of the vacuum command.

Can you enable core dumps and get a stack trace?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Chris White (cjwhite)]

Sorry I meant to say the server terminated the connection as indicated
by the error message but the server is still running and there is no
core (This time I enabled them).

Chris

-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 29, 2004 7:10 AM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

Post by Chris White (cjwhite)
It happened again. After doing the reindex, I did a vacuum full on the
pg_largeobject table. This time I did not get the TUPLE INDEX error
but the server terminated at the end of the vacuum command.

Can you enable core dumps and get a stack trace?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Chris White (cjwhite)
Sorry I meant to say the server terminated the connection as indicated
by the error message but the server is still running and there is no
core (This time I enabled them).

Hard to believe there's no core. What shows up in the postmaster log
when this happens?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Chris White (cjwhite)]

Nothing is generated to the screen and the log file is redirected to
stderr.


-----Original Message-----
From: Tom Lane [mailto:***@sss.pgh.pa.us]
Sent: Thursday, January 29, 2004 9:16 AM
To: ***@cisco.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Error seen when vacuuming pg_largeobject table

Post by Chris White (cjwhite)
Sorry I meant to say the server terminated the connection as indicated
by the error message but the server is still running and there is no
core (This time I enabled them).

Hard to believe there's no core. What shows up in the postmaster log
when this happens?

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend