Thread: [8.0.0] out of memory on large UPDATE
The table contains ~10 million rows: # time psql -c "UPDATE xa_url SET url = url;" -U pgsql pareto ERROR: out of memory DETAIL: Failed on request of size 32. 0.000u 0.022s 2:41:14.76 0.0% 88+66k 12+0io 19pf+0w And the server is running: PostgreSQL 8.0.0 on i386-portbld-freebsd4.10, compiled by GCC 2.95.4 I haven't had a chance to upgrade it to 8.0.3 yet ... didn't realize we had any limits on stuff like this ... bug, or really a limit? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes: > The table contains ~10 million rows: > # time psql -c "UPDATE xa_url SET url = url;" -U pgsql pareto > ERROR: out of memory > DETAIL: Failed on request of size 32. If you've got any AFTER UPDATE triggers on that table, you could be running out of memory for the pending-triggers list. regards, tom lane
On Thu, 11 Aug 2005, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> The table contains ~10 million rows: > >> # time psql -c "UPDATE xa_url SET url = url;" -U pgsql pareto >> ERROR: out of memory >> DETAIL: Failed on request of size 32. > > If you've got any AFTER UPDATE triggers on that table, you could be > running out of memory for the pending-triggers list. Nope, only have a BEFORE UPDATE, or would that be similar except for at which point it runs out of memory? Triggers: xa_url_domain_b_i_u BEFORE INSERT OR UPDATE ON xa_url FOR EACH ROW EXECUTE PROCEDURE xa_url_domain() ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Thu, 11 Aug 2005, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> On Thu, 11 Aug 2005, Tom Lane wrote: >>> If you've got any AFTER UPDATE triggers on that table, you could be >>> running out of memory for the pending-triggers list. > >> Nope, only have a BEFORE UPDATE, or would that be similar except for at >> which point it runs out of memory? > > Nope, BEFORE UPDATE shouldn't result in any permanent memory > accumulation. > > An out-of-memory error should result in a long report in the postmaster > log about how many bytes in each memory context --- can you post that? This is all I'm seeing in the logs: # grep "\[653\]" pgsql Aug 11 08:45:47 pgsql80 pg[653]: [34-1] ERROR: out of memory Aug 11 08:45:47 pgsql80 pg[653]: [34-2] DETAIL: Failed on request of size 32. ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes: > On Thu, 11 Aug 2005, Tom Lane wrote: >> If you've got any AFTER UPDATE triggers on that table, you could be >> running out of memory for the pending-triggers list. > Nope, only have a BEFORE UPDATE, or would that be similar except for at > which point it runs out of memory? Nope, BEFORE UPDATE shouldn't result in any permanent memory accumulation. An out-of-memory error should result in a long report in the postmaster log about how many bytes in each memory context --- can you post that? regards, tom lane
"Marc G. Fournier" <scrappy@postgresql.org> writes: > On Thu, 11 Aug 2005, Tom Lane wrote: >> An out-of-memory error should result in a long report in the postmaster >> log about how many bytes in each memory context --- can you post that? > This is all I'm seeing in the logs: > # grep "\[653\]" pgsql > Aug 11 08:45:47 pgsql80 pg[653]: [34-1] ERROR: out of memory > Aug 11 08:45:47 pgsql80 pg[653]: [34-2] DETAIL: Failed on request of size 32. (looks at code...) Hmm, it seems to print the report on stderr. I imagine we did that because there's possibly not enough memory to use elog. Anyway, can you arrange to capture the postmaster's stderr and try it again? regards, tom lane
On Thu, 11 Aug 2005, Marc G. Fournier wrote: > On Thu, 11 Aug 2005, Tom Lane wrote: > >> "Marc G. Fournier" <scrappy@postgresql.org> writes: >>> On Thu, 11 Aug 2005, Tom Lane wrote: >>>> If you've got any AFTER UPDATE triggers on that table, you could be >>>> running out of memory for the pending-triggers list. >> >>> Nope, only have a BEFORE UPDATE, or would that be similar except for at >>> which point it runs out of memory? >> >> Nope, BEFORE UPDATE shouldn't result in any permanent memory >> accumulation. >> >> An out-of-memory error should result in a long report in the postmaster >> log about how many bytes in each memory context --- can you post that? > > This is all I'm seeing in the logs: > > # grep "\[653\]" pgsql > Aug 11 08:45:47 pgsql80 pg[653]: [34-1] ERROR: out of memory > Aug 11 08:45:47 pgsql80 pg[653]: [34-2] DETAIL: Failed on request of size > 32. 'k, does this help any? TopMemoryContext: 40960 total in 4 blocks; 8632 free (10 chunks); 32328 used SPI Plan: 3072 total in 2 blocks; 1728 free (0 chunks); 1344 used TopTransactionContext: 534765568 total in 74 blocks; 2144 free (68 chunks); 534763424 used ExecutorState: 8192 total in 1 blocks; 7648 free (3 chunks); 544 used MessageContext: 57344 total in 3 blocks; 30312 free (11 chunks); 27032 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks); 128 used ExecutorState: 122880 total in 4 blocks; 98912 free (127 chunks); 23968 used ExprContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used CacheMemoryContext: 516096 total in 6 blocks; 134272 free (3 chunks); 381824 used xa_url_tuid_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used xa_url_spiderlite_signature_check_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used xa_url_priority_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used urls_status: 1024 total in 1 blocks; 912 free (0 chunks); 112 used urls_logger_status: 1024 total in 1 blocks; 912 free (0 chunks); 112 used url_url: 1024 total in 1 blocks; 912 free (0 chunks); 112 used xa_url_id_pk: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_index_indrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_type_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_shadow_usesysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_proc_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 712 free (0 chunks); 312 used pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 712 free (0 chunks); 312 used pg_namespace_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_namespace_nspname_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_language_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_language_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_group_sysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_group_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_conversion_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_conversion_default_index: 1024 total in 1 blocks; 712 free (0 chunks); 312 used pg_opclass_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_cast_source_target_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_amop_opr_opc_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_shadow_usename_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_operator_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_amproc_opc_proc_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_amop_opc_strat_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_index_indexrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_class_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_amproc_opc_proc_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_amop_opc_strat_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used MdSmgr: 8192 total in 1 blocks; 5968 free (0 chunks); 2224 used DynaHash: 8192 total in 1 blocks; 6088 free (0 chunks); 2104 used Type information cache: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used PLpgSQL function cache: 24576 total in 2 blocks; 14280 free (7 chunks); 10296 used CFuncHash: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used Operator class cache: 8192 totalin 1 blocks; 5080 free (0 chunks); 3112 used smgr relation table: 8192 total in 1 blocks; 1984 free (0 chunks); 6208 used Portal hash: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used Relcache by OID: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used Relcache by name: 24576 total in 2 blocks; 13240 free (5 chunks); 11336 used LockTable (locallock hash): 8192 total in 1 blocks; 4056 free (0 chunks); 4136 used ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Just as a reminder, this is an 8.0.0 install, so if you think this might have been fixed in later sub-releases, plesae let me know and I'll upgrade/test again ... On Thu, 11 Aug 2005, Marc G. Fournier wrote: > On Thu, 11 Aug 2005, Marc G. Fournier wrote: > >> On Thu, 11 Aug 2005, Tom Lane wrote: >> >>> "Marc G. Fournier" <scrappy@postgresql.org> writes: >>>> On Thu, 11 Aug 2005, Tom Lane wrote: >>>>> If you've got any AFTER UPDATE triggers on that table, you could be >>>>> running out of memory for the pending-triggers list. >>> >>>> Nope, only have a BEFORE UPDATE, or would that be similar except for at >>>> which point it runs out of memory? >>> >>> Nope, BEFORE UPDATE shouldn't result in any permanent memory >>> accumulation. >>> >>> An out-of-memory error should result in a long report in the postmaster >>> log about how many bytes in each memory context --- can you post that? >> >> This is all I'm seeing in the logs: >> >> # grep "\[653\]" pgsql >> Aug 11 08:45:47 pgsql80 pg[653]: [34-1] ERROR: out of memory >> Aug 11 08:45:47 pgsql80 pg[653]: [34-2] DETAIL: Failed on request of size >> 32. > > 'k, does this help any? > > TopMemoryContext: 40960 total in 4 blocks; 8632 free (10 chunks); 32328 used > SPI Plan: 3072 total in 2 blocks; 1728 free (0 chunks); 1344 used > TopTransactionContext: 534765568 total in 74 blocks; 2144 free (68 chunks); > 534763424 used > ExecutorState: 8192 total in 1 blocks; 7648 free (3 chunks); 544 used > MessageContext: 57344 total in 3 blocks; 30312 free (11 chunks); 27032 used > PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used > PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks); 128 used > ExecutorState: 122880 total in 4 blocks; 98912 free (127 chunks); 23968 used > ExprContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > CacheMemoryContext: 516096 total in 6 blocks; 134272 free (3 chunks); 381824 > used > xa_url_tuid_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > xa_url_spiderlite_signature_check_idx: 1024 total in 1 blocks; 912 free (0 > chunks); 112 used > xa_url_priority_idx: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > urls_status: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > urls_logger_status: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > url_url: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > xa_url_id_pk: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_index_indrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_type_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_type_typname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 > used > pg_statistic_relid_att_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_shadow_usesysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_proc_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 712 free (0 chunks); > 312 used > pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 712 free (0 chunks); > 312 used > pg_namespace_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_namespace_nspname_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_language_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_language_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_group_sysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_group_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_conversion_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_conversion_name_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_conversion_default_index: 1024 total in 1 blocks; 712 free (0 chunks); 312 > used > pg_opclass_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 776 free (0 chunks); > 248 used > pg_cast_source_target_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 > used > pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_amop_opr_opc_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used > pg_aggregate_fnoid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_shadow_usename_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_operator_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_amproc_opc_proc_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 > used > pg_amop_opc_strat_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 > used > pg_index_indexrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 > used > pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 848 free (0 chunks); > 176 used > pg_class_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used > pg_amproc_opc_proc_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 > used > pg_amop_opc_strat_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 > used > pg_class_relname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 > used > MdSmgr: 8192 total in 1 blocks; 5968 free (0 chunks); 2224 used > DynaHash: 8192 total in 1 blocks; 6088 free (0 chunks); 2104 used > Type information cache: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 > used > PLpgSQL function cache: 24576 total in 2 blocks; 14280 free (7 chunks); 10296 > used > CFuncHash: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used > Operator class cache: 8192 totalin 1 blocks; 5080 free (0 chunks); 3112 used > smgr relation table: 8192 total in 1 blocks; 1984 free (0 chunks); 6208 used > Portal hash: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used > Relcache by OID: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used > Relcache by name: 24576 total in 2 blocks; 13240 free (5 chunks); 11336 used > LockTable (locallock hash): 8192 total in 1 blocks; 4056 free (0 chunks); > 4136 used > ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used > > ---- > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes: > 'k, does this help any? > TopMemoryContext: 40960 total in 4 blocks; 8632 free (10 chunks); 32328 used > SPI Plan: 3072 total in 2 blocks; 1728 free (0 chunks); 1344 used > TopTransactionContext: 534765568 total in 74 blocks; 2144 free (68 chunks); 534763424 used Yeah, the leak is clearly in TopTransactionContext. That doesn't let the trigger code off the hook though, because the pending-triggers list is kept there. Are you *sure* there are no AFTER triggers here? (Don't forget foreign-key checking triggers.) regards, tom lane
On Thu, 11 Aug 2005, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> 'k, does this help any? > >> TopMemoryContext: 40960 total in 4 blocks; 8632 free (10 chunks); 32328 used >> SPI Plan: 3072 total in 2 blocks; 1728 free (0 chunks); 1344 used >> TopTransactionContext: 534765568 total in 74 blocks; 2144 free (68 chunks); 534763424 used > > Yeah, the leak is clearly in TopTransactionContext. That doesn't let > the trigger code off the hook though, because the pending-triggers list > is kept there. 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 ON DELETERESTRICT "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 DELETE SETNULL Triggers: xa_url_domain_b_i_u BEFORE INSERT OR UPDATE ON xa_url FOR EACH ROW EXECUTE PROCEDURE xa_url_domain() ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"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. Can you afford to drop the FK constraints while you do the update? I can't think of any other short-term workaround. regards, tom lane
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