Re: [GENERAL] fun fact about temp tables

[Alex Ignatov]

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

Re: [GENERAL] fun fact about temp tables

[Grigory Smolkin]

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

[Andrew Sullivan]

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

[Grigory Smolkin]

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

[Tom Lane]

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

[Tom Lane]

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]

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]

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

[Tom Lane]

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

[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?




--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company