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
format, and break in into 8000 row pieces
- execute a PL/PGSQL FUNCTION, which reformats the DOS SCRIPT ouput and
INSERT and UPDATE statements (mostly UPDATEs) to as many as 16
- most of the tables being updated fire PL/PGSQL TRIGGER FUNCTIONs to
calculations on the incoming records.
We would like to eliminate the DOS SCRIPT, and (rather than doing vacuums
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
not allow the listening program to sucessfully execute a VACUUM command
until after trigger functions are done updating their tables. (We
this happens because the tables on which VACUUMs are to be performed
Can anyone advise how to do this? I want to be able to fire off a VACUUM
command at my request. How do I do this, outside of breaking up the input
blocks of transactions?
- Re: at 2005-12-23 16:19:43 from Sean Davis
- Re: at 2005-12-23 16:28:59 from Tom Lane
pgsql-novice by date
|Next:||From: Sean Davis||Date: 2005-12-23 16:19:43|
|Subject: Re: |
|Previous:||From: Mag Gam||Date: 2005-12-23 15:15:39|
|Subject: PostgreSQL Logging|