From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: SZŰCS Gábor <surrano@mailbox.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2
Date: Tue, 6 May 2003 15:53:22 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0305061552020.22644-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <004a01c313ca$18c12ae0$0a03a8c0@fejleszt2>
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@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
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: achill@matrix.gatewaynet.com, surrano@mailbox.hu
Subject: Re: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2
In-Reply-To: <Pine.LNX.4.44.0305061552020.22644-100000@matrix.gatewaynet.com>
* 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