Thread: temporary tables are logged somehow?
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
As metioned, for example, in Robert Haas blog
3. They are not WAL-logged.
Whats wrong with it in my case?
--
Regards, Andrey Lizenko
On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko <lizenko79@gmail.com> wrote: >> 3. They are not WAL-logged. > Whats wrong with it in my case? Nothing. Temporary tables are not WAL-logged, but transaction commit is. -- Michael
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
Why unlogged tables behaviour is not the same?
If I try this:
create unlogged table if not exists positiontemporarytable (pos_instrument_id integer, pos_code varchar(40));
prepare pos_delete as delete from "positiontemporarytable";
execute pos_delete;
no WAL files created at all.
On 22 January 2015 at 17:36, Michael Paquier <michael.paquier@gmail.com> wrote:
On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko <lizenko79@gmail.com> wrote:
>> 3. They are not WAL-logged.
> Whats wrong with it in my case?
Nothing. Temporary tables are not WAL-logged, but transaction commit is.
--
Michael
Regards, Andrey Lizenko
Michael Paquier <michael.paquier@gmail.com> writes:
> On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko <lizenko79@gmail.com> wrote:
>> 3. They are not WAL-logged.
>> Whats wrong with it in my case?
> Nothing. Temporary tables are not WAL-logged, but transaction commit is.
Right. I think there is some optimization for transactions that only
wrote temp-table data, but it could at most be skipping fsync of the
transaction commit record, not omitting generating it entirely.
Also, changes to the system catalogs are always WAL-logged; so creation
and deletion of a temp table cause some amount of WAL output, even if
manipulation of the table's contents does not.
regards, tom lane
It means, that 8192 commits + 8192 "create temp table" (and drop it after closing connection) costs me 48 MB of WAL files.
And there is no way to reduce disk space usage, right?
Does amount of data which has to be written to WAL-file depend on size of transaction?
On 22 January 2015 at 18:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Right. I think there is some optimization for transactions that onlyMichael Paquier <michael.paquier@gmail.com> writes:
> On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko <lizenko79@gmail.com> wrote:
>> 3. They are not WAL-logged.
>> Whats wrong with it in my case?
> Nothing. Temporary tables are not WAL-logged, but transaction commit is.
wrote temp-table data, but it could at most be skipping fsync of the
transaction commit record, not omitting generating it entirely.
Also, changes to the system catalogs are always WAL-logged; so creation
and deletion of a temp table cause some amount of WAL output, even if
manipulation of the table's contents does not.
regards, tom lane
Regards, Andrey Lizenko