Skip site navigation (1) Skip section navigation (2)

From: "Lane Van Ingen" <lvaningen(at)esncc(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject:
Date: 2005-12-23 16:05:07
Message-ID: EKEMKEFLOMKDDLIALABIGEKFCGAA.lvaningen@esncc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?




Responses

  • 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 DavisDate: 2005-12-23 16:19:43
Subject: Re:
Previous:From: Mag GamDate: 2005-12-23 15:15:39
Subject: PostgreSQL Logging

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group