Re:

On 12/23/05 11:05 AM, "Lane Van Ingen" <lvaningen(at)esncc(dot)com> wrote:

> 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?

I don't know the answer to your question about vacuum. Others might ask for
some code from you showing your trigger function and the error generated....

As for the data manipulation/loading/etc., I find that loading to a "loader"
table is quite beneficial. In other words, use COPY to nearly
instantaneously load data into the "loader" table. Then, write a script to
go through the process of moving them to the separate tables.

Did you tinker with autovacuum--will this help you?

Sean