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