From: sfc@tpghq.com (Steve Caswell)
Subject: Re: Full log file while doing deletes ?
Organization: Palmer & Associates, Inc.
Date: Fri, 21 Aug 1992 16:18:23 GMT


>From: dwolfe@halcon.lerc.nasa.gov (Dana Wolfe)
>Subject: Full log file while doing deletes ?
>Date: 20 Aug 92 14:20:18 GMT
>
>
	>I have several large tables (  > 60MB ) from which I would 
	>like to delete about 15% of the rows. I continue to get the log
	>file full error, and was wondering if there is a way aound this.
	>The manual suggests increasing the size of the log file but,
	>if I understand the instructions,  the size of the resulting log 
	>file seems unacceptable. 
>
	>Thanks. 

>-- 
>----------------------------------------------------------------------
>Dana Wolfe,  MS 142-1		voice: 	  (216) 433-8270
>NASA Lewis Research Center	fax:   	  (216) 433-8000 
>21000 Brookpark Rd		internet: dwolfe@halcon.lerc.nasa.gov
>Cleveland, OH  44135			   
>----------------------------------------------------------------------

I'm not sure why the the size of the resulting log file should be unacceptable.
Basically you can have a log file that is as large as your largest disk.
What storage structure is the table in?  You might try modifying the table to
isam to try to get INGRES to do clustered logging.  With clustered logging,
INGRES can write one before image of a page and then change records based on
that page.  If the table were hash, INGRES would need to write a before image
for every row being deleted.  This would require much more logging overhead
than if clustered logging were used.

If you are running INGRES 6.4, you can use the set session rollback command.
Normally, INGRES will only rollback a statement if that statement encounters
an error.  If you use the command 'set session with on_error = rollback
transaction', INGRES will rollback the entire transaction if any one statement
fails.  The byproduct of this is that INGRES can get away with less logging
because it only needs to keep enough log information to get back to the state
the table was in before the start of the transaction.

If these things do not work, you might try breaking up your delete into smaller
chunks.  For instance, if you need to delete all the rows with a key value
less than or equal to 10000, and the log file is not big enough for this to
be deleted in one statement, try breaking it up into several statements with
each of them further restricting the rows to be deleted.  Don't forget to
commit after each statement, or you will still have one large transaction and
you could run out of log file.

For example, rather than try to delete all the rows in one command:

delete from table where key<=10000

,try several statements such as:

delete from table where key<=2000;
commit;
delete from table where key>=2000 and <=4000;
commit;
delete from table where key>=4000 and <=6000;
commit;
...etc.

Finally, check to see if you have any rules defined on the table being deleted.
If there are rules that fire on delete, they may be changing other tables, which
will also require logging overhead.  If this is the case, consider disabling
the rules for your mass update and manually performing any actions the rules
were performing.

Hope this helps.


-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."
Newsgroups: comp.databases.ingres