Date: Tue, 6 May 2003 15:53:22 -0200 (GMT+2)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: =?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <surrano@mailbox.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2
In-Reply-To: <004a01c313ca$18c12ae0$0a03a8c0@fejleszt2>
Message-ID: <Pine.LNX.4.44.0305061552020.22644-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: quoted-printable

On Tue, 6 May 2003, [iso-8859-2] SZ=DBCS G=E1bor wrote:

> Dear Gurus,
>=20
> I have a problem I've never met before, nor similar. Also, I don't have a=
ny
> 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.
>=20
> I have two tables and I wish to make a FK between them, such as:
>=20
>   ALTER TABLE invoice
>   ADD FOREIGN KEY (seller_account) REFERENCES account(id);
>=20
> 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...)
>=20
> Tried this and tried that, and finally tried
>=20
>   VACUUM FULL VERBOSE ANALYZE invoice;
>=20
> 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:
>=20
>   INFO:  --Relation public.invoice--
>   INFO:  Pages 44: Changed 0, Empty 0; Tup 1318: Vac 0, Keep 0, UnUsed 12=
7.
>           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
>=20
> As you may have found out, VACUUM ANALYZE and ANALYZE seemed to work, but
> VACUUM FULL ANALYZE and ALTER TABLE didn't.
>=20
> What may be the problem? If ps output helps:
>=20
> 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
>=20
> ... 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)?

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

--=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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