We have a strange issue going on with PostGres 7.3.4 on NetBSD. When we
have a COPY command in a script that will be importing a great deal of data
(on the order of 200Meg), the command does not always insert the data into
the table in the same order that is in the script. i.e. When you do a
SELECT * FROM table, or else a pg_dump, the data is all correctly there, and
most of it is in the same order as the original script, but ten or twenty
lines will be shifted randomly around.
This is an issue for us because we would like to have a utility to diff the
data in databases once a day or so by dumping their tables into individual
files and then making sure they contain everything that we used to build
them with.
I'm primarily wondering if anyone could explain WHY this happens. I don't
know the internals of Postgres, so the best I could come up with is that
there may be more than one thread inserting data and they are getting out of
synch in some way.
Can anyone put me on the right track?