Re: [GENERAL] fun fact about temp tables
Grigory Smolkin 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] fun fact about temp tables
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? On 08/05/2016 05:51 PM, Tom Lane wrote: Grigory Smolkin 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
Re: [GENERAL] fun fact about temp tables
On 05.08.2016 17:51, Tom Lane wrote: Grigory Smolkin 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] fun fact about temp tables
Grigory Smolkin 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 ... 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] fun fact about temp tables
Alex Ignatov 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] fun fact about temp tables
I can get error anyway on first select because of hint bit write. no? On 08/05/2016 06:54 PM, Tom Lane wrote: Alex Ignatov 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
Re: [GENERAL] fun fact about temp tables
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] fun fact about temp tables
On 08/05/2016 07:18 PM, Andrew Sullivan wrote: 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
Re: [GENERAL] fun fact about temp tables
On 05.08.2016 18:54, Tom Lane wrote: Alex Ignatov 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general