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

VACUUM FULL and ALTER TABLE do *nothing* in 7.3.2

From
"SZŰCS Gábor"
Date:
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.
TotalCPU 0.00s/0.00u sec elapsed 0.00 sec. INFO:  --Relation pg_toast.pg_toast_3349048-- INFO:  Pages 0: Changed 0,
Empty0; Tup 0: Vac 0, Keep 0, UnUsed 0.         Total CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO:  Analyzing
public.invoiceVACUUM
 

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
-N128 -B 256
 
17060 17058  0 00:00:00 [postmaster]7324 17058  0 00:00:01 postgres: sd bin 192.168.3.10 idle7325 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 ------------------------------



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

From
Achilleus Mantzios
Date:
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



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

From
"SZUCS Gabor"
Date:
Dear Achilleus,

Dunno, it's a multiuser environment. However, I'll try asking everyone to
stop working for a minute...

... oops, I just see that it finished about half an hour before (taking an
amazing 76 minutes for ALTER TABLE to complete). About that time, I started
and exited a client. Seems it solved the problem. Now "vacuum full analyze"
finished in a couple of seconds.

Do you have any idea about an explanation? Does vacuum wait for the end of
each and every transaction?

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
Sent: Tuesday, May 06, 2003 7:53 PM


Maybe its a locking issue.

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



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

From
Achilleus Mantzios
Date:
On Tue, 6 May 2003, SZUCS Gabor wrote:

> Dear Achilleus,
> 
> Dunno, it's a multiuser environment. However, I'll try asking everyone to
> stop working for a minute...
> 
> ... oops, I just see that it finished about half an hour before (taking an
> amazing 76 minutes for ALTER TABLE to complete). About that time, I started
> and exited a client. Seems it solved the problem. Now "vacuum full analyze"
> finished in a couple of seconds.
> 
> Do you have any idea about an explanation? Does vacuum wait for the end of
> each and every transaction?

Just take a look at 
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-lock.html

VACUUM FULL acquires a ACCESS EXCLUSIVE MODE.

That means that if a table is accessed in any way
the VACUUM FULL command will wait.

Open Cursors might be a reason.

> 
> G.
> --
> while (!asleep()) sheep++;
> 
> ---------------------------- cut here ------------------------------
> ----- Original Message -----
> From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
> Sent: Tuesday, May 06, 2003 7:53 PM
> 
> 
> Maybe its a locking issue.
> 
> Did you quit any app using the DB?
> Did you commit/rollback all pending transactions (if any)?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
==================================================================
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



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

From
Stephan Szabo
Date:
On Tue, 6 May 2003, SZUCS Gabor wrote:

> Dear Achilleus,
>
> Dunno, it's a multiuser environment. However, I'll try asking everyone to
> stop working for a minute...
>
> ... oops, I just see that it finished about half an hour before (taking an
> amazing 76 minutes for ALTER TABLE to complete). About that time, I started
> and exited a client. Seems it solved the problem. Now "vacuum full analyze"
> finished in a couple of seconds.
>
> Do you have any idea about an explanation? Does vacuum wait for the end of
> each and every transaction?

Both alter table and vacuum full want to take exclusive locks on the
table IIRC.  If say that transaction that was idle had done *anything* on
the table and was sitting open after that, both of those would wait until
the transaction released the lock.



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

From
Tom Lane
Date:
"SZUCS Gabor" <surrano@mailbox.hu> writes:
> Do you have any idea about an explanation? Does vacuum wait for the end of
> each and every transaction?

VACUUM FULL needs an exclusive lock on the target table.  So does ALTER
TABLE.  So yes, it will wait for any open transaction that has accessed
the table to complete.
        regards, tom lane