Re: pgAdmin - Query - out of memory for query result

Hello:

I have run into a very frustrating problem.

I have a database with some tables which were loaded from Excel spreadsheets
via a Perl script. Guess that does not really matter. The table
definitions
are below.

Both tables contain 9,398,340 rows

On a machine running Windows 7 64Bit with 6Gig of RAM
When I do a simple query, select * from estimated_idiosyncratic_
return, on these tables I get: out of memory for query result

If I run the same query on the machine where the database resides,
the query runs, No issue. The machine where the database resides is
laptop running Windows XP Sp2 with 3 Gig of RAM.

Other queries run fine.

I do not understand why these simple queries would work on a machine
with 3Gig of RAM and fail on a machine with 6Gig.

Your kind assistance is requested.

KD

-- DROP TABLE estimated_systematic_return;

CREATE TABLE estimated_systematic_return
(
est_systematic_return_key serial NOT NULL,
ws_run_key integer,
fund_key integer NOT NULL,
period integer,
period_index integer,
period_yyyymm integer,
est_systematic_return_value numeric(30,20) NOT NULL,
CONSTRAINT est_systematic_return_pk PRIMARY KEY
(est_systematic_return_key )
)
WITH (
OIDS=FALSE
);
ALTER TABLE estimated_systematic_return
OWNER TO postgres;

-- Index: estimated_systematic_return_ws_run_key

-- DROP INDEX estimated_systematic_return_ws_run_key;

CREATE INDEX estimated_systematic_return_ws_run_key
ON estimated_systematic_return
USING btree
(ws_run_key );

-- DROP TABLE estimated_idiosyncratic_return;

CREATE TABLE estimated_idiosyncratic_return
(
est_idiosyncratic_return_key serial NOT NULL,
ws_run_key integer,
fund_key integer NOT NULL,
period integer,
period_index integer,
period_yyyymm integer,
est_idiosyncratic_return_value numeric(30,20) NOT NULL,
CONSTRAINT est_idiosyncratic_return_pk PRIMARY KEY
(est_idiosyncratic_return_key )
)
WITH (
OIDS=FALSE
);
ALTER TABLE estimated_idiosyncratic_return
OWNER TO postgres;

-- Index: estimated_idiosyncratic_return_ws_run_key

-- DROP INDEX estimated_idiosyncratic_return_ws_run_key;

CREATE INDEX estimated_idiosyncratic_return_ws_run_key
ON estimated_idiosyncratic_return
USING btree
(ws_run_key );

-- Index: estimated_idiosyncratic_return_ws_run_key_fund_key

-- DROP INDEX estimated_idiosyncratic_return_ws_run_key_fund_key;

CREATE INDEX estimated_idiosyncratic_return_ws_run_key_fund_key
ON estimated_idiosyncratic_return
USING btree
(ws_run_key , fund_key );

On Wed, 2012-01-11 at 01:10 -0500, Kevin Duffy wrote:
> [...]
> I have run into a very frustrating problem.
>
> I have a database with some tables which were loaded from Excel spreadsheets
> via a Perl script. Guess that does not really matter. The table
> definitions
> are below.
>
> Both tables contain 9,398,340 rows
>
> On a machine running Windows 7 64Bit with 6Gig of RAM
> When I do a simple query, select * from estimated_idiosyncratic_
> return, on these tables I get: out of memory for query result
>
> If I run the same query on the machine where the database resides,
> the query runs, No issue. The machine where the database resides is
> laptop running Windows XP Sp2 with 3 Gig of RAM.
>
> Other queries run fine.
>
> I do not understand why these simple queries would work on a machine
> with 3Gig of RAM and fail on a machine with 6Gig.
>

I suppose from your email's subject that you use pgAdmin on your Windows
machine, and psql on the other one?

If that's true, pgAdmin will probably need more memory to display the
results than psql. I've tried to find the error message you're talking
about, but couldn't find it in pgAdmin's code source. But I guess that,
if your query returns many lines, it will hit hard on the memory. And
even if it can use that much memory, the grid component can get pretty
slow.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org

I am using pgAdmin on both machines.
And for some strange reason the query fails on the machine with more
memory.

The message shows up in the Output pane in the messages tab.

I do not get it.

KD

On Wed, Jan 11, 2012 at 8:50 AM, Guillaume Lelarge
<guillaume(at)lelarge(dot)info>wrote:

> On Wed, 2012-01-11 at 01:10 -0500, Kevin Duffy wrote:
> > [...]
> > I have run into a very frustrating problem.
> >
> > I have a database with some tables which were loaded from Excel
> spreadsheets
> > via a Perl script. Guess that does not really matter. The table
> > definitions
> > are below.
> >
> > Both tables contain 9,398,340 rows
> >
> > On a machine running Windows 7 64Bit with 6Gig of RAM
> > When I do a simple query, select * from estimated_idiosyncratic_
> > return, on these tables I get: out of memory for query result
> >
> > If I run the same query on the machine where the database resides,
> > the query runs, No issue. The machine where the database resides is
> > laptop running Windows XP Sp2 with 3 Gig of RAM.
> >
> > Other queries run fine.
> >
> > I do not understand why these simple queries would work on a machine
> > with 3Gig of RAM and fail on a machine with 6Gig.
> >
>
> I suppose from your email's subject that you use pgAdmin on your Windows
> machine, and psql on the other one?
>
> If that's true, pgAdmin will probably need more memory to display the
> results than psql. I've tried to find the error message you're talking
> about, but couldn't find it in pgAdmin's code source. But I guess that,
> if your query returns many lines, it will hit hard on the memory. And
> even if it can use that much memory, the grid component can get pretty
> slow.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
> PostgreSQL Sessions #3: http://www.postgresql-sessions.org
>
>