Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> CREATE GLOBAL TEMP TABLE blah INHERITS myhugetable;
I don't think you can have your temp table inherit from a real table. That
would make your real table have temp table semantics. Ie, records in it will
spontaneously disappear on reboot.
But you could use "LIKE" to define the table which I think does what you want
here.
Partitioned table support would eventually include the ability to take the
table (once you've done the untempify thing below) and move it into the
partitioned table as a partition.
> -- Load data, munge, etc... no xlog traffic
> -- If system crashes here, nothing to recover
>
> ALTER TABLE blah ALTER SCHEMA TO public; -- untempify
Personally I don't like the idea of tying temporary table status with the
schema. Is this in the standard? It seems to me that you should be able to
create temporary tables in whatever schema you want. People sometimes use
schemas to separate different modules of their application. It's strange to
force them to share a single namespace for temporary tables.
I would think untempify should be a separate special command, not some hidden
feature in ALTER SCHEMA. It makes it less surprising when someone reads the
code. It also makes it more likely someone who needs the functionality will
find it.
ALTER TABLE blah SET NOT TEMPORARY
This also raises the question of whether it should be possible to do:
ALTER TABLE blah SET TEMPORARY
and when it should be possible. At a first cut, any table that isn't involved
in any foreign key relationships and isn't part of any inherited table
structure should be eligible.
Come to think of it my database would benefit from this feature. I have cache
tables I rebuild nightly. They have to be visible from every session but I
don't care about losing them on a crash since they can always be rebuilt. As
it stands they inflate the daily backups, and if I were using PITR the nightly
build would flood the PITR logs with tons of useless data. Making them global
temporary tables would let me reduce the backup size, reduce PITR volume, and
speed up the nightly cache build in one stroke.
--
greg