bulk copy fills transaction log

[robert.littlejohn]

I have Ingres ver3.0.1 rel109 installed on a Red Hat Advanced v3 server
and am have some trouble using the copy command to migrate data from a
table in Ingres II 2.6/0207 (usl.us5/00) on Unix.

The btree table copies out fine (binary format) and has approx. 22 million
rows. The copy in on the Unix system works but on Linux my 400 Meg
transaction log fills instantly. I've made sure journaling is turned off
but still the log fills. Any help would be appreciated.

Archived post - originally posted at 2005-01-31 05:50:20

[kschendel]

Well, the general rule is, if any appreciable amount of transaction log is
being used, it's NOT a bulk copy.
Things to check:
- is the table you are copying into a heap?
- if it's not a heap, is it empty and no more than 18 pages in size?
- have you verified that there are no secondary indexes (including indexes
created by constraints)?
- is journaling off (you've already checked this)
- in r3, the table must not be partitioned.
(We'll implement bulk-load of partitioned tables at some point, but other
things have taken priority.)

If you can't figure it out, and if nobody else is using the database, and
if you're willing to trash the table if something goes wrong, then you can
turn on "set nologging" and do the copy. Keep in mind that if anything -
Anything - goes wrong during a nologging update, the database is marked
inconsistent. Don't use nologging unless you know what your recovery plan
is going to be.

Archived post - originally posted at 2005-01-28 14:37:18

[kschendel]

No, multiple locations is fine.
I mean that the table must not be partitioned with the PARTITION= clause
of the CREATE or MODIFY statement. (A new r3 feature.)

Archived post - originally posted at 2005-01-31 05:50:20

[robert.littlejohn]

Thanks for your reply - I'm checking now. When you say the table must not
be partitioned - do you mean it can't be spread across mulitple locations?

Archived post - originally posted at 2005-01-28 14:52:47