mysql workbench cannot be executed from_无法从函数或多命令字符串执行VACUUM (VACUUM cannot be executed from a funct...

2014-02-13 18:59:56

1

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.