Oh, here is more context about this issue: http://www.postgresql.org/message-id/201208171307.16342.and...@2ndquadrant.com
-- Valentin On Wed, Aug 28, 2013 at 1:28 AM, Valentine Gogichashvili <val...@gmail.com>wrote: > Hello Andreas, > > it is more then a year now, that I have not returned to that topic. > > As we still have a problem with very slow WAL replay in situations, when > we create and drop a lot of temporary tables, I have made a small synthetic > test. > I have build the simplest steaming replication using PostgreSQL 9.3rc1. > > My goal was to see, if the recovery process will be able to keep up with > this WAL flow. > > Running this sproc on the master: > > CREATE OR REPLACE FUNCTION public.f() > RETURNS integer > LANGUAGE plpgsql > AS $function$ > begin > > CREATE TEMP TABLE temp_table_to_test_replication AS > SELECT s.i as id from generate_series(1, 100) as s(i); > DROP TABLE temp_table_to_test_replication; > RETURN 1; > end; > $function$ > > leads to writing of WAL files. Is it an expected behavior? Is it expected > that WAL files are filled when the only thing, that sproc is supposed to > do is to create and drop a temporary table. Are these catalog changes? > > (I was calling it with: > > seq 1000 | xargs -l -n 1 -P 5 -I x psql -tA -c "select f();" > /dev/null > > for that call pg_stat_replication.sent_location moved from 0/21891CB0 to > 0/21DFEC74 or 5689284 bytes > ) > > > WAL files are also written when executing a stored procedure, that was > writing into an UNLOGGED table: > > CREATE OR REPLACE FUNCTION public.s() > RETURNS integer > LANGUAGE plpgsql > AS $function$ > begin > INSERT INTO unlogged_test_table SELECT pg_backend_pid() FROM > generate_series(1,100) as s(i); > DELETE FROM unlogged_test_table WHERE id = pg_backend_pid(); > RETURN pg_backend_pid(); > end; > $function$ > > (WAL position moved from 0/21E51894 to 0/21E5B58C for 40184 bytes) > > Also as I recreated the same table as a LOGGED one, the WAL diff generated > becomes 11668768 that is what I would expect. > > WAL files are not being generated when calling really read-only sprocs > like now() :) > > Regards, > > Valentine Gogichashvili >