Re: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2

On Tue, 6 May 2003, [iso-8859-2] SZΫCS Gαbor wrote:

> Dear Gurus,
>
> I have a problem I've never met before, nor similar. Also, I don't have any
> ideas how to search the archives for similar accidents. If you can point me
> to the right direction, please don't hesitate to do so.
>
> I have two tables and I wish to make a FK between them, such as:
>
> ALTER TABLE invoice
> ADD FOREIGN KEY (seller_account) REFERENCES account(id);
>
> Quite simple, isn't it? In 7.2.1, it executed in a second or two for two
> db's (essentially, the same skeleton with different data. No big deal).
> However, in 7.3.2, it does __nothing__ (not even a notice or warning or
> info), or at least, it takes ages. psql won't give the prompt back in 20
> minutes (yes, minutes... still running...)
>
> Tried this and tried that, and finally tried
>
> VACUUM FULL VERBOSE ANALYZE invoice;
>
> It did *nothing* though I waited for at least two minutes. Not even the
> first INFO line, as in the output for VACUUM VERBOSE ANALYZE:
>
> INFO: --Relation public.invoice--
> INFO: Pages 44: Changed 0, Empty 0; Tup 1318: Vac 0, Keep 0, UnUsed 127.
> Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: --Relation pg_toast.pg_toast_3349048--
> INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
> Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: Analyzing public.invoice
> VACUUM
>
> As you may have found out, VACUUM ANALYZE and ANALYZE seemed to work, but
> VACUUM FULL ANALYZE and ALTER TABLE didn't.
>
> What may be the problem? If ps output helps:
>
> 17058 1 0 00:00:04 /usr/local/pgsql-7.3.2/bin/postmaster
> -p 5433 -i -N 128 -B 256
> 17060 17058 0 00:00:00 [postmaster]
> 7324 17058 0 00:00:01 postgres: sd bin 192.168.3.10 idle
> 7325 17058 0 00:00:04 postgres: sd bin 192.168.3.10 idle in transaction
> 17238 17058 0 00:00:09 postgres: postgres bin [local] ALTER TABLE waiting
>
> ... and CPU is around 10%. What is this "waiting" thing?

Maybe its a locking issue.

Did you quit any app using the DB?
Did you commit/rollback all pending transactions (if any)?

>
> G.
> --
> while (!asleep()) sheep++;
>
> ---------------------------- cut here ------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr