Обсуждение: [8.0.0] out of memory on large UPDATE

Поиск
Список
Период
Сортировка

[8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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

Re: [8.0.0] out of memory on large UPDATE

От
Tom Lane
Дата:
"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

Re: [8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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

Re: [8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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

Re: [8.0.0] out of memory on large UPDATE

От
Tom Lane
Дата:
"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

Re: [8.0.0] out of memory on large UPDATE

От
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

Re: [8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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

Re: [8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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

Re: [8.0.0] out of memory on large UPDATE

От
Tom Lane
Дата:
"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

Re: [8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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

Re: [8.0.0] out of memory on large UPDATE

От
Tom Lane
Дата:
"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

Re: [8.0.0] out of memory on large UPDATE

От
"Marc G. Fournier"
Дата:
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