After several days of running a simple query over a very large dataset
(more than a terabyte), I logged in to note that postgresql had
decided to log *billions* of log lines.
The query looks like this:
create table result_table as
select TEXTFIELD, count(distinct INTFIELD) from SOME_HUGE_TABLE group
by TEXTFIELD;
At some point, PG started logging stuff that looked like this (over
and over again):
SELECT 2704204951 user@dbname LOG: 00000: performsort starting: CPU 0.00....
The 2704204951 number corresponds to the "%l" (session line number)
in log_line_prefix. Thus, by the time I saw this, it had logged 2.7
billion lines.
An strace of the process showed this, over and over again:
getrusage(....)
write(2, <logging info>)
and nothing else. What happened here? It seems like PG just went
crazy and got itself into some sort of loop. I had to kill the query.
This is PostgreSQL 8.4.13 on x86_64, Linux, using Scientific Linux 6.4.
--
Jon