fun fact about temp tables

[Grigory Smolkin <g.smolkin@xxxxxxxxxxxxxx>]

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