On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:> The way I read this is that the OP wants to be able to write functions that target temporary tables. These functions all assume that said tables already exist so the functions themselves do not need to be concerned with their management. The OP would like to be able to define these tables as persistent objects in the database catalogs but in practice they behave as any other temporary table would. In effect, upon session startup, these tables would be created automatically by the backend without any client involvement.Yes. This is more or less correct, though I am quite certain that these tables underlying data store structures are not defined at session startup on our current server. The table structures are global within the catalog in exactly the same sense that a normal table is. They are used to create the table storage when needed.
> This seems a bit wasteful in terms of all those session/connections that don't care a whit about said temporary tables...so maybe I'm missing something here in the implementation.So, there is no startup work to create the tables for a session that does not use the tables. While I have no information on the actual implementation, the actual underlying store must be created at first use, or something like that. But, there definitely is no more per-session cost to those sessions that do not use the temporary tables than the cost of adding any extra table to the catalog.
Nice - definitely a contributing factor to why their implementation would seem non-trivial.
That is, I can define a function f_dosomething() that performs some operation on a relation atable that does not exist in the schema. I can then define the relation atable as a local temporary table in an initialization function, f_init(), say.Assuming I call f_init() then f_dosomething(), f_dosomething() will see the local temporary table defined in f_init() just as it would see any other table.
Yes.
Once defined, is a local temporary table also visible to clients as part of the schema?
The tables appear in pg_class and related catalog tables just like any other table - which is a primary source of catalog bloat.
For example, we can define a report on the table and it will show whatever results we have calculated for the current client session. The report editor does not need to figure out how to call a procedure to get the table definition. As far as any application is concerned the global temporary table is just a table defined in the schema.
Indeed :(
David J.
