Re: Plsql Function with error: No space left on device.

From: "David M. Richter" <D(dot)Richter(at)DKFZ-heidelberg(dot)de>

> Hallo Richard!
>
> Thanks a lot for Your hint. It works. Yeah.
> But only in a little database of 1000 rows.
> In my case with 4 milions of rows I was waiting 17 hours without any
> success by using the processor 97%.
> So I canceled the Job.

Patience is a virtue, but nobody is *that* virtuous.

> What could i optimize in this query?

Not much as it stands, it couldn't be much simpler.

> Do You perhaps have another great hint?

Well - if you've been drinking all night, have a glass of water before going
to bed - always served me well ;-)

We're going to need some more information. Try EXPLAIN <update query> and
post the results along with the \d for the two tables and how many records
are in each. Oh - and how many distinct values for each field there are.

I'm assuming for any childoid there is only one corresponding parentoid in
relseries_image? If so, and values of childoid are repeated many times, try
selecting unique pairs into a temporary table. Join against that for the
update. It could be that PG is ending up with a zillion rows in the join.

- Richard Huxton