Re: [ADMIN] Enormous pg_sorttemp~ files when using "OR"

[Robin's Postgres List Archive]

Oh yeah, sorry - I forgot to comment on my environment:

-PostgreSQL 6.5.2
-RedHat 6.0
-Intel Pentium II 400mhz 160mb RAM
-Java/JDBC

On Sat, 18 Dec 1999, Andy Lewis wrote:

> This has also happened to me.
> 
> I had another programmer write a query that was requesting a field from a
> table but, forgot to include the name of the table in the query. It was a
> 3 table query and he accidentally included a fourth table field but didn't
> add the table in the clause.
> 
> I was suprised to see postgres not error out. Instead it sucked all of the
> CPU from the machine threw the pg_sort files all of the drive and
> eventually filled up the drive.
> 
> Very strange. Perhaps the Postgres Team can take note and fix for future.
> 
> Another thing that I am seeing alot of is fields labeled as serial and
> having duplicate oid's. It happens on 3 of my 4 machines on a monthly
> basis. This really needs to be fixed because vacuum won't run without
> the serial fields being unique.
> 
> All of the above running 6.5.3
> 
> Andy
> 
> On Sat, 18 Dec 1999, Robin's Postgres List Archive wrote:
> 
> > 
> > Hi Folks,
> > 
> > When I write a query with a "WHERE" claused constrained by the "OR"
> > operator, postgresql creates the enormous pg_sorttemp~ files in
> > ~pgsql/data/base/dbName/ that basically eat up all remaining disk space in
> > a matter of seconds.  These files are 1.8GB+ by the time the partition is
> > full.
> > 
> > Obviously, I need to rewrite these queries or break them up into multiple
> > queries.  I was posting here to see if anyone with more experience might
> > have a suggestion or comment on this - like a better way to write queries
> > that otherwise would use "OR", or maybe a configuration or hack tip on how
> > to prevent postgresql from running wild by building such huge sorttemp
> > files.
> > 
> > Thanks,
> > 
> > -Robin
> > 
> > 
> > ************
> > 
> 


************