Обсуждение: 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