Postgres Pro
Downloads
Home   >   mailing lists

Thread: Memory consumption problem

Memory consumption problem

From
Franck Routier
Date:
Hi,

I have a problem with a simple request :

insert into mytable
select a.id, a.desc, 'mystring'
from mytable a;

This request runs for about two hours, and then OOMKiller kills the
postmaster !

Mytable has about 1,7 million tuples, it has a few indexes.
Postgresql version is 8.1, running on Debian 3.1, with 512kb of memory
and 1,3gb of swap space.
This request seems to end up eating all memory and swap...

Is this something that should occur or is there a problem on my server ?

Thanks,

Franck

Re: Memory consumption problem

From
"Daniel T. Staal"
Date:
On Fri, May 5, 2006 10:52 am, Franck Routier said:
> Hi,
>
> I have a problem with a simple request :
>
> insert into mytable
> select a.id, a.desc, 'mystring'
> from mytable a;

Um, is that selecting the ids and descriptions from a table and then
inserting them into that same table?

I'm not sure what Postgres does with recursion...

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Re: Memory consumption problem

From
Bruno Wolff III
Date:
On Fri, May 05, 2006 at 11:33:06 -0400,
  "Daniel T. Staal" <DStaal@usa.net> wrote:
> On Fri, May 5, 2006 10:52 am, Franck Routier said:
> > Hi,
> >
> > I have a problem with a simple request :
> >
> > insert into mytable
> > select a.id, a.desc, 'mystring'
> > from mytable a;
>
> Um, is that selecting the ids and descriptions from a table and then
> inserting them into that same table?
>
> I'm not sure what Postgres does with recursion...

That wouldn't cause recursion. The SELECT part of the query will see the
original contents of the table and not ones being inserted in the same query.

Re: Memory consumption problem

From
Valentin Gjorgjioski
Date:
On 05.05.2006 16:52 Franck Routier wrote:
> Hi,
>
> I have a problem with a simple request :
>
> insert into mytable
> select a.id, a.desc, 'mystring'
> from mytable a;
>
well similar queries with 2 milion tuples runs on my server in 1sec
without problems.

Re: Memory consumption problem

From
Tom Lane
Date:
Franck Routier <franck.routier@axege.com> writes:
> I have a problem with a simple request :

> insert into mytable
> select a.id, a.desc, 'mystring'
> from mytable a;

> Mytable has about 1,7 million tuples, it has a few indexes.

Tell us about triggers or foreign keys involving that table?

            regards, tom lane