Thread: 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
Grigory Smolkin <g.smolkin@postgrespro.ru> writes:
> 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
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?
Grigory Smolkin <g.smolkin@postgrespro.ru> writes: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 Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On 05.08.2016 17:51, Tom Lane wrote: > Grigory Smolkin <g.smolkin@postgrespro.ru> writes: >> 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
Grigory Smolkin <g.smolkin@postgrespro.ru> writes:
> 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
Alex Ignatov <a.ignatov@postgrespro.ru> writes:
> On 05.08.2016 17:51, Tom Lane wrote:
>> 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
I can get error anyway on first select because of hint bit write. no?
Alex Ignatov <a.ignatov@postgrespro.ru> writes:On 05.08.2016 17:51, Tom Lane wrote: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 Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On Fri, Aug 05, 2016 at 06:02:08PM +0300, Grigory Smolkin wrote: > 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 -- Andrew Sullivan ajs@crankycanuck.ca
On Fri, Aug 05, 2016 at 06:02:08PM +0300, Grigory Smolkin wrote: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.
-- Grigory Smolkin Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On 05.08.2016 18:54, Tom Lane wrote: > Alex Ignatov <a.ignatov@postgrespro.ru> writes: >> On 05.08.2016 17:51, Tom Lane wrote: >>> 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