Re: temporary tables are logged somehow?

On 01/22/2015 06:06 AM, Andrey Lizenko wrote:
> 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.

3 * 16 = 48MB

Say each row takes 10 bytes(an underestimate).

8000 rows * 8192 connections * 10 bytes = 655,360,000 bytes or 655.36
megabytes.

>
>
>     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
> http://rhaas.blogspot.ru/2010/05/global-temporary-and-unlogged-tables.html
>
>     3. They are not WAL-logged.
>
>
> Whats wrong with it in my case?

Nothing as far as I can see.

>
>
> --
> Regards, Andrey Lizenko


--
Adrian Klaver
adrian.klaver@aklaver.com