Out of memory while UPDATE some_table ... FROM some_view (8.0 beta5)
От | Robert Osowiecki |
---|---|
Тема | Out of memory while UPDATE some_table ... FROM some_view (8.0 beta5) |
Дата | |
Msg-id | 41AC6853.4020807@cavern.pl обсуждение исходный текст |
Ответы |
Re: Out of memory while UPDATE some_table ... FROM some_view (8.0 beta5)
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
While doing some large update on table with over 1 million records: update tordspecif set sp_vat=vv_vat, sp_vat_opis=vv_vat_opis, sp_ar_sww=vv_sww from varticlevat where sp_az_artsize=vv_artsize PostgreSQL 8.0.0beta5 on i686-pc-linux-gnu, compiled by GCC 2.96 reported an error: TopMemoryContext: 57344 total in 6 blocks; 8616 free (26 chunks); 48728 used TopTransactionContext: 8192 total in 1 blocks; 7856 free (4 chunks); 336 used specif_insert: 23552 total in 5 blocks; 3568 free (0 chunks); 19984 used SPI Plan: 1024 total in 1 blocks; 192 free (0 chunks); 832 used SPI Plan: 7168 total in 3 blocks; 2752 free (0 chunks); 4416 used SPI Plan: 1024 total in 1 blocks; 32 free (0 chunks); 992 used SPI Plan: 7168 total in 3 blocks; 896 free (0 chunks); 6272 used SPI Plan: 7168 total in 3 blocks; 3720 free (0 chunks); 3448 used SPI Plan: 1024 total in 1 blocks; 192 free (0 chunks); 832 used SPI Plan: 1024 total in 1 blocks; 176 free (0 chunks); 848 used SPI Plan: 3072 total in 2 blocks; 1760 free (0 chunks); 1312 used SPI Plan: 31744 total in 6 blocks; 7152 free (0 chunks); 24592 used SPI Plan: 1024 total in 1 blocks; 32 free (0 chunks); 992 used SPI Plan: 7168 total in 3 blocks; 1216 free (0 chunks); 5952 used SPI Plan: 7168 total in 3 blocks; 3720 free (0 chunks); 3448 used SPI Plan: 1024 total in 1 blocks; 176 free (0 chunks); 848 used SPI Plan: 3072 total in 2 blocks; 1952 free (0 chunks); 1120 used SPI Plan: 7168 total in 3 blocks; 3568 free (0 chunks); 3600 used SPI Plan: 7168 total in 3 blocks; 3568 free (0 chunks); 3600 used SPI Plan: 7168 total in 3 blocks; 3568 free (0 chunks); 3600 used SPI Plan: 7168 total in 3 blocks; 216 free (0 chunks); 6952 used SPI Plan: 3072 total in 2 blocks; 2016 free (1 chunks); 1056 used SPI Plan: 7168 total in 3 blocks; 3936 free (0 chunks); 3232 used SPI Plan: 7168 total in 3 blocks; 3936 free (0 chunks); 3232 used SPI Plan: 7168 total in 3 blocks; 3936 free (0 chunks); 3232 used SPI Plan: 3072 total in 2 blocks; 1296 free (0 chunks); 1776 used SPI Plan: 3072 total in 2 blocks; 1296 free (0 chunks); 1776 used SPI Plan: 3072 total in 2 blocks; 1296 free (0 chunks); 1776 used SPI Plan: 7168 total in 3 blocks; 848 free (0 chunks); 6320 used SPI Plan: 1024 total in 1 blocks; 176 free (0 chunks); 848 used SPI Plan: 1024 total in 1 blocks; 32 free (0 chunks); 992 used SPI Plan: 15360 total in 4 blocks; 8112 free (0 chunks); 7248 used SPI Plan: 1024 total in 1 blocks; 192 free (0 chunks); 832 used MessageContext: 277944 total in 6 blocks; 111192 free (5 chunks); 166752 used PortalMemory: 8192 total in 1 blocks; 8040 free (2 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks); 128 used ExecutorState: 65592 total in 4 blocks; 25832 free (1 chunks); 39760 used HashTableContext: 8192 total in 1 blocks; 8112 free (0 chunks); 80 used HashBatchContext: 8192 total in 1 blocks; 128 free (3 chunks); 8064 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 360701952 total in 52 blocks; 7158680 free (140 chunks); 353543272 used ExecutorState: 8192 total in 1 blocks; 6632 free (1 chunks); 1560 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used (repeated several times) CacheMemoryContext: 2088960 total in 8 blocks; 1028592 free (1 chunks); 1060368 used (lots of indicies) MdSmgr: 8192 total in 1 blocks; 5536 free (0 chunks); 2656 used DynaHash: 8192 total in 1 blocks; 6184 free (0 chunks); 2008 used Prepared Queries: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used RI query cache: 24576 total in 2 blocks; 14280 free (7 chunks); 10296 used PLpgSQL function cache: 24576 total in 2 blocks; 14280 free (7 chunks); 10296 used Type information cache: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used Operator class cache: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used CFuncHash: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used smgr relation table: 8192 total in 1 blocks; 952 free (0 chunks); 7240 used Portal hash: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used Relcache by OID: 8192 total in 1 blocks; 1960 free (0 chunks); 6232 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: 16384 total in 2 blocks; 16352 free (21 chunks); 32 used <11109>ERROR: out of memory <11109>DETAIL: Failed on request of size 96. Query is EXPLAIN-ed as follows: Hash Join (cost=6997.64..169707.66 rows=1339172 width=279) Hash Cond: ("outer".sp_az_artsize = "inner".az_artsize) InitPlan -> Seq Scan on tsystemvar (cost=0.00..2.15 rows=1 width=4) Filter: ((sv_name)::text = 'CURRENT_SEASON'::text) -> Seq Scan on tordspecif (cost=0.00..38621.72 rows=1339172 width=245) -> Hash (cost=6512.77..6512.77 rows=44688 width=38) -> Hash Left Join (cost=1950.38..6512.77 rows=44688 width=38) Hash Cond: ("outer".az_artsize = "inner".ap_az_artsize) -> Hash Join (cost=1210.12..4390.20 rows=44688 width=18) Hash Cond: (("outer".az_ar_code)::text = ("inner".ar_code)::text) -> Seq Scan on tarticlesize (cost=0.00..1471.88 rows=44688 width=16) -> Hash (cost=1061.30..1061.30 rows=15930 width=27) -> Seq Scan on tarticle (cost=0.00..1061.30 rows=15930 width=27) -> Hash (cost=638.50..638.50 rows=11500 width=24) -> Seq Scan on tartpricevat (cost=0.00..638.50 rows=11500 width=24) Filter: ((ap_deleted = 0) AND (ap_se_code = $0)) EXPLAIN ANALYSE produces similar error (stats collector/query optimizer bug?) The database itself is fresh (populated by psql from pg_dumpall) Please send any suggestions on how could I investigate this problem further. Best regards. Robert Osowiecki
В списке pgsql-bugs по дате отправления: