Обсуждение: strange behavior of UPDATE

Hi,

recently I tried to reproduce some benchmark results
when I discovered a very strange behavior. I did
my tests with the current snapshot of last week,
but other people who have performed the same bench-
mark with postgresql-6.4-2 reported the same problems.

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.

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


-- 
Edmund Mergl          mailto:E.Mergl@bawue.de
Im Haldenhau 9        http://www.bawue.de/~mergl
70565 Stuttgart       fon: +49 711 747503
Germany

Edmund Mergl <E.Mergl@bawue.de> writes:
> 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.

Can you provide a script or something to reproduce this behavior?

There are a number of people using Postgres with large databases
and not reporting any such problem, so I think there has to be some
special triggering condition; it's not just a matter of things
breaking at a million rows.  Before digging into it, I'd like to
eliminate variables like whether I have the right test case.
        regards, tom lane

OK, can you attach to the running process and tell us what functions it
is running.  That would help.


[Charset iso-8859-2 unsupported, filtering to ASCII...]
> Hi,
> 
> recently I tried to reproduce some benchmark results
> when I discovered a very strange behavior. I did
> my tests with the current snapshot of last week,
> but other people who have performed the same bench-
> mark with postgresql-6.4-2 reported the same problems.
> 
> 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.
> 
> 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
> 
> 
> -- 
> 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
 

Tom Lane wrote:
>
> Edmund Mergl <E.Mergl@bawue.de> writes:
> > 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.
>
> Can you provide a script or something to reproduce this behavior?
>
> There are a number of people using Postgres with large databases
> and not reporting any such problem, so I think there has to be some
> special triggering condition; it's not just a matter of things
> breaking at a million rows.  Before digging into it, I'd like to
> eliminate variables like whether I have the right test case.
>
>                         regards, tom lane


the original benchmark can be found at

   ftp://ftp.heise.de/pub/ix/benches/sqlb-21.tar

for a stripped-down version see the attachment.
For loading the database and running the first
and second part (selects and updates) just do
the following:

  createdb test
  ./make_wnt 1000000 pgsql >make.out 2>&1 &

This needs about 700 MB of diskspace.
On a PII-400 it takes about 40 minutes to
load the database, 20 minutes to create the indeces
and 20 minutes to run the first part of the
benchmark (make_sqs). For running the benchmark
in 20 minutes (without swapping) one needs 384 MB RAM.

The second part (make_nqs) contains update
statements which can not be performed properly
using PostgreSQL.

For testing it is sufficient to initialize the
database and then to perform a query like

   update bench set k500k = k500k + 1 where k100 = 30


Edmund

--
Edmund Mergl          mailto:E.Mergl@bawue.de
Im Haldenhau 9        http://www.bawue.de/~mergl
70565 Stuttgart       fon: +49 711 747503
Germany

Bruce Momjian wrote:
> 
> OK, can you attach to the running process and tell us what functions it
> is running.  That would help.
> 
> [Charset iso-8859-2 unsupported, filtering to ASCII...]
> > Hi,
> >
> > recently I tried to reproduce some benchmark results
> > when I discovered a very strange behavior. I did
> > my tests with the current snapshot of last week,
> > but other people who have performed the same bench-
> > mark with postgresql-6.4-2 reported the same problems.
> >
> > 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.
> >
> > 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

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

Edmund,

Here is what I got running that test on DUAL PII 350Mhz, 256 RAM,
FreeBSD-3.1 elf release, current 6.5 cvs:

Start of inserting 1000000 rows:       SB  22 MAJ 1999 13:14:58 MSD
Start of indexing 1000000 rows:        SB  22 MAJ 1999 14:47:06 MSD
Start of SetQuery single user:    SB  22 MAJ 1999 18:18:05 MSD
Start of NewQuery single user:    SB  22 MAJ 1999 19:38:06 MSD
End of iX SQLBench 2.1:      WS  23 MAJ 1999 11:05:25 MSD

It was so slow, does FreeBSD has slow IO operation or it's swapping 
problem ? 
SB  22 MAJ 1999 22:07:26 MSD
Q8A
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is impossible. Terminating.
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is impossible. Terminating.
WS  23 MAJ 1999 11:05:24 MSD
Q8B
Connection to database 'test' failed.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I did upgrade of postgres and accidentally stopped testing - I didn't expect
it's still running !

