Re:

Sean: yes, we do load into a 'loader table' at the end of the DOS script,
using the COPY command.

Tom: so we have (partly) learned. That means the LISTEN/NOTIFY approach
ought to work but it doesn't work until after the functions have run. We
have also noticed that we can't 'see' (via pgAdmin3) any of the updates
until all have finished, and we are thinking the reason for that is the same
reason a VACUUM request from a client program doesn't work either.

Campaigh Runner: the target table(s) are really part of a data wharehouse;
some are small (< 100 rows), but get updated frequently (40x / second);
others are larger, with the largest being probably ~ 100K rows. Most of the
columns get updated, but not all; most of the transactions are updates,
mixed in with occasional inserts.

-----Original Message-----
From: CampaignRunner [mailto:support(at)campaign-runner(dot)com]
Sent: Friday, December 23, 2005 1:19 PM
To: lvaningen(at)esncc(dot)com
Subject: Re: [NOVICE]

Hello,

We have been using PostgreSQL to do updates of 100,000 plus records, so
I am intrigued by the fact that you need to vacuum after every 8000
updates. How big is the target table (i.e., number of rows and number
of columns)? How many columns are updated at a time? Outside of these
transactions, how often do you vacuum the target table? Finally, what
sort of calculations are the triggers performing?

Sincerely,

CampaignRunner Support Team

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, December 23, 2005 11:29 AM
To: Lane Van Ingen
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE]

"Lane Van Ingen" <lvaningen(at)esncc(dot)com> writes:
> Can anyone advise how to do this? I want to be able to fire off a VACUUM
> <tbl>
> command at my request.

You can't issue the VACUUM from inside a function, period. It has to be
done from the client end, because it can't run inside a transaction.

regards, tom lane

-----Original Message-----
From: Sean Davis [mailto:sdavis2(at)mail(dot)nih(dot)gov]
Sent: Friday, December 23, 2005 11:20 AM
To: Lane Van Ingen; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE]

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