On Wed, Jan 11, 2017 at 7:39 PM, Ian Lewis <ile...@mstarlabs.com> wrote:
> On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston < >> david.g.johns...@gmail.com> 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.
