VACUUM cannot be executed from a function or multi-command string

Question

I have written a script, using PL/pgSQL, that I run in pgAdmin III. The script deletes existing DB contents and then adds a bunch of "sample" data for the desired testing scenario (usually various types of load tests). Once the data is loaded, I would like to "vacuum analyze" the affected tables, both to recover the space from the deleted records and to accurately reflect the new contents.

I can use various workarounds (e.g. do the VACUUM ANALYZE manually, include drop/create statements for the various structures within the script, etc.) But, what I would really like to do is:

DO $$
BEGIN
  -- parent table
  FOR i IN 1..10000 LOOP
    INSERT INTO my_parent_table( ... ) VALUES ...;
  END LOOP;

  VACUUM ANALYZE my_parent_table;

  -- child table
  FOR i IN 1..50000 LOOP
    INSERT INTO my_child_table( ... ) VALUES ...;
  END LOOP;

  VACUUM ANALYZE my_child_table;
END;
$$;

When I run this, I get:

ERROR:  VACUUM cannot be executed from a function or multi-command string

So then I tried moving the vacuum statements to the end like so:

DO $$
BEGIN
  -- parent table
  FOR i IN 1..10000 LOOP
    INSERT INTO my_parent_table( ... ) VALUES ...;
  END LOOP;

  -- child table
  FOR i IN 1..50000 LOOP
    INSERT INTO my_child_table( ... ) VALUES ...;
  END LOOP;
END;
$$;

VACUUM ANALYZE my_parent_table;
VACUUM ANALYZE my_child_table;

This give me the same error. Is there any way I can incorporate the vacuum analyze into the same script that adds the data?

I am using PostgreSQL v 9.2.

Answer 1

If you are running this from the pgAdmin3 query window with the "execute query" button, that sends the entire script to the server as one string.

If you execute it from the query window "execute pgScript" button, that sends the commands separately and so would work, except that it does not tolerate the DO syntax for anonymous blocks. You would have to create a function that does the currently anonymous work, and then invoke the "execute pgScript" with something like:

select inserts_function();
VACUUM ANALYZE my_parent_table;
VACUUM ANALYZE my_child_table;

Answer 2

The final solution I implemented ended up being a composite of suggestions made in the comments by a_horse_with_no_name and xzilla:

• Using TRUNCATE instead of DELETE avoids the need for a VACUUM
• ANALYZE can then be used by itself in-line during the script as needed