Re: VACUUM and transactions in different databases

In response to Tom Lane <tgl@sss.pgh.pa.us>:

> Bill Moran <wmoran@collaborativefusion.com> writes:
> > In response to Alvaro Herrera <alvherre@commandprompt.com>:
> >> Of course they are able to complete, but the point is that they would
> >> not remove the tuples that would be visible to that idle open
> >> transaction.
>
> > I would expect that, but the OP claimed that vacuum full waited until
> > the other transaction was finished.
>
> No, she didn't claim that.  As far as I see she was just complaining
> about the failure to remove dead tuples:
>
> > > > > If I have a running transaction in database1 and try to vacuum
> > > > > database2 but the dead tuples in database2 cannot be removed.

Yes, but a later mail in the thread read:

> > Can you run a "vacuum
> > full", and does it reclaim the space?
>
> I tried but it hangs.
>
> [root@conni ~]# ps axw|grep postgres
>  1746 ?        S      0:00 postgres: writer process
>  1747 ?        S      0:00 postgres: stats buffer process
>  1748 ?        S      0:00 postgres: stats collector process
>  2106 pts/1    S      0:00 su postgres
>  2120 pts/1    S+     0:00 psql postgres
>  2188 ?        S      0:04 postgres: postgres dummy1 [local] VACUUM waiting
>  2200 pts/3    S      0:00 su postgres
>  2215 ?        S      0:00 postgres: postgres dummy2 [local] idle in transaction
>  2717 pts/2    R+     0:00 grep postgres

Admittedly, I had the (incorrect) idea that she might need a vacuum
full to reclaim space that lazy vacuum couldn't.  And, admittedly, this
wasn't the point of the original post.

--
Bill Moran
Collaborative Fusion Inc.