12

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.

Robert N
  • 1,156
  • 2
  • 14
  • 32
  • 2
    A bulk *insert* doesn't really need a vacuum afterwards. vacuum is only needed if you *delete* or update large amounts of data (because it reclaims space that is no longer needed). In your case `analyze` itself should be enough. –  Feb 13 '14 at 21:46
  • Thank you, but I really do want "VACUUM ANALYZE"; before added the new data for the desired testing scenario, the script first deletes all existing data. I have edited the question to make this explicit. – Robert N Feb 21 '14 at 18:11
  • 2
    If your script is first deleting all existing data, perhaps you should truncate the tables instead, thereby avoiding the need to vacuum. – xzilla Mar 02 '14 at 04:34
  • 1
    In addition to the __jjanes__ answer do `vacuum analyze` __before__ the `insert` just after `delete` (or `truncate`) as it will be instantaneous. – Clodoaldo Neto Mar 03 '14 at 14:11
  • [This answer](https://dba.stackexchange.com/a/182270/203446) on dba.stackexchange resolved it for me: Call `ANALYZE` in your function, but leave it up to autovacuum to elegantly free up space (just make sure you have configured autovacuum thresholds for your large tables). – Duncanmoo Mar 19 '21 at 10:11

2 Answers2

14

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;
jjanes
  • 37,812
  • 5
  • 27
  • 34
1

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
Robert N
  • 1,156
  • 2
  • 14
  • 32