Re: Commit every N rows in PL/pgsql

> > I need to populate a new column in a Postgres 8.3 table. The SQL would be
> something like "update t set col_c = col_a + col_b". Unfortunately, this
> table has 110 million rows, so running that query runs out of memory.
>
> That's unusual, what is the error you get?

Here it is:
TopMemoryContext: 57608 total in 7 blocks; 4072 free (6 chunks); 53536 used
RI compare cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
RI query cache: 24576 total in 2 blocks; 14136 free (5 chunks); 10440 used
TopTransactionContext: 57344 total in 3 blocks; 10088 free (8 chunks);
47256 used
AfterTriggerEvents: 2642403328 total in 327 blocks; 10176 free (319
chunks); 2642393152 used
Combo CIDs: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344
used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks);
10504 used
MessageContext: 32768 total in 3 blocks; 11688 free (5 chunks); 21080 used
smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416
used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks);
16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 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: 57344 total in 3 blocks; 24384 free (21 chunks); 32960
used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
CacheMemoryContext: 2283848 total in 25 blocks; 986328 free (93 chunks);
1297520 used
t_idx_3: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
t_idx_2: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
t_idx_1: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
t_idx_0: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
t_fromuid: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
t_exportid: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
t_callid_hashtext: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
t_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
CachedPlan: 3072 total in 2 blocks; 1512 free (0 chunks); 1560 used
CachedPlanSource: 3072 total in 2 blocks; 1672 free (1 chunks); 1400
used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
company_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
CachedPlan: 3072 total in 2 blocks; 1512 free (0 chunks); 1560 used
CachedPlanSource: 3072 total in 2 blocks; 1672 free (1 chunks); 1400
used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
timezone_code_key: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
timezone_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_trigger_tgconstrname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_trigger_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
timezoneregion_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_toast_113593282_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_constraint_contypid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_toast_113593269_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_attrdef_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_shdepend_depender_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_roles: 7168 total in 3 blocks; 2824 free (0 chunks); 4344 used
globalconfig_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
schemaversion_pkey: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks);
936 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_constraint_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
MdSmgr: 8192 total in 1 blocks; 5872 free (0 chunks); 2320 used
LOCALLOCK hash: 8192 total in 1 blocks; 1856 free (0 chunks); 6336 used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
2010-05-20 22:53:40 EST ERROR: out of memory
2010-05-20 22:53:40 EST DETAIL: Failed on request of size 32.
2010-05-20 22:53:40 EST STATEMENT: update t
set col_c = col_a + col_c;

> Your table will grow (on disk) to twice the size it had previously, as new
> rows will be created for the transaction you're running the update from, but
> it will shrink again with vacuuming and usage. So you may run out of disk
> space, but /never/ out of memory. If you do then you probably have
> configured Postgres to use more memory than you have.
>

If it does grow to double on disk, that would be a problem. The table is
40GB and there's 40GB free on the disk... From the error, I thought it was a
memory problem though.

> Nested transactions wouldn't solve the problem, as the rows you "commit"
> here still aren't allowed to be visible to other transactions and so both
> versions of the rows need to be kept around until the outer transaction
> commits. It's not going to save you any space.
>

I think that Oracle stored procedures aren't wrapped in an implicit
transaction the way they are in Postgres. We ended up solving the problem
outside Postgres, but it's still an interesting problem :-)

> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1081,4bfce25910411232819391!
>
>
>

--
len(dot)walter(at)gmail(dot)com skype:lenwalter
msn:len(dot)walter(at)gmail(dot)com<msn%3Alen(dot)walter(at)gmail(dot)com>