Thread: vacuums failing

Could someone please tell me what is going wrong?


query: VACUUM  ANALYZE
ProcessUtility: VACUUM  ANALYZE
DEBUG:  --Relation pg_type--
DEBUG:  Pages 2: Changed 0, reaped 1, Empty 0, New 0; Tup 134: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 1, MinLen 105,
MaxLen 109; Re-using: Free/Avail. Space 1220/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.01u sec.
DEBUG:  Index pg_type_typname_index: Pages 2; Tuples 134: Deleted 0. CPU
0.00s/0.01u sec.
DEBUG:  Index pg_type_oid_index: Pages 2; Tuples 134: Deleted 0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_attribute--
DEBUG:  Pages 9: Changed 0, reaped 1, Empty 0, New 0; Tup 682: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 10, MinLen 98,
MaxLen 100; Re-using: Free/Avail. Space 2112/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.01u sec.
DEBUG:  Index pg_attribute_relid_attnum_index: Pages 5; Tuples 682:
Deleted 0. CPU 0.00s/0.00u sec.
DEBUG:  Index pg_attribute_relid_attnam_index: Pages 10; Tuples 682:
Deleted 0. CPU 0.00s/0.00u sec.
DEBUG:  --Relation pg_proc--
DEBUG:  Pages 26: Changed 0, reaped 0, Empty 0, New 0; Tup 1083: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 177
, MaxLen 229; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.02u sec.
DEBUG:  Index pg_proc_proname_narg_type_index: Pages 24; Tuples 1083.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_proc_oid_index: Pages 6; Tuples 1083. CPU 0.00s/0.01u
sec.
DEBUG:  --Relation pg_class--
DEBUG:  Pages 2: Changed 0, reaped 1, Empty 0, New 0; Tup 92: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 38, MinLen 106,
MaxLen 136; Re-using: Free/Avail. Space 5612/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.00u sec.
DEBUG:  Index pg_class_relname_index: Pages 2; Tuples 92: Deleted 0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_class_oid_index: Pages 2; Tuples 92: Deleted 0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_indexes--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_group--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_group_sysid_index: Pages 2; Tuples 0. CPU 0.00s/0.00u
sec.
DEBUG:  Index pg_group_name_index: Pages 2; Tuples 0. CPU 0.00s/0.00u
sec.
DEBUG:  --Relation pg_database--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 3: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 79, Max
Len 85; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_attrdef--
DEBUG:  Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 29: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 149, M
axLen 682; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_attrdef_adrelid_index: Pages 2; Tuples 29. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_relcheck--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_relcheck_rcrelid_index: Pages 2; Tuples 0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_trigger--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 1: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 152, Ma
xLen 152; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_trigger_tgrelid_index: Pages 2; Tuples 1. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_trigger_tgconstrrelid_index: Pages 2; Tuples 1. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_trigger_tgconstrname_index: Pages 2; Tuples 1. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_inherits--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_inherits_relid_seqno_index: Pages 2; Tuples 0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_index--
DEBUG:  Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 47: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 1, MinLen 164, M
axLen 164; Re-using: Free/Avail. Space 284/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_index_indexrelid_index: Pages 2; Tuples 47: Deleted 0.
CPU 0.00s/0.00u sec.
DEBUG:  --Relation pg_statistic--
DEBUG:  Pages 6: Changed 4, reaped 3, Empty 0, New 0; Tup 237: Vac 222,
Keep/VTL 0/0, Crash 0, UnUsed 18, MinLen 64
, MaxLen 860; Re-using: Free/Avail. Space 19464/19464; EndEmpty/Avail.
Pages 0/3. CPU 0.00s/0.00u sec.
DEBUG:  Index pg_statistic_relid_att_index: Pages 4; Tuples 237: Deleted
222. CPU 0.00s/0.01u sec.
DEBUG:  Rel pg_statistic: Pages: 6 --> 3; Tuple(s) moved: 190. CPU
0.00s/0.01u sec.
DEBUG:  Index pg_statistic_relid_att_index: Pages 4; Tuples 237: Deleted
190. CPU 0.00s/0.00u sec.
DEBUG:  --Relation pg_operator--
DEBUG:  Pages 9: Changed 0, reaped 0, Empty 0, New 0; Tup 559: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 116,
MaxLen 116; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.01u sec.
DEBUG:  Index pg_operator_oprname_l_r_k_index: Pages 8; Tuples 559. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_operator_oid_index: Pages 4; Tuples 559. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_opclass--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 30: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 68, Ma
xLen 68; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_opclass_name_index: Pages 2; Tuples 30. CPU 0.00s/0.00u
sec.
DEBUG:  Index pg_opclass_deftype_index: Pages 2; Tuples 30. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_am--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 4: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 148, Ma
xLen 148; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_am_name_index: Pages 2; Tuples 4. CPU 0.00s/0.00u sec.
DEBUG:  --Relation pg_amop--
DEBUG:  Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 182: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 46, M
axLen 46; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_amop_strategy_index: Pages 2; Tuples 182. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_amop_opid_index: Pages 2; Tuples 182. CPU 0.00s/0.00u
sec.
DEBUG:  --Relation pg_amproc--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 51: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 46, Ma
xLen 46; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_language--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 3: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 79, Max
Len 84; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_language_oid_index: Pages 2; Tuples 3. CPU 0.00s/0.00u
sec.
DEBUG:  Index pg_language_name_index: Pages 2; Tuples 3. CPU 0.00s/0.00u
sec.
DEBUG:  --Relation pg_aggregate--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 45: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 100, M
axLen 107; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_aggregate_name_type_index: Pages 2; Tuples 45. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_ipl--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_inheritproc--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_rewrite--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 5: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 903, Ma
xLen 1236; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec.
DEBUG:  Index pg_rewrite_rulename_index: Pages 2; Tuples 5. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_rewrite_oid_index: Pages 2; Tuples 5. CPU 0.00s/0.00u
sec.
DEBUG:  --Relation pg_listener--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_listener_relname_pid_index: Pages 2; Tuples 0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_description--
DEBUG:  Pages 9: Changed 0, reaped 0, Empty 0, New 0; Tup 1103: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 42,
MaxLen 110; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.01u sec.
DEBUG:  Index pg_description_objoid_index: Pages 6; Tuples 1103. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_shadow--
DEBUG:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 2: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 76, Max
Len 76; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation directory--
DEBUG:  Pages 6: Changed 0, reaped 1, Empty 0, New 0; Tup 537: Vac 9,
Keep/VTL 0/0, Crash 0, UnUsed 10, MinLen 74,
MaxLen 214; Re-using: Free/Avail. Space 2296/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.01u sec.
DEBUG:  Index directory_username_key: Pages 6; Tuples 537: Deleted 9.
CPU 0.00s/0.00u sec.
DEBUG:  Index directory_pkey: Pages 4; Tuples 537: Deleted 9. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_user--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_rules--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation usertable--
DEBUG:  Pages 9: Changed 0, reaped 9, Empty 0, New 0; Tup 561: Vac 1,
Keep/VTL 0/0, Crash 0, UnUsed 241, MinLen 71,
 MaxLen 139; Re-using: Free/Avail. Space 4280/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.01u sec.
