pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLeft join error
4+ messages / 3 participants
[nested] [flat]
* Left join error
@ 2001-11-05 17:39 Ligia Pimentel <lmpimentel@yahoo.com>
2001-11-06 16:36 ` Re: Left join error Josh Berkus <josh@agliodbs.com>
2001-11-06 19:14 ` Re: Left join error Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Ligia Pimentel @ 2001-11-05 17:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Left join error
2001-11-05 17:39 Left join error Ligia Pimentel <lmpimentel@yahoo.com>
@ 2001-11-06 16:36 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-11-06 16:36 UTC (permalink / raw)
To: Ligia Pimentel <lmpimentel@yahoo.com>; pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Left join error
2001-11-05 17:39 Left join error Ligia Pimentel <lmpimentel@yahoo.com>
@ 2001-11-06 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-11-06 19:14 UTC (permalink / raw)
To: Ligia Pimentel <lmpimentel@yahoo.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Left join error
@ 2001-11-06 18:18 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-11-06 18:18 UTC (permalink / raw)
To: Ligia MarĂa Pimentel C. <lmpimentel@yahoo.com>; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-11-06 19:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-05 17:39 Left join error Ligia Pimentel <lmpimentel@yahoo.com>
2001-11-06 16:36 ` Josh Berkus <josh@agliodbs.com>
2001-11-06 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-06 18:18 Re: Left join error Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox