X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 65C41475E77 for ; Tue, 6 May 2003 08:22:02 -0400 (EDT) Received: from mail.deltav.hu (mail.deltav.hu [213.163.0.192]) by postgresql.org (Postfix) with ESMTP id E6468474E42 for ; Tue, 6 May 2003 08:22:00 -0400 (EDT) Received: from fejleszt2 ([213.163.10.103]) by mail.deltav.hu (Netscape Messaging Server 3.6) with SMTP id AAA150E for ; Tue, 6 May 2003 14:22:02 +0200 Message-ID: <004a01c313ca$18c12ae0$0a03a8c0@fejleszt2> From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" To: Subject: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2 Date: Tue, 6 May 2003 14:22:01 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Spam-Status: No, hits=-5.8 required=5.0 tests=BAYES_10 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/79 X-Sequence-Number: 13244 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 ------------------------------