Re: [HACKERS] strange behavior of UPDATE

> > > 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(dot)Mergl(at)bawue(dot)de
> Im Haldenhau 9 http://www.bawue.de/~mergl
> 70565 Stuttgart fon: +49 711 747503
> Germany
>

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