Thread: PG 8 Out of memory issuing queries

Hi!

Hope anybody can help me with this issue. I have a fairly large table
(~75 million rows) that where imported without pk. Tried to create a pk
on a supposed unique field, but failed with duplicates. So i issued:
select oldid from table group by oldid having count(*) > 1
and it fails with Out of memory. Tried to create a new pk using: alter
table <table> add newid serial primary key; and it also fails with out
of memory error.

Are there any settings on postgresql.conf that I may change to prevent
this error?

This is on Windows XP profesional with Postgresql 8.0


TIA,

--
Werner Bohl <WernerBohl@infutor.com>
Infutor de Costa Rica S.A.

Werner Bohl wrote:
> Hi!
>
> Hope anybody can help me with this issue. I have a fairly large table
> (~75 million rows) that where imported without pk. Tried to create a pk
> on a supposed unique field, but failed with duplicates. So i issued:
> select oldid from table group by oldid having count(*) > 1
> and it fails with Out of memory. Tried to create a new pk using: alter
> table <table> add newid serial primary key; and it also fails with out
> of memory error.

Use a cursor during your large count(*) query and you can find you bad
entries.

J


>
> Are there any settings on postgresql.conf that I may change to prevent
> this error?
>
> This is on Windows XP profesional with Postgresql 8.0
>
>
> TIA,
>


--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage,  and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com