Re: [BUGS] General Bug Report: Files greater than 1 GB are created while sorting

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Doug Mitchell
Your email address : doug(at)mitchcraft(dot)com

Category : runtime: back-end
Severity : serious

Summary: Files greater than 1 GB are created while sorting

System Configuration
--------------------
Operating System : Linux 2.2.5 ELF (RedHat 6.0)

PostgreSQL version : 6.5

Compiler used : cc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)

Hardware:
---------
PII-300 with 384 MB RAM

Versions of other tools:
------------------------
$ rpm -q make flex
make-3.77-6
flex-2.5.4a-6

--------------------------------------------------------------------------

Problem Description:
--------------------

The backed is creating files bigger than 1 GB when sorting
and it will break when the file gets to 2 GB.

Here are the biggest files:

1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:10 bigtable_pkey
1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:36 pg_temp.2446.0
1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:55 bigtable
1122136 -rw------- 1 postgres postgres 1147937412 Jun 30 21:39 pg_temp2769.3
1148484 -rw------- 1 postgres postgres 1174890288 Jun 30 21:26 pg_temp2769.4

I also have some smaller ".1" files that are the rest of the above
files along with everything else you might expect to find in a
PG database directory. It's those two last big ones that are
troublesome.

Table and indicies are segmenting just fine at 1GB, but
some sort files just keep growing. I did actually get a
back-end error one time when one exceeded 2 GB.

Thanks,
Doug

--------------------------------------------------------------------------

Test Case:
----------
Just do:
mydb=> select * into bigtable2 from bigtable order by custno;

You might want to decrease RELSEGSZ to see it faster.
Mail be back if you can't reproduce it.
(and please make the bug report form boxes bigger!)

--------------------------------------------------------------------------

Solution:
---------
Something is not using the Magnetic Disk Storage Manager,
but is writing a temp file out on its own during the sort.

--------------------------------------------------------------------------

Nice analysis of a problem. Probably psort code is not using those
routines because we never expected the sorts to get over 2Gigs.

> The backed is creating files bigger than 1 GB when sorting
> and it will break when the file gets to 2 GB.
>
> Here are the biggest files:
>
> 1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:10 bigtable_pkey
> 1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:36 pg_temp.2446.0
> 1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:55 bigtable
> 1122136 -rw------- 1 postgres postgres 1147937412 Jun 30 21:39 pg_temp2769.3
> 1148484 -rw------- 1 postgres postgres 1174890288 Jun 30 21:26 pg_temp2769.4
>
> I also have some smaller ".1" files that are the rest of the above
> files along with everything else you might expect to find in a
> PG database directory. It's those two last big ones that are
> troublesome.
>
> Table and indicies are segmenting just fine at 1GB, but
> some sort files just keep growing. I did actually get a
> back-end error one time when one exceeded 2 GB.
>
> Thanks,
> Doug
>
>
> --------------------------------------------------------------------------
>
> Test Case:
> ----------
> Just do:
> mydb=> select * into bigtable2 from bigtable order by custno;
>
> You might want to decrease RELSEGSZ to see it faster.
> Mail be back if you can't reproduce it.
> (and please make the bug report form boxes bigger!)
>
> --------------------------------------------------------------------------
>
> Solution:
> ---------
> Something is not using the Magnetic Disk Storage Manager,
> but is writing a temp file out on its own during the sort.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

I haved added to the TODO list:

* Allow creation of sort tables > 1 Gig

> Problem Description:
> --------------------
>
> The backed is creating files bigger than 1 GB when sorting
> and it will break when the file gets to 2 GB.
>
> Here are the biggest files:
>
> 1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:10 bigtable_pkey
> 1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:36 pg_temp.2446.0
> 1049604 -rw------- 1 postgres postgres 1073741824 Jun 30 19:55 bigtable
> 1122136 -rw------- 1 postgres postgres 1147937412 Jun 30 21:39 pg_temp2769.3
> 1148484 -rw------- 1 postgres postgres 1174890288 Jun 30 21:26 pg_temp2769.4

I have renamed these sort temp tables to pg_sorttemp so they will not be
confused with actual temp tables. Because people installing 6.5.1 will
have stopped their postmasters, that will not cause any problems.

You are safe up to 2 gigs, and at that point, the OS will can cause a
problem. The new naming should make the cause clearer. Don't know if
we can get this done in 6.5.1 because the change to segment these
requires some work. Looks like the psort code goes right to fd/*,
bypassing the storage manager.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026