DEBUG:  Index usertable_pkey: Pages 7; Tuples 561: Deleted 1. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_views--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation pg_tables--
DEBUG:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxL
en 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
DEBUG:  --Relation application--
DEBUG:  Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 16: Vac 5,
Keep/VTL 0/0, Crash 0, UnUsed 17, MinLen 112,
MaxLen 300; Re-using: Free/Avail. Space 4020/0; EndEmpty/Avail. Pages
0/0. CPU 0.00s/0.00u sec.
DEBUG:  Index application_pkey: Pages 2; Tuples 16: Deleted 5. CPU
0.00s/0.00u sec.
DEBUG:  --Relation message--
DEBUG:  Pages 88: Changed 0, reaped 84, Empty 0, New 0; Tup 511: Vac 40,
Keep/VTL 0/0, Crash 0, UnUsed 1017, MinLen
 90, MaxLen 2380; Re-using: Free/Avail. Space 602120/594316;
EndEmpty/Avail. Pages 0/78. CPU 0.01s/0.00u sec.
DEBUG:  Index message_pod_key: Pages 5; Tuples 511: Deleted 30. CPU
0.00s/0.00u sec.
DEBUG:  Index message_originator_key: Pages 5; Tuples 511: Deleted 30.
CPU 0.00s/0.00u sec.
DEBUG:  Index message_pkey: Pages 4; Tuples 511: Deleted 30. CPU
0.01s/0.00u sec.
DEBUG:  Rel message: Pages: 88 --> 15; Tuple(s) moved: 2. CPU
0.02s/0.00u sec.
DEBUG:  Index message_pod_key: Pages 5; Tuples 511: Deleted 2. CPU
0.00s/0.00u sec.
DEBUG:  Index message_originator_key: Pages 5; Tuples 511: Deleted 2.
CPU 0.00s/0.00u sec.
DEBUG:  Index message_pkey: Pages 4; Tuples 511: Deleted 2. CPU
0.00s/0.00u sec.
NOTICE:  FlushRelationBuffers(message, 15): block 0 is referenced
(private 0, global 2)
FATAL 1:  VACUUM (vc_repair_frag): FlushRelationBuffers returned -2






