Re: No long-lived transaction, still can't delete tuples

At 09:56 AM 4/25/02 -0400, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > At 06:59 PM 4/24/02 -0400, Tom Lane wrote:
> >> Until client A returns from his lunch break, you'll not be able to
> >> vacuum the trash that client B generated, even though B has committed
> >> his changes.
>
> > Does just a BEGIN without anything else hold up vacuum? It doesn't seem to
> > for 7.1.3. Whereas a BEGIN followed by a select from a table holds up
> > vacuum once vacuum reaches the relevant table.
>
>You're confusing obtaining a lock with determining xmin for tuple
>removal purposes.

OK. I get it now.

> > BEGIN followed by select
> > (1), causes vacuum to stop with:
> > ERROR:  Parent tuple was not found
>
>Oh?  If you have a repeatable example of that, I'd like to see it.

Hmm, only happens in one database where I have (for long periods) two
processes continuously inserting/updating data in two separate tables.
Maybe that database is a bit screwed up. I tried creating another database
and doing manual inserts into a table and I can't reproduce it.

well maybe vacuum verbose might help:
VACUUM verbose;
NOTICE:  Skipping "pg_type" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attribute" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_class" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_group" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_database" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attrdef" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_trigger" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inherits" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_index" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_operator" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_opclass" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_am" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amop" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_language" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_largeobject" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_aggregate" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_ipl" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inheritproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_description" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_listener" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_shadow" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_proc" --- only table owner can VACUUM it
NOTICE:  --Relation wordlist--
NOTICE:  Pages 42: Changed 0, reaped 0, Empty 0, New 0; Tup 6313: Vac 0,
Keep/VTL 0/0, Crash
  0, UnUsed 0, MinLen 41, MaxLen 57; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0
/0. CPU 0.00s/0.00u sec.
NOTICE:  Index wordlist_id_key: Pages 28; Tuples 6313. CPU 0.00s/0.01u sec.
NOTICE:  --Relation pg_toast_29612--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0,
UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CP
U 0.00s/0.00u sec.
NOTICE:  Index pg_toast_29612_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation arch_ranks_arch4--
NOTICE:  Pages 137: Changed 2, reaped 135, Empty 0, New 0; Tup 6892: Vac 0,
Keep/VTL 31/31,
Crash 0, UnUsed 4238, MinLen 88, MaxLen 1138; Re-using: Free/Avail. Space
10412/2676; EndEmp
ty/Avail. Pages 0/24. CPU 0.00s/0.00u sec.
NOTICE:  Rel arch_ranks_arch4: Pages: 137 --> 137; Tuple(s) moved: 0. CPU
0.01s/0.07u sec.
NOTICE:  --Relation pg_toast_102844--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0,
UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CP
U 0.00s/0.00u sec.
NOTICE:  Index pg_toast_102844_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation arch_ranks_arch6--
NOTICE:  Pages 319: Changed 2, reaped 318, Empty 0, New 0; Tup 10529: Vac
0, Keep/VTL 30/30,
  Crash 0, UnUsed 8628, MinLen 88, MaxLen 1143; Re-using: Free/Avail. Space
13548/3488; EndEm
pty/Avail. Pages 0/24. CPU 0.01s/0.01u sec.
ERROR:  Parent tuple was not found

Another try:
VACUUM verbose;
NOTICE:  Skipping "pg_type" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attribute" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_class" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_group" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_database" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_attrdef" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_trigger" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inherits" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_index" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_operator" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_opclass" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_am" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amop" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_amproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_language" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_largeobject" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_aggregate" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_ipl" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_inheritproc" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_description" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_listener" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_shadow" --- only table owner can VACUUM it
NOTICE:  Skipping "pg_proc" --- only table owner can VACUUM it
NOTICE:  --Relation wordlist--
NOTICE:  Pages 42: Changed 0, reaped 0, Empty 0, New 0; Tup 6313: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 41, MaxLen 57; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.01s/0.00u sec.
NOTICE:  Index wordlist_id_key: Pages 28; Tuples 6313. CPU 0.00s/0.01u sec.
NOTICE:  --Relation pg_toast_29612--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 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.
NOTICE:  Index pg_toast_29612_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  --Relation arch_ranks_arch4--
NOTICE:  Pages 137: Changed 2, reaped 135, Empty 0, New 0; Tup 6867: Vac 0,
Keep/VTL 6/6, Crash 0, UnUsed 4266, MinLen 88, MaxLen 1138; Re-using:
Free/Avail. Space 13412/3076; EndEmpty/Avail. Pages 0/26. CPU 0.00s/0.00u sec.
ERROR:  Parent tuple was not found

Another try, narrowing vacuum to one table:
session #1: rollback ;begin; select (1);

session #2: VACUUM verbose arch_ranks_arch4;
NOTICE:  --Relation arch_ranks_arch4--
NOTICE:  Pages 137: Changed 2, reaped 135, Empty 0, New 0; Tup 6867: Vac 0,
Keep/VTL 6/6, Crash 0, UnUsed 4266, MinLen 88, MaxLen 1138; Re-using:
Free/Avail. Space 13640/3136; EndEmpty/Avail. Pages 0/26. CPU 0.00s/0.01u sec.
ERROR:  Parent tuple was not found

session #1: rollback;
session #2: VACUUM verbose arch_ranks_arch4;
NOTICE:  --Relation arch_ranks_arch4--
NOTICE:  Pages 138: Changed 1, reaped 135, Empty 0, New 0; Tup 6861: Vac
101, Keep/VTL 0/0, Crash 0, UnUsed 4212, MinLen 88, MaxLen 1138; Re-using:
Free/Avail. Space 19516/15948; EndEmpty/Avail. Pages 0/32. CPU 0.01s/0.00u sec.
NOTICE:  Rel arch_ranks_arch4: Pages: 138 --> 136; Tuple(s) moved: 105. CPU
0.00s/0.02u sec.
NOTICE:  --Relation pg_toast_102844--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 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.
NOTICE:  Index pg_toast_102844_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
VACUUM

\d arch_ranks_arch4
              Table "arch_ranks_arch4"
   Attribute  |           Type           | Modifier
-------------+--------------------------+----------
  id          | integer                  |
  updated     | timestamp with time zone |
  valid       | integer                  |
  name        | text                     |
  specialty   | text                     |
  status      | text                     |
  ranking     | integer                  |
  power       | integer                  |
  land        | integer                  |
  forts       | integer                  |
  description | text                     |

Regards,
Link.