Re: VACUUM and transactions in different databases

Cornelia Boenigk <c@cornelia-boenigk.de> wrote:
>
> Hi Bill
>
>  > I don't believe that's the reason.  AFAIK, activity in one database
>  > will never block activity in another.
>
> This way I read the documentation.

psql -U pgsql db2
Welcome to psql 8.2.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

psql -U pgsql db1
Welcome to psql 8.2.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

db1=# begin;
BEGIN
db1=# insert into t1 values (44, 'text string');
INSERT 0 1
db1=#
[1]+  Stopped                 psql -U pgsql db1
[wmoran@working ~]$ psql -U pgsql db2
Welcome to psql 8.2.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

db2=# vacuum full;
VACUUM
db2=# \q
[wmoran@working ~]$ fg
psql -U pgsql db1
rollback;
ROLLBACK

Works that way for me ...

>  > I would suspect that you haven't vacuumed this database in a long time,
>
> I created both databases one hour ago for just testing this behaviour. I
> started with two identical tables, each with 5000 rows in both
> databases. In db1 I opened a transaction, updated the table and left the
> transaction open.
>
> In db2 I updated, inserted and deleted a lot and then tried to vacuum.

So, long time then.  My definition of "long time" is equal to your
definition of "a lot" :)

>  > 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

You might want to provide some more details on what you're doing.
Obviously, the simplified version of your problem doesn't exist (as
demonstrated by the fact that I can't reproduce it).  Perhaps your
transaction is doing something different that what you expect.

-Bill