Re: Question about Idle in TX

On Tue, Aug 03, 2010 at 03:57:27PM -0400, Tom Lane wrote:
- David Kerr <dmk(at)mr-paradox(dot)net> writes:
- > On Tue, Aug 03, 2010 at 03:49:57PM -0400, Tom Lane wrote:
- > - In recent versions of PG, no. Before about 8.3 it was a Really Bad Idea,
- > - because the open transaction would prevent VACUUM from reclaiming storage.
-
- > We're on 8.3.9, so hopefully it's fairly safe then?
-
- Should be. You might want to test it just to make sure I'm recalling
- correctly when that got fixed. Do a BEGIN in one session, then in
- another session insert and delete some rows in a table, then VACUUM
- VERBOSE and see if they get cleaned up.
-
- regards, tom lane
-
Sorry for the delayed response, been in RedHat training all week.

Seems like it worked fine in 9.0beta3:
SESSION 1
psql
test=> begin;
BEGIN
test=>

SESSION 2
test=# insert into test (select generate_series(1001,1999,1));
INSERT 0 999
test=# delete from test;
DELETE 1999
test=# VACUUM VERBOSE test;
INFO: vacuuming "public.test"
INFO: "test": removed 1999 row versions in 8 pages
INFO: "test": found 1999 removable, 0 nonremovable row versions in 8 out of 8
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "test": truncated 8 to 0 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM