Re: Means to emulate global temporary table

On Wed, Jan 11, 2017 at 7:39 PM, Ian Lewis <ilewis(at)mstarlabs(dot)com> wrote:

> On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)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.​