Thread: Vacuum behaviour in plpgsql function

I have the following function in PostgreSQL 7.2.1 on i586-pc-linux-gnu, compiled by GCC 2.96:

CREATE FUNCTION "fct_vacuum_db"() RETURNS "int2" AS '

DECLARE
   db_table RECORD;
   v_overhead float;

BEGIN
   FOR db_table IN SELECT relname FROM pg_class WHERE relname like ''tb_%'' AND relkind = ''r'' LOOP

      SELECT pgstattuple(db_table.relname)INTO v_overhead;

      IF v_overhead >= 0 THEN
         RAISE NOTICE ''Vacuuming table (%)'',db_table.relname;
         EXECUTE ''vacuum analyze ''|| db_table.relname || '';'';
      END IF;
  END LOOP;
return 1;

==============================
Using the function pgstatuple I want to use this to control what gets vacuumed. However, whenever the EXECUTE statement
runsI get the following message from the backend:  

==============================
NOTICE: physical length: 0.00MB live tuples: 0 (0.00MB, 0.00%) dead tuples: 0 (0.00MB, 0.00%) free/reusable space:
0.00MB(0.00%) overhead: 0.00%  
NOTICE: Vacuuming table (tb_a)
server closed the connection unexpectedly This probably means the server terminated abnormally before or while
processingthe request. 
The connection to the server was lost. Attempting reset: Failed.
===============================

What am I doing wrong ??


----------------------------------------------------------------------------------------
Alain Lavigne - Data Administrator - ZAQ Interactive Solutions  E-Mail: alavigne@zaq.com
297 St-Paul, West - Montreal, Quebec, Canada  - H2Y 2A5
Phone: 514-282-7073 ext: 371 - Fax: 514-282-8011

"Alain Lavigne" <alavigne@zaq.com> writes:
> Using the function pgstatuple I want to use this to control what gets
> vacuumed.

You can't run VACUUM from inside a function because of memory-management
limitations.  In current sources there is an error check to prevent you
from trying.

            regards, tom lane

Thanks Tom, I didn't know.  I will find another way to do it.

----------------------------------------------------------------------------------------
Alain Lavigne - Data Administrator - ZAQ Interactive Solutions  E-Mail: alavigne@zaq.com
297 St-Paul, West - Montreal, Quebec, Canada  - H2Y 2A5
Phone: 514-282-7073 ext: 371 - Fax: 514-282-8011


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: June 3, 2002 19:41
To: Alain Lavigne
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum behaviour in plpgsql function


"Alain Lavigne" <alavigne@zaq.com> writes:
> Using the function pgstatuple I want to use this to control what gets
> vacuumed.

You can't run VACUUM from inside a function because of memory-management
limitations.  In current sources there is an error check to prevent you
from trying.

            regards, tom lane