Thread: pg_toast_temp_xx AND pg_temp_xx SCHEMAS
Hi guys, In one of my databases (8.3.6) I have around 25 schemas with names like these. There seem to appear a couple of new ones every now and then. The only reference I found was this: --- http://www.postgresql.org/docs/8.3/static/release-8-3.html * Place temporary tables' TOAST tables in special schemas named pg_toast_temp_nnn (Tom) This allows low-level code to recognize these tables as temporary, which enables various optimizations such as not WAL-logging changes and using local rather than shared buffers for access. This also fixes a bug wherein backends unexpectedly held open file references to temporary TOAST tables. --- Could anyone point me out some documentation about what these schemas mean? Should I be worried? Anything I should do about it? Thanks, Fernando
"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> Could anyone point me out some documentation about what these schemas mean?
They're just there to hold temporary tables (so that the names of such
tables don't conflict with any non-temporary tables). The reason they
seem to accumulate is we only create one when needed, and there's one
for each concurrently executing backend if it creates any temp tables.
> Should I be worried? Anything I should do about it?
No, and no.
regards, tom lane
> -----Mensaje original----- > De: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > ... and there's one for each concurrently > executing backend if it creates any temp tables. > That explains the growth I noticed as backends have been incremented recently. Thanks!!
> They're just there to hold temporary tables (so that the names of such
> tables don't conflict with any non-temporary tables). The reason they
> seem to accumulate is we only create one when needed, and there's one
> for each concurrently executing backend if it creates any temp tables.
Hm, do they get garbage-collected in any way? I have several such schemes that won't disappear even though no other sessions but one is there.
On a side note: I can drop temporary tables of other sessions via "DROP pg_temp_xx.table" (although I can't seem to affect table data seen from other session). Is there any security issues/gotchas here?
> tables don't conflict with any non-temporary tables). The reason they
> seem to accumulate is we only create one when needed, and there's one
> for each concurrently executing backend if it creates any temp tables.
Hm, do they get garbage-collected in any way? I have several such schemes that won't disappear even though no other sessions but one is there.
On a side note: I can drop temporary tables of other sessions via "DROP pg_temp_xx.table" (although I can't seem to affect table data seen from other session). Is there any security issues/gotchas here?
Vyacheslav Kalinin <vka@mgcp.com> writes:
>> They're just there to hold temporary tables (so that the names of such
>> tables don't conflict with any non-temporary tables). The reason they
>> seem to accumulate is we only create one when needed, and there's one
>> for each concurrently executing backend if it creates any temp tables.
> Hm, do they get garbage-collected in any way? I have several such schemes
> that won't disappear even though no other sessions but one is there.
No, that would just be a waste of cycles since they'll be needed again
eventually. The steady-state situation should be that you have
max_connections of them, more or less, if you ever get up to
max_connections live connections.
> On a side note: I can drop temporary tables of other sessions via "DROP
> pg_temp_xx.table" (although I can't seem to affect table data seen from
> other session). Is there any security issues/gotchas here?
If you can do that without being superuser, I'd take an interest.
Otherwise the answer is "don't do that".
regards, tom lane