From: SZŰCS Gábor <surrano@mailbox.hu>
To: pgsql-sql@postgresql.org
Subject: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2
Date: Tue, 6 May 2003 14:22:01 +0200
Message-ID: <004a01c313ca$18c12ae0$0a03a8c0@fejleszt2> (raw)
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?
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: surrano@mailbox.hu
Subject: Re: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2
In-Reply-To: <004a01c313ca$18c12ae0$0a03a8c0@fejleszt2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox