[GENERAL] fun fact about temp tables

[Grigory Smolkin]

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?

[Tom Lane]

Post by Grigory Smolkin
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.

Sure. Just like it reserves space for ordinary tables right away,
long before there's any need to push the data out of shared_buffers.
Otherwise, you might find yourself having to throw an "out of disk
space" error after having already committed the relevant INSERTs.

regards, tom lane

[Grigory Smolkin]

Thank you for your answer.

But it`s temporary table so it`s equal to saying 'I don`t care about
this data' and I can get 'out of disk space' regardless of using
temporary tables.

What are we winning here?

Post by Tom Lane

Post by Grigory Smolkin
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.

Sure. Just like it reserves space for ordinary tables right away,
long before there's any need to push the data out of shared_buffers.
Otherwise, you might find yourself having to throw an "out of disk
space" error after having already committed the relevant INSERTs.
regards, tom lane

[Alex Ignatov]

Post by Tom Lane

Post by Grigory Smolkin
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.

Sure. Just like it reserves space for ordinary tables right away,
long before there's any need to push the data out of shared_buffers.
Otherwise, you might find yourself having to throw an "out of disk
space" error after having already committed the relevant INSERTs.
regards, tom lane

How about out of space when we filling WAL files? Just rollback and
nothing else. We can fall in this with space reservation for WAL files.

In this situation with temp table we reserved space long before we ever
need it if we have size of temp table < temp_buffer.
Why not just rollback transaction in "Out of space" situation? With
this preliminary reservation we use HDD resource but in fact we dont
need it.
In situation with high rate creation of temp table, we saturate HDD
resource with "just in case" reservation.

Have we any other explanation except "out of space" problem? May be
there is some fundamental things tied with that?

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

[Tom Lane]

Post by Grigory Smolkin
Thank you for your answer.
But it`s temporary table so it`s equal to saying 'I don`t care about
this data' and I can get 'out of disk space' regardless of using
temporary tables.
What are we winning here?

Sane behavior. Would you really want this:

=> CREATE TEMP TABLE foo(...);
CREATE TABLE
=> INSERT INTO foo SELECT lots-o-rows;
INSERT nnnn
... much later ...
=> SELECT * FROM unrelated_temp_table;
ERROR: out of disk space

If we might suffer out-of-disk-space while flushing a buffer, that's
what we'd risk. So we allocate the disk space before accepting the
INSERT in the first place.

regards, tom lane

[Tom Lane]

Post by Alex Ignatov

Post by Tom Lane
Sure. Just like it reserves space for ordinary tables right away,
long before there's any need to push the data out of shared_buffers.
Otherwise, you might find yourself having to throw an "out of disk
space" error after having already committed the relevant INSERTs.

How about out of space when we filling WAL files?

What about it? That will be reported before committing, too.

What Grigory wants would imply committing and then sometime later
saying "oh, wait ... remember that data we told you we'd committed?
We lied."

Temp tables do indeed disappear at session end (and a fortiori after
a crash), but that doesn't create an excuse for them not to have
normal transactional behavior within the session.

regards, tom lane

[Grigory Smolkin]

I can get error anyway on first select because of hint bit write. no?

Post by Tom Lane

Post by Alex Ignatov

Post by Tom Lane
Sure. Just like it reserves space for ordinary tables right away,
long before there's any need to push the data out of shared_buffers.
Otherwise, you might find yourself having to throw an "out of disk
space" error after having already committed the relevant INSERTs.

How about out of space when we filling WAL files?

What about it? That will be reported before committing, too.
What Grigory wants would imply committing and then sometime later
saying "oh, wait ... remember that data we told you we'd committed?
We lied."
Temp tables do indeed disappear at session end (and a fortiori after
a crash), but that doesn't create an excuse for them not to have
normal transactional behavior within the session.
regards, tom lane

[Andrew Sullivan]

But it`s temporary table so it`s equal to saying 'I don`t care about this
data' and I can get 'out of disk space' regardless of using temporary
tables.
What are we winning here?

Surely, that the transaction operates in a predictable way? A temp
table doesn't say, "I don't care about this data," it says, "I don't
care about this data over the long haul." I've had lots of data go
through temp tables that I really really wanted to get into some other
place later, and it'd suck if the transaction failed half way through
because it turns out there's nowhere to put the data I've just staged.

A

[Grigory Smolkin]

Post by Andrew Sullivan

But it`s temporary table so it`s equal to saying 'I don`t care about this
data' and I can get 'out of disk space' regardless of using temporary
tables.
What are we winning here?

Surely, that the transaction operates in a predictable way? A temp
table doesn't say, "I don't care about this data," it says, "I don't
care about this data over the long haul." I've had lots of data go
through temp tables that I really really wanted to get into some other
place later, and it'd suck if the transaction failed half way through
because it turns out there's nowhere to put the data I've just staged.
A

But in that case you loose your data is case of power outage, deadlock
or network problem.
As it seems to me you can either 'care about your data' and use regular
tables, protected by wal, or don`t and use temp tables.
What am trying to understand, does temp tables really worth that many
disk operations? First we create empty file, then reserve space for it
and then we
write data in case of temp_buffers overflow. If there are many temp
tables it`s starting to eat a lot of I/O.
Wouldn`t it be more effective to create file for temp table on demand?
I think for most temp tables operations temp_buffers memory will be enough.

[Alex Ignatov]

Post by Tom Lane

Post by Alex Ignatov

Post by Tom Lane
Sure. Just like it reserves space for ordinary tables right away,
long before there's any need to push the data out of shared_buffers.
Otherwise, you might find yourself having to throw an "out of disk
space" error after having already committed the relevant INSERTs.

How about out of space when we filling WAL files?

What about it? That will be reported before committing, too.
What Grigory wants would imply committing and then sometime later
saying "oh, wait ... remember that data we told you we'd committed?
We lied."
Temp tables do indeed disappear at session end (and a fortiori after
a crash), but that doesn't create an excuse for them not to have
normal transactional behavior within the session.
regards, tom lane

If temp table fits in temp_buffer why do we have to reserve disk space
for that table?

If we commit after filling temp table ok=> Not enough temp_buffers for
the new one temp table write the first one to disk=> Not enough space
for temp file ok - our system in any way cant work further.

Cant see any problems in writing temp table data to disk only when
temp_buffer is full.

Any arguments against that behavior?

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company