Re: Tests with switch of WAL segment files.

Hi! I am trying to better understand when switch of WAL files takes place. I have executed the next tests while "archive_mode = off":

    $ initdb -D /var/lib/postgresql/8.3/data

    $ du -sk /var/lib/postgresql/8.3/data/base
    13156   base

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ du -sk /var/lib/postgresql/8.3/data/base
    116532  base
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 17:21 000000010000000000000000

    $ psql -t -c "select pg_size_pretty(pg_total_relation_size('heavytable')) as size;"
    97 MB

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ du -sk /var/lib/postgresql/8.3/data/base
    116588  base
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 17:26 000000010000000000000000

I create many other times the 'heavytable', nothing changed, WAL segment file keeps to be the same, the size of base directory almost didn't change.
I guess that's normal, and I think there is something that I haven't yet reached to understand from all the docs that I have read. I have two questions:

Q1 - Why do I have just one segment file the whole time? I thought PostgreSQL would create at least 3 of them at the beginning... I have "checkpoint_segments = 3".

If I now state a "select pg_switch_xlog();" it returns one label, but if I execute it again N times it will constantly return the same label (while I don't insert data).
I have executed "select pg_switch_xlog();" many times (please see the next log of tests). After that, I realize that PostgreSQL is ready to create the next segment file ONLY whenever I insert new data.

Q2 - After that, I stated a CHECKPOINT, what caused a switch to a new file. I execute CHECKPOINT once again and switch takes place again. But, if I execute CHECKPOINT again and again, from now and on will NOT be any switches. Why? What makes the switch to a new file take place?

Here is the log of tests:

    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/4EFA80
    $ du -sk /var/lib/postgresql/8.3/data/base
    116684  base
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:23 000000010000000000000000

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000000
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000001

    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/100B9D0
    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/2000000
    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/2000000
    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/2000000
    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000000
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000001
    -rw------- 1 postgres postgres  16M 2009-07-16 18:49 000000010000000000000002

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;" # <== it causes switch
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000001
    -rw------- 1 postgres postgres  16M 2009-07-16 18:52 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"  # <== one of these will cause switch
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:52 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000004

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"  # <== there won't be any switches from now and on!
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:52 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000004

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:56 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:56 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:59 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 19:00 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

Thanks in advance!!
J. Carlos Muro