Re: Problems Vacuum'ing - Mailing list pgsql-hackers

Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:
> 
[snip]
> 
> Turn on query logging and see if the BEGIN is issued right after the
> COMMIT/ROLLBACK, or whether it waits and issues it right before
> SELECT/CREATE TEMP TABLE.
> 
> It doesn't matter if it's only doing queries; if it does them inside a
> transaction, it would be enough to keep VACUUM from working "properly."

Will the following do as well?

(Thanks to Jochem for the pointer for how to determine open
transactions and the pg_stat_activity hint.)

Logged into work.  WebObects application is running.  Database I'm
working with partly populated from earlier work.

postgres=# select * from pg_locks where transaction is not null;relation | database | transaction |  pid  |     mode
 | granted 
 
----------+----------+-------------+-------+---------------+---------         |          |     1245358 | 18020 |
ExclusiveLock| t         |          |     1245364 |   267 | ExclusiveLock | t
 
(2 rows)

postgres=# select * from pg_stat_activity;datid | datname  | procpid | usesysid |  usename   | current_query |
query_start
 
-------+----------+---------+----------+------------+---------------+-------------17142 | postgres |     267 |        1
|postgres   |               | 17144 | qantel   |   18020 |      103 | webobjects |               | 
 
(2 rows)


sysagent=> delete from ethers;
DELETE 368
sysagent=> delete from host_mac_hist;
DELETE 169
sysagent=> vacuum full analyze verbose ethers;
INFO:  vacuuming "public.ethers"
INFO:  "ethers": found 0 removable, 368 nonremovable row versions in 4 pages
DETAIL:  368 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 88 bytes long.
There were 55 unused item pointers.
Total free space (including removable row versions) is 3724 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3628 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ethers_hostname_key" now contains 368 row versions in 275 pages
DETAIL:  0 index row versions were removed.
223 index pages have been deleted, 223 are currently reusable.
CPU 0.01s/0.03u sec elapsed 0.23 sec.
INFO:  "ethers": moved 0 row versions, truncated 4 to 4 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.ethers"
INFO:  "ethers": 4 pages, 0 rows sampled, 0 estimated total rows
VACUUM
sysagent=> vacuum full analyze verbose host_mac_hist;
INFO:  vacuuming "public.host_mac_hist"
INFO:  "host_mac_hist": found 0 removable, 169 nonremovable row versions in 2 pages
DETAIL:  169 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 80 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 3556 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3532 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "host_mac_hist": moved 0 row versions, truncated 2 to 2 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.host_mac_hist"
INFO:  "host_mac_hist": 2 pages, 0 rows sampled, 0 estimated total rows
VACUUM

Shut down WebObjects.  Clear both tables.  Do full vacuum.
Re-populate tables.  Then...

postgres=# select * from pg_locks where transaction is not null;relation | database | transaction | pid  |     mode
| granted 
 
----------+----------+-------------+------+---------------+---------         |          |     1245558 | 3110 |
ExclusiveLock| t
 
(1 row)

postgres=# select * from pg_stat_activity;datid | datname  | procpid | usesysid | usename  | current_query |
query_start
 
-------+----------+---------+----------+----------+---------------+-------------17142 | postgres |    3110 |        1 |
postgres|               | 
 
(1 row)

sysagent=> delete from ethers;
DELETE 368
sysagent=> delete from host_mac_hist;
DELETE 169
sysagent=> vacuum full analyze verbose ethers;
INFO:  vacuuming "public.ethers"
INFO:  "ethers": found 10030 removable, 0 nonremovable row versions in 98 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 760736 bytes.
98 pages are or will become empty, including 98 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
INFO:  index "ethers_hostname_key" now contains 0 row versions in 275 pages
DETAIL:  10030 index row versions were removed.
271 index pages have been deleted, 271 are currently reusable.
CPU 0.00s/0.08u sec elapsed 0.08 sec.
INFO:  "ethers": truncated 98 to 0 pages
INFO:  analyzing "public.ethers"
INFO:  "ethers": 0 pages, 0 rows sampled, 0 estimated total rows
VACUUM
sysagent=> vacuum full analyze verbose host_mac_hist;
INFO:  vacuuming "public.host_mac_hist"
INFO:  "host_mac_hist": found 169 removable, 0 nonremovable row versions in 2 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 15668 bytes.
2 pages are or will become empty, including 2 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "host_mac_hist": truncated 2 to 0 pages
INFO:  analyzing "public.host_mac_hist"
INFO:  "host_mac_hist": 0 pages, 0 rows sampled, 0 estimated total rows
VACUUM

Again the difference: With WebObjects running, deleting rows and
trying to vacuum immediately, even full, fails.  Shut-down WebObjects
and I can.

Next test? ;)

Jim