After reinstalling of postgres I did 
test=> select count(*) from bench;
anf it takes forever ! Anybody tried that test ?
Here is a bt from gdb:
0x8078b8d in TransactionIdDidAbort ()
(gdb) bt 
#0  0x8078b8d in TransactionIdDidAbort ()
#1  0x806b460 in heapgettup ()
#2  0x806bf3f in heap_getnext ()
#3  0x809c816 in SeqNext ()
#4  0x8097609 in ExecScan ()
#5  0x809c8cb in ExecSeqScan ()
#6  0x8095c56 in ExecProcNode ()
#7  0x80993dd in ExecAgg ()
#8  0x8095cd6 in ExecProcNode ()
#9  0x8094c10 in ExecutePlan ()
#10 0x809450b in ExecutorRun ()
#11 0x80ec121 in ProcessQueryDesc ()
#12 0x80ec19e in ProcessQuery ()
#13 0x80eaab3 in pg_exec_query_dest ()
#14 0x80ea994 in pg_exec_query ()
#15 0x80ebb28 in PostgresMain ()
#16 0x80d3608 in DoBackend ()
#17 0x80d30f3 in BackendStartup ()
#18 0x80d2716 in ServerLoop ()
#19 0x80d226f in PostmasterMain ()
#20 0x80a5517 in main ()
#21 0x80611fd in _start ()
(gdb)

I don't know what exactly this test did but I shocked from such a
poor performance. We really need to find out what's the problem.
I'm in a way to open a new very big Web+DB project and I'm a little bit
scare to do this with postgres :-)

Regards,
    Oleg


On Sat, 22 May 1999, Edmund Mergl wrote:

> Date: Sat, 22 May 1999 06:39:25 +0200
> From: Edmund Mergl <E.Mergl@bawue.de>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: PostgreSQL Hackers Mailinglist <pgsql-hackers@postgreSQL.org>
> Subject: Re: [HACKERS] strange behavior of UPDATE
> 
> Tom Lane wrote:
> > 
> > Edmund Mergl <E.Mergl@bawue.de> writes:
> > > 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.
> > 
> > Can you provide a script or something to reproduce this behavior?
> > 
> > There are a number of people using Postgres with large databases
> > and not reporting any such problem, so I think there has to be some
> > special triggering condition; it's not just a matter of things
> > breaking at a million rows.  Before digging into it, I'd like to
> > eliminate variables like whether I have the right test case.
> > 
> >                         regards, tom lane
> 
> 
> the original benchmark can be found at
> 
>    ftp://ftp.heise.de/pub/ix/benches/sqlb-21.tar
> 
> for a stripped-down version see the attachment.
> For loading the database and running the first
> and second part (selects and updates) just do
> the following: 
> 
>   createdb test
>   ./make_wnt 1000000 pgsql >make.out 2>&1 &
> 
> This needs about 700 MB of diskspace.
> On a PII-400 it takes about 40 minutes to
> load the database, 20 minutes to create the indeces
> and 20 minutes to run the first part of the
> benchmark (make_sqs). For running the benchmark
> in 20 minutes (without swapping) one needs 384 MB RAM.
> 
> The second part (make_nqs) contains update
> statements which can not be performed properly
> using PostgreSQL.
> 
> For testing it is sufficient to initialize the
> database and then to perform a query like
> 
>    update bench set k500k = k500k + 1 where k100 = 30
> 
> 
> Edmund
> 
> -- 
> Edmund Mergl          mailto:E.Mergl@bawue.de
> Im Haldenhau 9        http://www.bawue.de/~mergl
> 70565 Stuttgart       fon: +49 711 747503
> Germany

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Going through the documentation I can only find little about outer
joins. One statement is in the Changes doc about including syntax for
outer joins, but there doesn't seem to be implemented any code after
that.

Is it true that there's no outer joins yet? Any plans? Btw. what is the
syntax for outer joins. I know only Oracle's (+) operator.

Edmund Mergl <E.Mergl@bawue.de> writes:
> 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.

I tried to reproduce this with current sources on a rather underpowered
Linux box (64Mb of memory, about 40Mb of which is locked down by a
high-priority data collection process).  It took a *long* time, but
as far as I could see it was all disk activity, and that's hardly
surprising given the drastic shortage of buffer cache memory.
In particular I did not see any dramatic growth in the size of the
backend process.  The test case

update bench set k500k = k500k + 1 where k100 = 30;

required a maximum of 10Mb.

Perhaps you could try it again with a current 6.5 snapshot and see
whether things are any better?

Also, I suspect that increasing the postmaster -B setting beyond its
default of 64 would be quite helpful.
        regards, tom lane

Tom,

