Re: [GENERAL] Means to emulate global temporary table

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

> I don't see where "call a setup function immediately after connecting" is that big a problem.  The client has to declare their intent to use said features - and that declaration causes normal temporary tables to spring into existence.  If the process functions are used without doing the first step the user will get an error about relation not found.  I suspect there may be search_path or language limitations to this approach but the complaint as written doesn't give enough detail about why our temporary tables are proving insufficient.

Does this mean that a local temporary table created in one function in a database is visible globally throughout the database for the duration of the session?

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.

If this is correct, it is at least a solution to the server side of what I am trying to replace.

Once defined, is a local temporary table also visible to clients as part of the schema?

The main remaining problem comes in the clients where we currently obtain the working (temporary) table structure from the global schema, which is nice because it means we can handle the working results exactly as we would handle any other table content. 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. Our current report editor has no way to define a report from a table that does not exist in some schema somewhere, and I am not even sure it is possible to get it to call a procedure before attempting to access the schema.

This may be a big problem for us. But, I do not see any obvious work around for it under PostgreSQL.

Ian Lewis (www.mstarlabs.com)