Re: COPY bug on 7.3.4 whe copying lots of data

"Mason" <mason(at)vanten(dot)com> writes:
> 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.

This is not a bug: neither the SQL standard nor Postgres make any
guarantees about the order of rows in a table.

> I'm primarily wondering if anyone could explain WHY this happens.

Probably a matter of where the free space happens to be. Once the
system runs out of free space internal to the table, it will
consistently append to the end, but as long as there is any free space
it will get used in a not-very-predictable order.

If you can TRUNCATE the table before you insert then you would get the
result you want, at least as long as no updates happen. But I think any
code that relies on row ordering for correctness is broken by
definition, and you'd be better off rethinking that assumption.

regards, tom lane