did you wait until test finished.
I also tried to reproduce test with current 6.5 cvs, Linux 2.0.36,
DUAL PPRO 256Mb. It's still running, it's extremely slow, but
memory usage was about 10-11Mb, CPU usage about 5-9%. 
I use -B 1024 option. No surprize people won't use Postgres 
for large application.

9:12[postgres@zeus]:~/test/sqlbench> cat cat L9905232104.txt

postgresql-6.5pre on linux-2.2.7
Start of inserting 1000000 rows:       Sun May 23 21:04:32 MSD 1999
Start of indexing 1000000 rows:        Mon May 24 00:09:47 MSD 1999
Start of SetQuery single user:    Mon May 24 03:24:01 MSD 1999
Start of NewQuery single user:    Mon May 24 05:23:41 MSD 1999

9:15[postgres@zeus]:~/test/sqlbench>gdb /usr/local/pgsql.65/bin/postgres 10130
GDB is free software and you are welcome to distribute copies of itunder certain conditions; type "show copying" to see
theconditions.
 
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (i486-slackware-linux),
Copyright 1996 Free Software Foundation, Inc...

/usr2/u/postgres/test/sqlbench/10130: No such file or directory.
Attaching to program /usr/local/pgsql.65/bin/postgres', process 10130
Reading symbols from /lib/libdl.so.1...done.
Reading symbols from /lib/libm.so.5...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /lib/libncurses.so.3.0...done.
Reading symbols from /lib/libc.so.5...done.
Reading symbols from /lib/ld-linux.so.1...done.
0x400c0564 in __read ()
(gdb) bt
#0  0x400c0564 in __read ()
#1  0x80e5abb in FileRead ()
#2  0x80ec793 in mdread ()
#3  0x80ed3b5 in smgrread ()
#4  0x80e34d2 in ReadBufferWithBufferLock ()
#5  0x80e33b2 in ReadBuffer ()
#6  0x806ff28 in heap_fetch ()
#7  0x809ec19 in IndexNext ()
#8  0x809b3e9 in ExecScan ()
#9  0x809ed61 in ExecIndexScan ()
#10 0x8099a46 in ExecProcNode ()
#11 0x809d1bd in ExecAgg ()
#12 0x8099ab6 in ExecProcNode ()
#13 0x80989f0 in ExecutePlan ()
#14 0x80982eb in ExecutorRun ()
#15 0x80eff54 in ProcessQueryDesc ()
#16 0x80effce in ProcessQuery ()
#17 0x80ee783 in pg_exec_query_dest ()
#18 0x80ee664 in pg_exec_query ()
#19 0x80ef8d8 in PostgresMain ()
#20 0x80d7290 in DoBackend ()
#21 0x80d6dd3 in BackendStartup ()
#22 0x80d6496 in ServerLoop ()
---Type <return> to continue, or q <return> to quit---
#23 0x80d603c in PostmasterMain ()
#24 0x80a9287 in main ()
#25 0x806502e in _start ()
(gdb) 

Top shows:

10130 postgres   7   0 11020  10M  9680 D       0  5.9  4.0   5:04 postmaster

Regards,
    Oleg

On Sun, 23 May 1999, Tom Lane wrote:

> Date: Sun, 23 May 1999 20:43:33 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Edmund Mergl <E.Mergl@bawue.de>
> Cc: PostgreSQL Hackers Mailinglist <pgsql-hackers@postgreSQL.org>
> Subject: Re: [HACKERS] strange behavior of UPDATE 
> 
> Edmund Mergl <E.Mergl@bawue.de> writes:
> > 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.
> 
> I tried to reproduce this with current sources on a rather underpowered
> Linux box (64Mb of memory, about 40Mb of which is locked down by a
> high-priority data collection process).  It took a *long* time, but
> as far as I could see it was all disk activity, and that's hardly
> surprising given the drastic shortage of buffer cache memory.
> In particular I did not see any dramatic growth in the size of the
> backend process.  The test case
> 
> update bench set k500k = k500k + 1 where k100 = 30;
> 
> required a maximum of 10Mb.
> 
> Perhaps you could try it again with a current 6.5 snapshot and see
> whether things are any better?
> 
> Also, I suspect that increasing the postmaster -B setting beyond its
> default of 64 would be quite helpful.
> 
>             regards, tom lane
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

> Going through the documentation I can only find little about outer
> joins. One statement is in the Changes doc about including syntax for
> outer joins, but there doesn't seem to be implemented any code after
> that.
> Is it true that there's no outer joins yet? Any plans? Btw. what is the
> syntax for outer joins. I know only Oracle's (+) operator.

