Re: VACUUM and transactions in different databases

Cornelia Boenigk <c(at)cornelia-boenigk(dot)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(at)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(at)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(at)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