I am using Windows 2003 + version 8.0.1.
We are using PostgreSQL to do daily massive data loads (inserts and updates
of 1 million+ input records) to one of our applications. We have found that
PostgreSQL works fastest if we do a vacuum after every 8,000 update
transactions. In order to make it work like this, we have had to write a DOS
SCRIPT, which breaks the input up into 8K blocks of rows:
- use a DOS SCRIPT to take an input file of transactions, in comma
delimited
format, and break in into 8000 row pieces
- execute a PL/PGSQL FUNCTION, which reformats the DOS SCRIPT ouput and
makes
INSERT and UPDATE statements (mostly UPDATEs) to as many as 16
different
tables
- most of the tables being updated fire PL/PGSQL TRIGGER FUNCTIONs to
perform
calculations on the incoming records.
We would like to eliminate the DOS SCRIPT, and (rather than doing vacuums
after
8,000 transactions, do the VACUUMs on EACH of the 16 tables whenever they
individually exceed 8,000 updates.
- We have tried imbedding a VACUUM command into each of the 16 tables'
PL/PGSQL TRIGGER functions, but pl/pgsql does not permit the execution
of a VACUUM command in those functions (gives an SPI_ERROR message).
- We tried LISTEN / NOTIFY between the pg/plsql trigger function and an
external Java program, but found out that pl/pgsql would apparently
will
not allow the listening program to sucessfully execute a VACUUM command
until after trigger functions are done updating their tables. (We
presume
this happens because the tables on which VACUUMs are to be performed
are
still locked).
Can anyone advise how to do this? I want to be able to fire off a VACUUM
<tbl>
command at my request. How do I do this, outside of breaking up the input
into
blocks of transactions?