--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Joseph Shraibman <jks@selectacast.net> writes:
> Could someone please tell me what is going wrong?
> query: VACUUM  ANALYZE
> NOTICE:  FlushRelationBuffers(message, 15): block 0 is referenced
> (private 0, global 2)
> FATAL 1:  VACUUM (vc_repair_frag): FlushRelationBuffers returned -2

This is probably an artifact of the known bug that 7.0.* forgets to
run AbortTransaction() if a client disconnects in the middle of a
BEGIN block.  There's no harm to your data, but buffer reference counts
in shared memory aren't decremented before the backend exits.  So,
VACUUM gets unhappy because it fears some other backend is touching
the table it's vacuuming.

Restarting the postmaster should make the symptom go away.  The bug
itself is fixed for 7.0.3.

            regards, tom lane

Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> writes:
> > Could someone please tell me what is going wrong?
> > query: VACUUM  ANALYZE
> > NOTICE:  FlushRelationBuffers(message, 15): block 0 is referenced
> > (private 0, global 2)
> > FATAL 1:  VACUUM (vc_repair_frag): FlushRelationBuffers returned -2
>
> This is probably an artifact of the known bug that 7.0.* forgets to
> run AbortTransaction() if a client disconnects in the middle of a
> BEGIN block.

Ah, I was torture testing my application to see how it handled
application crashes, simulated by making the program exit before it was
done.

  There's no harm to your data, but buffer reference counts
> in shared memory aren't decremented before the backend exits.  So,
> VACUUM gets unhappy because it fears some other backend is touching
> the table it's vacuuming.
>
> Restarting the postmaster should make the symptom go away.  The bug
> itself is fixed for 7.0.3.
>

When is 7.0.3 coming out?


--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Joseph Shraibman <jks@selectacast.net> writes:
> When is 7.0.3 coming out?

Next week, probably.  We were holding off a release to see if we could
find the cause of the VACUUM crashes Alfred Perlstein's been reporting
for months.  I think that's finally fixed, but we want to let Alfred
beat on the patch for a week or so to make sure.

If you are in a hurry you can pull 7.0.3-to-be from our CVS server;
see http://www.postgresql.org/docs/postgres/x28786.htm, but be sure to
grab the REL7_0_PATCHES branch (cvs checkout -r REL7_0_PATCHES pgsql
instead of just cvs checkout pgsql).

            regards, tom lane