Обсуждение: Left join error

Hello.
I have a table with 146000+ records. I tried to select with a left join to
another table but I get this message:

ERROR:  MemoryContextAlloc: invalid request size 4294967293

This is the query.

select * from cheques left join formasenblanco on cheques.cheque=
formasenblanco.cheque where formasenblanco.cheque is null;

The table cheques has about 146000+ records. Formasenblanco is smaller. I
know I can use subquerys, but in theory, this is much slower.
I'm using Postgres 7.1 on Redhat 6.2

Does anyone have a suggestion?

Thank you.

Ligia

Ligia,

> ERROR:  MemoryContextAlloc: invalid request size 4294967293

This sounds like a memory/server configuration problem.  have you had
any other errors running PostgreSQL?  Can you post your machine
configuration (processor, memory, other services, etc.)?

Though I'm not sure this is the best list for runtime problems.
However, I'm not sure what the right list would be.  Hey, Marc, how
about a PGSQL-PERFORMANCE list?

> select * from cheques left join formasenblanco on cheques.cheque=
> formasenblanco.cheque where formasenblanco.cheque is null;

I don't believe that this will get you the results you are seeking, even
when the techincal problem gets fixed.    I think what you really want
is:

SELECT *
FROM cheques
WHERE NOT EXISTS (SELECT f.cheque FROM formasenblanco f     WHERE f.cheque = cheques.cheque);

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Ligia,

> Anyhow,... I found that if I write more restrictions on the same
> query, like
> another where, the query responds as I expect. Maybe I was writing
> something
> wrong.

Then it's definitely symptomatic of a memory config problem.  I'd say
that when queries get very large, pgsql is trying to use memory that
doesn't exist.  Possibilites: you've set shared_buffers or sort_mem much
too high; your machine is chronically low on memory; you have some kind
of kernel config problem; you have a bad RAM card.

Good luck on figuring out which.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

"Ligia Pimentel" <lmpimentel@yahoo.com> writes:
> I have a table with 146000+ records. I tried to select with a left join to
> another table but I get this message:
> ERROR:  MemoryContextAlloc: invalid request size 4294967293

Hmm.  This might indicate corrupted data --- do you see a similar error
if you just do "select * from cheques" or "select * from formasenblanco"?

If not, then I think you've run into a software bug.  It'd be worth
while to update to 7.1.3 before pursuing it further.  If you still see
the failure in 7.1.3 then I would like to see an EXPLAIN of the join
query as well as a stack backtrace from the error.  The easiest way
to get the stack trace is

in window 1:fire up psql

in window 2:use ps to determine PID of the backend your psql is connected to
gdb /path/to/postgres-executable    -- do this as postgres user
gdb> attach backend-PID-found-above
gdb> break elog
gdb> continue

in window 1:issue the problem query

in window 2:gdb should report reaching the breakpoint.  Do
gdb> bt--- copy & paste results printed here
gdb> quit
        regards, tom lane