fun fact about temp tables

Hello, everyone!

I`ve noticed interesting aspect in temp tables working. It appears
postgres is trying to reserve space on disk for temp tables even before
temp_buffers overflow.

test4=# show temp_buffers ;
temp_buffers
--------------
8MB

test4=# create temp table t(a int, b int);

strace:

-------------------------------------

open("base/65677/t3_73931", O_RDONLY) = -1 ENOENT (No such file or
directory)
stat("base/65677", {st_mode=S_IFDIR|0700, st_size=12288, ...}) = 0
open("base/65677/t3_73931", O_RDWR|O_CREAT|O_EXCL, 0600) = 6
open("base/65677/12828_fsm", O_RDWR) = 8
lseek(8, 0, SEEK_END) = 24576
open("base/65677/12958_fsm", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 24576
open("base/65677/12851_fsm", O_RDWR) = 12
lseek(12, 0, SEEK_END) = 24576
open("base/65677/12840_fsm", O_RDWR) = 13
lseek(13, 0, SEEK_END) = 24576
open("base/65677/12840", O_RDWR) = 14
lseek(14, 0, SEEK_END) = 360448
close(6) = 0

----------------------------------------------------------

test4=# INSERT INTO t (a, b) SELECT NULL, i FROM generate_series(1,1000) i;
-------------------------------------------------------

open("base/65677/t3_73931_fsm", O_RDWR) = -1 ENOENT (No such file or
directory)
open("base/65677/t3_73931", O_RDWR) = 15
lseek(15, 0, SEEK_END) = 0
lseek(15, 0, SEEK_END) = 0
write(15,
"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
8192) = 8192

---------------------------------------------------------------

test4=# select pg_size_pretty(pg_total_relation_size('t'));
pg_size_pretty
----------------
64 kB
(1 row)

Postgres filling relation file with nulls page by page. Isn`t that just
kind of killing the whole idea of temp tables?

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company