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