I've got a rather simple load script that makes use of the COPY command to
get some data into a DB. All good.
I wrote an equally simple "reload" script that for one table simply does a
DELETE and then calls the load script. The 2 must happen in the same
transaction since there is a DEFERRED cascade delete on a FK that I do not
want triggered. (I.e., the reload mostly recreates records with the same
PKs, though with changes to other columns.) At the end of the reload the
table in question is empty! Mid-way through (before commit) a SELECT
count(*) on the table reports the expected number of rows. No
error/exception occurs during the procedure. NOTE: I tried adding an oid
column to the table and that did not fix the problem.
[ Another, secondary, thing is that this process is terribly slow ( 90
seconds for 800 rows of data !). Yes, there are some indices on the table,
but I figured COPY might be smart and have indices recomputed in bulk not
per-row ?? -- and, dropping then re-applying the indices doesn't speed
things up very much: the blockage again seems to be the delete and insert in
the same x-action. ]
Any clues?
Thx,
== Ezra Epstein