Re: [8.0.0] out of memory on large UPDATE

On Thu, 11 Aug 2005, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> On Thu, 11 Aug 2005, Tom Lane wrote:
>>> Are you *sure* there are no AFTER triggers here?
>>> (Don't forget foreign-key checking triggers.)
>
>> This is all of them ... nothing AFTER, just ON or BEFORE ...
>
>> Foreign-key constraints:
>>      "xa_classification_id_fk" FOREIGN KEY (classification_id) REFERENCES xa_classification(classification_id) ON
UPDATERESTRICT ON DELETE RESTRICT 
>>      "xa_ip_address_id_fk" FOREIGN KEY (ip_address_id) REFERENCES xa_ip_addresses(ip_address_id) ON UPDATE RESTRICT
ONDELETE RESTRICT 
>>      "xa_logger_status_id_fk" FOREIGN KEY (logger_status_id) REFERENCES xa_logger_status(logger_status_id) ON UPDATE
RESTRICTON DELETE RESTRICT 
>>      "xa_url_queue_id_fk" FOREIGN KEY (url_queue_id) REFERENCES xa_url_queue(url_queue_id) ON UPDATE RESTRICT ON
DELETESET NULL 
>> Triggers:
>>      xa_url_domain_b_i_u BEFORE INSERT OR UPDATE ON xa_url FOR EACH ROW EXECUTE PROCEDURE xa_url_domain()
>
> Um, foreign-key triggers are always AFTER.

Ah, k ... that would actually make sense had I thought of it too :(

> Can you afford to drop the FK constraints while you do the update?  I
> can't think of any other short-term workaround.

Not sure, but is there a way to do so temporarily?

DarcyB and I were talking the other day about how slow things where for
that UPDATE ... I figured alot of the cause was the UPDATEng of the
INDICES at the same time, so he suggested doing something they are
apparenty looking for with Slony, and "temporarily disabling" the indices
inside a transaction, and then REINDEXng at the end ... ie.

BEGIN;
UPDATE pg_catalog.pg_class
    SET relhasindex = 'f'
  WHERE pg_catalog.pg_class.oid= 'tableoid';
<perform update here>
UPDATE pg_catalog.pg_class
    SET relhasindex = 't'
  WHERE pg_catalog.pg_class.oid= 'tableoid';
REINDEX;
END;

Could I do similar setting "relfkeys = 'f'"?  Or is it more complicated
then that?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664