Re: [HACKERS] strange behavior of UPDATE - Mailing list pgsql-hackers

> > > The setup is pretty simple: one table with 13
> > > integer and 7 char(20) columns. For every column
> > > an index is created. The postmaster is started with
> > > -o -F and before each query a 'vacuum analyze' is
> > > performed.


Yes, this is what I wanted.  Does the test use the DEFAULT clause.  If
so, I may have just fixed the problem.  If not, it may be another
problem with char() length not being padded properly.


> > >
> > > When loading 100.000 rows into the table
> > > everything works ok. Selects and updates
> > > are reasonable fast. But when loading
> > > 1.000.000 rows the select statements still
> > > work, but a simple update statement
> > > shows this strange behavior. A never ending
> > > disk-activity starts. Memory consumption
> > > increases up to the physical limit (384 MB)
> > > whereas the postmaster uses only a few %
> > > of CPU time. After 1 hour I killed the post-
> > > master.
> > >
> > > It would be nice, if this could be fixed.
> > > People from the german UNIX magazine IX
> > > benchmarked Oracle, Informix and Sybase on Linux
> > > and they claimed, that Postgres is totally unusable
> > > because of this problem.
> > >
> > > If you need some additional info, just let me know.
> > >
> > >
> > > Edmund
> 
> 
> I can attach to the backend and print a backtrace. 
> Is this what you expect ?
> 
> 
> Edmund
> 
> 
> (gdb) bt
> #0  0x40186534 in __libc_read ()
> #1  0x360 in ?? ()
> #2  0x80de019 in mdread (reln=0x8222790, blocknum=1671, 
>     buffer=0x40215c40 "_\a\024\b_\037") at md.c:413
> #3  0x80dead5 in smgrread (which=0, reln=0x8222790, blocknum=1671, 
>     buffer=0x40215c40 "_\a\024\b_\037") at smgr.c:231
> #4  0x80d5863 in ReadBufferWithBufferLock (reln=0x8222790, blockNum=1671, 
>     bufferLockHeld=0) at bufmgr.c:292
> #5  0x80d5758 in ReadBuffer (reln=0x8222790, blockNum=1671) at bufmgr.c:170
> #6  0x8073153 in _bt_getbuf (rel=0x8222790, blkno=1671, access=0)
>     at nbtpage.c:337
> #7  0x8074659 in _bt_searchr (rel=0x8222790, keysz=1, scankey=0x8236470, 
>     bufP=0xbfffa36c, stack_in=0x849e498) at nbtsearch.c:116
> #8  0x8074547 in _bt_search (rel=0x8222790, keysz=1, scankey=0x8236470, 
>     bufP=0xbfffa36c) at nbtsearch.c:52
> #9  0x8070d31 in _bt_doinsert (rel=0x8222790, btitem=0x849e468, 
>     index_is_unique=0 '\000', heapRel=0x8218c40) at nbtinsert.c:65
> #10 0x8073aea in btinsert (rel=0x8222790, datum=0x849e420, 
>     nulls=0x849e408 " ", ht_ctid=0x82367d4, heapRel=0x8218c40) at nbtree.c:369
> #11 0x8109a13 in fmgr_c (finfo=0xbfffa40c, values=0xbfffa41c, 
>     isNull=0xbfffa40b "") at fmgr.c:154
> #12 0x8109cb7 in fmgr (procedureId=331) at fmgr.c:338
> #13 0x806d540 in index_insert (relation=0x8222790, datum=0x849e420, 
>     nulls=0x849e408 " ", heap_t_ctid=0x82367d4, heapRel=0x8218c40)
>     at indexam.c:190
> #14 0x80953a2 in ExecInsertIndexTuples (slot=0x8233368, tupleid=0x82367d4, 
>     estate=0x8231740, is_update=1) at execUtils.c:1210
> #15 0x809293c in ExecReplace (slot=0x8233368, tupleid=0xbfffa540, 
>     estate=0x8231740) at execMain.c:1472
> #16 0x809255e in ExecutePlan (estate=0x8231740, plan=0x8231280, 
>     operation=CMD_UPDATE, offsetTuples=0, numberTuples=0, 
>     direction=ForwardScanDirection, destfunc=0x8236350) at execMain.c:1086
> #17 0x8091cae in ExecutorRun (queryDesc=0x8231728, estate=0x8231740, 
>     feature=3, limoffset=0x0, limcount=0x0) at execMain.c:359
> #18 0x80e1098 in ProcessQueryDesc (queryDesc=0x8231728, limoffset=0x0, 
>     limcount=0x0) at pquery.c:333
> #19 0x80e10fe in ProcessQuery (parsetree=0x8220998, plan=0x8231280, 
>     dest=Remote) at pquery.c:376
> #20 0x80dfbb6 in pg_exec_query_dest (
>     query_string=0xbfffa67c "update bench set k500k = k500k + 1 where k100 = 30;
> ", dest=Remote, aclOverride=0) at postgres.c:742
> #21 0x80dfab7 in pg_exec_query (
>     query_string=0xbfffa67c "update bench set k500k = k500k + 1 where k100 = 30;
> ") at postgres.c:642
> #22 0x80e0abc in PostgresMain (argc=6, argv=0xbfffe704, real_argc=6, 
>     real_argv=0xbffffcf4) at postgres.c:1610
> ---Type <return> to continue, or q <return> to quit---
> #23 0x80cacaa in DoBackend (port=0x81c3c38) at postmaster.c:1584
> #24 0x80ca77a in BackendStartup (port=0x81c3c38) at postmaster.c:1351
> #25 0x80c9ec9 in ServerLoop () at postmaster.c:802
> #26 0x80c9a0e in PostmasterMain (argc=6, argv=0xbffffcf4) at postmaster.c:596
> #27 0x80a1836 in main (argc=6, argv=0xbffffcf4) at main.c:97
> #28 0x400fbcb3 in __libc_start_main (main=0x80a17d0 <main>, argc=6, 
>     argv=0xbffffcf4, init=0x8061878 <_init>, fini=0x810f13c <_fini>, 
>     rtld_fini=0x4000a350 <_dl_fini>, stack_end=0xbffffcec)
>     at ../sysdeps/generic/libc-start.c:78
> 
> 
> 
> 
> 
> 
> -- 
> Edmund Mergl          mailto:E.Mergl@bawue.de
> Im Haldenhau 9        http://www.bawue.de/~mergl
> 70565 Stuttgart       fon: +49 711 747503
> Germany
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026