There is a small amount of code inside of #ifdef ENABLE_OUTER_JOINS
but it is not even close to what needs to be present for anything to
run. Bruce and I were talking about an implementation, but it is
definitely not coming for v6.5.
                  - Thomas

Oh, the syntax has lots of variants, but the basic one is:

select * from t1 left|right|full outer join t2 on t1.x = t2.x;

or

select * from t1 left|right|full outer join t2 using (x);

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> select * from t1 left|right|full outer join t2 on t1.x = t2.x;

Will this be correct?

SELECT * FROM t1, t2, t3, t4 LEFT OUTER JOIN ON t1.x = t2.x, t1.x = t3.x, t1.x = t4.x;

> > select * from t1 left|right|full outer join t2 on t1.x = t2.x;
> Will this be correct?
> SELECT * FROM t1, t2, t3, t4 LEFT OUTER JOIN ON t1.x = t2.x,
>  t1.x = t3.x, t1.x = t4.x;

Left outer joins will take the left-side table and null-fill entries
which do not have a corresponding match on the right-side table. If
your example is trying to get an output row for at least every input
row from t1, then perhaps the query would be

select * from t1 left join t2 using (x)                left join t3 using (x)                left join t4 using (x);

But since I haven't implemented it yet I don't have much experience
with the outer join syntax...
                        - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> Left outer joins will take the left-side table and null-fill entries
> which do not have a corresponding match on the right-side table. If
> your example is trying to get an output row for at least every input
> row from t1, then perhaps the query would be
> 
> select * from t1 left join t2 using (x)
>                  left join t3 using (x)
>                  left join t4 using (x);
> 
> But since I haven't implemented it yet I don't have much experience
> with the outer join syntax...

You miss at least two points: The keyword OUTER and the column name
from t1. As I know, LEFT is the default, so it could be omitted.

Maybe
SELECT * FROM t1 USING (X) OUTER JOIN t2 USING (Y)OUTER JOIN t3 USING (Z)OUTER JOIN t4 using (t);

It should be possible to boil it down to
SELECT * FROM t1 USING (X) OUTER JOIN t2 USING (Y), t3 USING (Z), t4 using (t);

> > Left outer joins will take the left-side table and null-fill entries
> > which do not have a corresponding match on the right-side table. If
> > your example is trying to get an output row for at least every input
> > row from t1, then perhaps the query would be
> > select * from t1 left join t2 using (x)
> >                  left join t3 using (x)
> >                  left join t4 using (x);
> > But since I haven't implemented it yet I don't have much experience
> > with the outer join syntax...
> You miss at least two points: The keyword OUTER and the column name
> from t1. As I know, LEFT is the default, so it could be omitted.

"OUTER" conveys no additional information, and can be omitted. My copy
of Date and Darwen indicates that "LEFT JOIN" is the minimum required
to get a left outer join (i.e. the "LEFT" can not be omitted).

I'm not sure what you mean about missing something about "the column
name for t1". My hypothetical query is referring to column "x",
present in all four tables. Was there some other place a column for t1
should be mentioned?

> Maybe
> SELECT * FROM t1 USING (X) OUTER JOIN t2 USING (Y)
>  OUTER JOIN t3 USING (Z)
>  OUTER JOIN t4 using (t);
> It should be possible to boil it down to
> SELECT * FROM t1 USING (X) OUTER JOIN t2 USING (Y), t3 USING (Z), t4 using (t);

This doesn't resemble SQL92, but may have some similarity to outer
join syntaxes in Oracle, Sybase, etc. Don't know myself.

A (hypothetical) simple two table outer join can be written as
 select * from t1 left join t2 using (x);

Introducing a third table to be "left outer joined" to this
intermediate result can be done as
 select * from t1 left join t2 using (x)                  left join t3 using (x);

where the second "x" refers to the column named "x" from the first
outer join, and the column named "x" from t3.

An alternate equivalent query would be
 select * from t1 left join t2 on t1.x = t2.x                  left join t3 on x = t3.x;

Hope this helps (and that I've got the details right now that I've
spouted off... :)
                      - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> "OUTER" conveys no additional information, and can be omitted. My copy

Sorry. You're right. Just as long as you accept it.

> I'm not sure what you mean about missing something about "the column
> name for t1". My hypothetical query is referring to column "x",
> present in all four tables. Was there some other place a column for t1
> should be mentioned?

What if the column is named order_id in one table and ord_id in another?

>   select * from t1 left join t2 on t1.x = t2.x
>                    left join t3 on x = t3.x;

OK, this will do it. You can have a t1.x = t2.y.