Re: pg_xlog becomes extremely large during CREATE INDEX

On Wed, 2004-05-12 at 21:31, Jeffrey W. Baker wrote:
> On Wed, 2004-05-12 at 20:18, Tom Lane wrote:
> > "Jeffrey W. Baker" <jwbaker(at)acm(dot)org> writes:
> > > I have configured 24 checkpoint segments,
> > > which I expect gives me a worst-case usage in pg_xlog of 384MB.
> > > Unfortunately, during the CREATE INDEX, pg_xlog becomes full!
> >
> > Postgres version? AFAIR 7.1.2 or thereabouts would act that way,
> > because it wouldn't discard log segments containing work of open
> > transactions.

The CREATE INDEX finally returned, and none of the xlog space was
reclaimed. The terminal size is 6.1GB. Interestingly, there is a
continuous group of xlogs that haven't been touched since data started
being copied from pgsql_tmp to ../. (what do you call this stage of the
index build?) In reverse order of modification time:

May 12 21:55 00000008000000F7
May 12 21:49 00000008000000F6
May 12 21:49 00000008000000F5
[... decreasing by 1 until ...]
May 12 20:40 000000070000008C
May 12 20:40 000000070000008B
May 12 20:40 000000070000008A
[the ones below have higher numbers, but aren't being written to!]
May 12 20:40 0000000900000010
May 12 20:40 000000090000000F
May 12 20:39 000000090000000E
May 12 20:39 000000090000000D
May 12 20:39 000000090000000C
May 12 20:39 000000090000000B
May 12 20:39 000000090000000A
May 12 20:39 0000000900000009
May 12 20:39 0000000900000008
May 12 20:39 0000000900000007
May 12 20:39 0000000900000006
May 12 20:39 0000000900000005
May 12 20:39 0000000900000004
May 12 20:38 0000000900000003
May 12 20:38 0000000900000002
May 12 20:38 0000000900000001
May 12 20:38 0000000900000000
May 12 20:38 00000008000000FE
May 12 20:38 00000008000000FD
May 12 20:38 00000008000000FC
May 12 20:38 00000008000000FB
May 12 20:38 00000008000000FA
May 12 20:38 00000008000000F9
May 12 20:37 00000008000000F8

Not coincidentally, there are 24 of these oddballs and 24 is also the
number of checkpoint segments I have configured.

-jwb