Postgres Pro
Downloads
Home   >   mailing lists

temporary tables are logged somehow? - Mailing list pgsql-general

From Andrey Lizenko
Subject temporary tables are logged somehow?
Date
Msg-id CADKuZZCojR_gU5S+zHhDt81k7-Jq86e-k_4sq4K53sC64_m1yA@mail.gmail.com
Whole thread Raw
Responses Re: temporary tables are logged somehow?  (Michael Paquier <michael.paquier@gmail.com>)
Re: temporary tables are logged somehow?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Tree view
Hello all, 
I have a problem with growing WAL-files populating a temporary table.

After running the following script 8192 times (each in separate connection) I can see 3*16 MB WAL files.


CREATE TEMP TABLE IF NOT EXISTS positiontemporarytable (pos_instrument_id integer, pos_code varchar(40));
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
prepare pos_insert (integer, varchar(40)) as insert into "positiontemporarytable" values($1, $2);
execute pos_insert ('76','27958');
execute pos_insert ('71','9406:58');
...... 
<about 8000 same 'executes' commands>
...... 
execute pos_insert ('74','19406:58'); 
COMMIT;


It was tested via pgbench:
pgbench -l -t 8192 -C -f /db/postgres/report_test.sql db_test
 
and  simple bash script:
for i in {1..8192}
do
    echo $i
    psql -f /db/postgres/report_test.sql db_test
done

Results are the same. Server version 9.3.5 (it seems 9.2.4 and 9.2.9 also affected by this).

As metioned, for example, in Robert Haas blog
3. They are not WAL-logged.

Whats wrong with it in my case?


--
Regards, Andrey Lizenko

pgsql-general by date:

Previous
From: Spiros Ioannou
Date:
Subject: Re: partitioning query planner almost always scans all tables
Next
From: Michael Paquier
Date:
Subject: Re: temporary tables are logged somehow?