pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)

Hi everybody,

I'm a PG newbie, and I'm trying to write a function that returns a set of
records.
These records come from 2 or more tables (joined).

When I have only 1 table, the return type of the function can be SETOF
mytable.
When I have 2 tables or more, am I obliged to create a type (CREATE TYPE) so
that my function can return a SETOF mytype?

This works fine, but I'm trying to avoid building a specific type for each
row returning function.
Is there any other solution (SQL or PLPGSQL)?

TIA,
-- Hervé Inisan.

pgsql-general-owner@postgresql.org wrote:

Hi everybody,

I'm a PG newbie, and I'm trying to write a function that returns a
set of records.
These records come from 2 or more tables (joined).

When I have only 1 table, the return type of the function can be SETOF
mytable.
When I have 2 tables or more, am I obliged to create a type (CREATE
TYPE) so that my function can return a SETOF mytype?

This works fine, but I'm trying to avoid building a specific type for
each row returning function.
Is there any other solution (SQL or PLPGSQL)?

Yes, return setof record, look at this site, it helped me a lot.

http://techdocs.postgresql.org/guides/SetReturningFunctions

---
Miguel

"Reid Thompson" <Reid.Thompson@ateb.com> writes:

Is there any solution to this other than adding memory, or am I
mis-understanding the error?

pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
pg_dump: lost synchronization with server, resetting connection

My bet is that this is actually a corrupt-data problem ... unless you
actually have any 875MB fields in your table. The implication is that
the length word of a variable-width field contains garbage. More than
likely, the whole tuple is garbaged, but this happens to be the first
visible symptom.

There is plenty of discussion of recovering from data corruption in
the archives, so go have a look.

v7.2.3 on RHAT 7.3 linux with 128MB RAM.

Might want to think about an update sometime, too ;-)

regards, tom lane

Tom Lane wrote:

"Reid Thompson" <Reid.Thompson@ateb.com> writes:

Is there any solution to this other than adding memory, or am I
mis-understanding the error?

pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
pg_dump: lost synchronization with server, resetting connection

My bet is that this is actually a corrupt-data problem ...
unless you actually have any 875MB fields in your table. The
implication is that the length word of a variable-width field
contains garbage. More than likely, the whole tuple is
garbaged, but this happens to be the first visible symptom.

There is plenty of discussion of recovering from data
corruption in the archives, so go have a look.

v7.2.3 on RHAT 7.3 linux with 128MB RAM.

Might want to think about an update sometime, too ;-)

regards, tom lane

No, no 875MB fields -- the largest field is limited to 2048 chars.
Heading to the archives.

thanks,

reid

Hi Herv�,

You should perhaps use record instead of a particular type.

... RETURNS SETOF record AS ...

Otto

----- Original Message -----
From: "Herv� Inisan" <typo3@self-access.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, May 12, 2005 8:31 PM
Subject: [GENERAL] About Types

Hi everybody,

I'm a PG newbie, and I'm trying to write a function that returns a set of
records.
These records come from 2 or more tables (joined).

When I have only 1 table, the return type of the function can be SETOF
mytable.
When I have 2 tables or more, am I obliged to create a type (CREATE TYPE) so
that my function can return a SETOF mytype?

This works fine, but I'm trying to avoid building a specific type for each
row returning function.
Is there any other solution (SQL or PLPGSQL)?

TIA,
-- Herv� Inisan.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

This works fine, but I'm trying to avoid building a

specific type for

each row returning function.
Is there any other solution (SQL or PLPGSQL)?

Yes, return setof record, look at this site, it helped me a lot.

http://techdocs.postgresql.org/guides/SetReturningFunctions

Thank you Miguel and Otto.
RECORD did the trick :-)

-- Hervé Inisan.

On Thu, May 12, 2005 at 02:56:35PM -0400, Tom Lane wrote:

"Reid Thompson" <Reid.Thompson@ateb.com> writes:

Is there any solution to this other than adding memory, or am I
mis-understanding the error?

pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
pg_dump: lost synchronization with server, resetting connection

My bet is that this is actually a corrupt-data problem ... unless you
actually have any 875MB fields in your table. The implication is that
the length word of a variable-width field contains garbage. More than
likely, the whole tuple is garbaged, but this happens to be the first
visible symptom.

Just for curiosity sake:

875574064 base 10 = 34303330 base 16

Looks like the ASCII string "4030"

Definitly corrupted tuple...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/