Обсуждение: Memory consumption problem

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

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

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.

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.

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