Re: [GENERAL] Means to emulate global temporary table

[Ian Lewis]

On Wed, Jan 11, 2017 at 5:36 PM, Adrian Klaver 
wrote:

>
> So what is the relationship of clients to sessions?

Most of our client applications use one session. But, a few use multiple
sessions, largely to support threaded access to the database. In our
current setup, a session connection may only be used by one thread. So, if
you want another thread to access the database you must have it open its
own separate session.

When looking at the content of a calculated result in a global temporary
table, however, we only ever use one session. And, we have no choice in
that as the results are per session.


>
>> Well you where saying that having a client create a table would result in
>> it having the clients permissions instead of the servers. Doing the table
>> creation through a function with SECURITY INVOKER would allow you to
>> 'shape' the permissions.
>>
>
Sorry, I was not completely clear in my first message. By "ownership" I
meant ownership of the table data structure in a programming sense, not
ownership of the table content. That is, I do not want the client to know
that the global temporary table structure have certain fields of certain
data types. The table and field definitions belong with the code that
manipulates the table, and all that code runs on our server. On the client
we just have presentation code that displays the content of the table,
nothing that needs to really understand the tables structure.

In our current use, security is handled by restricting who can call the
manipulation functions that populate the global temporary tables. It is
those functions that look at secure data and produced derived results in
the global temporary table. The global temporary table itself need have no
special access restrictions. If you cannot call the population services the
table never contains any data. The actual table structure itself (field
definitions, keys) matters not at all and has no security issues associated
with it.

Ian Lewis (www.mstarlabs.com)

Re: [GENERAL] Means to emulate global temporary table

[Adrian Klaver]

On 01/12/2017 12:37 AM, George Neuner wrote:

On Wed, 11 Jan 2017 15:23:10 -0800, John R Pierce
 wrote:


On 1/11/2017 2:07 PM, Ian Lewis wrote:

I am working on porting from an SQL Anywhere server that has support
for general temporary tables. It appears that PostgreSQL does not have
such support.


postgres temporary tables are either local to a transaction, or to a
connection/session, and are automatically deleted when the transaction
or session terminates.

how do these 'general temporary tables' differ from regular tables that
you create on demand, then delete when you're done with them?


SQL Anywhere has a couple of interesting twists on temporary tables.

First, it allows temporary tables to be defined as part of the
database schema, and to have them implicitly instantiated upon the
first mention in a session.  It is not necessary to issue a "create"
call before using the table.

Second, it allows temporary tables to be _per_user_ ("global") in
addition to per connection ("local").  Global temp tables are shared
by simultaneous connections from the same user - once created they
persist until the last connection by the owning user is closed.


Aah, now I see, thanks for this.



George






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[Berend Tober]

Karsten Hilbert wrote:

On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote:


I don't see where "call a setup function immediately after connecting"


Sounds like a "login trigger", more generally an ON CONNECT
event trigger, which we don't have at the moment as far as I
know.

One of the main arguments against it was that a failing
trigger function might prevent all access while the suggested
solution to that (I think by Tom Lane) was to auto-disable ON
CONNECT triggers when starting up as --single.



I'll take some credit for a partial solution suggestion:


https://www.postgresql.org/message-id/47D7DACD.9000304%40ct.metrocast.net


and pass most of the credit back to you for a better one:


https://www.postgresql.org/message-id/20080312140850.GA3882%40merkur.hilbert.loc



While the notion of an ON CONNECT trigger seems interesting, the rest of that discussion thread had 
a lot of good points about challenges to successfully implementing this idea.


-- B




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[Pavel Stehule]

Hi

2017-01-12 10:06 GMT+01:00 Karsten Hilbert :

> On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote:
>
> > I don't see where "call a setup function immediately after connecting"
>
> Sounds like a "login trigger", more generally an ON CONNECT
> event trigger, which we don't have at the moment as far as I
> know.
>
> One of the main arguments against it was that a failing
> trigger function might prevent all access while the suggested
> solution to that (I think by Tom Lane) was to auto-disable ON
> CONNECT triggers when starting up as --single.
>
> Unfortunately, I don't know what came of it by now.
>

https://github.com/okbob/session_exec

Regards

Pavel


> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Means to emulate global temporary table

[Karsten Hilbert]

On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote:

> I don't see where "call a setup function immediately after connecting"

Sounds like a "login trigger", more generally an ON CONNECT
event trigger, which we don't have at the moment as far as I
know.

One of the main arguments against it was that a failing
trigger function might prevent all access while the suggested
solution to that (I think by Tom Lane) was to auto-disable ON
CONNECT triggers when starting up as --single.

Unfortunately, I don't know what came of it by now.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[George Neuner]

On Wed, 11 Jan 2017 15:23:10 -0800, John R Pierce
 wrote:

>On 1/11/2017 2:07 PM, Ian Lewis wrote:
>> I am working on porting from an SQL Anywhere server that has support 
>> for general temporary tables. It appears that PostgreSQL does not have 
>> such support.
>
>postgres temporary tables are either local to a transaction, or to a 
>connection/session, and are automatically deleted when the transaction 
>or session terminates.
>
>how do these 'general temporary tables' differ from regular tables that 
>you create on demand, then delete when you're done with them?

SQL Anywhere has a couple of interesting twists on temporary tables.

First, it allows temporary tables to be defined as part of the
database schema, and to have them implicitly instantiated upon the
first mention in a session.  It is not necessary to issue a "create"
call before using the table.

Second, it allows temporary tables to be _per_user_ ("global") in
addition to per connection ("local").  Global temp tables are shared
by simultaneous connections from the same user - once created they
persist until the last connection by the owning user is closed.

George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[Steve Atkins]

> On Jan 11, 2017, at 7:02 PM, David G. Johnston  
> wrote:
> 
> ​"throughout" mustn't mean "by other sessions" or this becomes unwieldy.
> 
> Here's a mock-up:
> 
> CREATE TABLE template_table ();
> CREATE VIEW view_over_my_template_instance AS SELECT * FROM 
> my_instance_of_template_table; --fails if done here without the desired 
> feature
> 
> In a given session:
> 
> CREATE TEMP TABLE my_instance_of_template_table LIKE template_table;
> SELECT * FROM view_over_my_template_table; -- returns only this session's 
> temp table data
> 
> Other sessions can simultaneously execute the same SELECT * FROM view_over_* 
> and get their own results.
> 
> The goal is to avoid having to CREATE TEMP TABLE within the session but 
> instead be able to do:
> 
> CREATE GLOBAL TEMP TABLE my_instance_of_template_table LIKE template_table;
> 
> And have the CREATE VIEW not fail and the session behavior as described.

Would this differ in any user-visible way from what you'd have if you executed 
at the start of each session:

CREATE TEMPORARY TABLE my_instance_of_template_table LIKE template_table;
CREATE TEMPORARY VIEW view_over_my_template_instance AS SELECT * FROM 
my_instance_of_template_table;

There'd be a small amount of session startup overhead, but that could be 
handled at the pooler level and amortized down to zero.

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[David G. Johnston]

On Wed, Jan 11, 2017 at 7:51 PM, John R Pierce  wrote:

> On 1/11/2017 6:39 PM, Ian Lewis wrote:
>
>> 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?
>>
>
> postgres temporary tables are only visible to the session that creates
> them. all kind of wierdness would happen if they were somehow visible
> outside that session, for instance what if another session is accessing one
> of these hypothetical things, when the session that creates the temp table
> exits ?   and, how do you resolve name conflicts?  if session 1 creates
> temp table ABC, and session 2 creates temp table ABC, how would session 3
> know which one to use? conversely, if each session creates unique
> names, they'd have to build every sql statement from string fragments, this
> is considered poor practice, and how would session 3 know what unique name
> to use for one of these other sessions shared temporary tables? all
> very confusing.
>
>
> so I'm still not clear here what it is you expect these 'global temp
> tables' to do, and how they are supposed to behave?


​"throughout" mustn't mean "by other sessions" or this becomes unwieldy.

Here's a mock-up:

CREATE TABLE template_table ();
CREATE VIEW view_over_my_template_instance AS SELECT * FROM
my_instance_of_template_table; --fails if done here without the desired
feature

In a given session:

CREATE TEMP TABLE my_instance_of_template_table LIKE template_table;
SELECT * FROM view_over_my_template_table; -- returns only this session's
temp table data

Other sessions can simultaneously execute the same SELECT * FROM
view_over_* and get their own results.

The goal is to avoid having to CREATE TEMP TABLE within the session but
instead be able to do:

CREATE GLOBAL TEMP TABLE my_instance_of_template_table LIKE template_table;

And have the CREATE VIEW not fail and the session behavior as described.

David J.



​

Re: [GENERAL] Means to emulate global temporary table

[John R Pierce]

On 1/11/2017 6:39 PM, Ian Lewis wrote:
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? 


postgres temporary tables are only visible to the session that creates 
them. all kind of wierdness would happen if they were somehow 
visible outside that session, for instance what if another session is 
accessing one of these hypothetical things, when the session that 
creates the temp table exits ?   and, how do you resolve name 
conflicts?  if session 1 creates temp table ABC, and session 2 
creates temp table ABC, how would session 3 know which one to use? 
conversely, if each session creates unique names, they'd have to build 
every sql statement from string fragments, this is considered poor 
practice, and how would session 3 know what unique name to use for one 
of these other sessions shared temporary tables? all very confusing.



so I'm still not clear here what it is you expect these 'global temp 
tables' to do, and how they are supposed to behave?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[David G. Johnston]

On Wed, Jan 11, 2017 at 7:39 PM, Ian Lewis  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.​

Re: [GENERAL] Means to emulate global temporary table

[Ian Lewis]

On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver 
wrote:

So what makes them temporary as they seem to persist between sessions?


They are temporary in the sense that the content of the table is
per-session, just as a local temporary table would be. That is, each
session has its own independent data set. But, the table is defined and
accessible within the schema as a normal table would be.

While efficiency is not an issue in our usage, on our current server, they
are very efficient because they do not need to handle locking as a normal
table would do because only one session can access the data.



That can be handled with SECURITY DEFINER:
>
>
>
> https://www.postgresql.org/docs/9.6/static/sql-createfunction.html
>
> "EXTERNAL] SECURITY INVOKER
>
> [EXTERNAL] SECURITY DEFINER
>
> SECURITY INVOKER indicates that the function is to be executed with
> the privileges of the user that calls it. That is the default. SECURITY
> DEFINER specifies that the function is to be executed with the privileges
> of the user that created it.
>
> The key word EXTERNAL is allowed for SQL conformance, but it is
> optional since, unlike in SQL, this feature applies to all functions not
> only external one
>


I will look at this in more detail, but, on first reading, I do not quite
see how it helps.

Ian Lewis (www.mstarlabs.com)

Re: [GENERAL] Means to emulate global temporary table

[Ian Lewis]

>
> 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.
>
>>
>> > 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)
>

Re: [GENERAL] Means to emulate global temporary table

[Adrian Klaver]

On 01/11/2017 05:30 PM, Ian Lewis wrote:
Ccing list


On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver
> wrote:

So what makes them temporary as they seem to persist between sessions?


They are temporary in the sense that the content of the table is
per-session, just as a local temporary table would be. That is, each
session has its own independent data set. But, the table is defined and
accessible within the schema as a normal table would be.


So what is the relationship of clients to sessions?



While efficiency is not an issue in our usage, on our current server,
they are very efficient because they do not need to handle locking as a
normal table would do because only one session can access the data.


That can be handled with SECURITY DEFINER:

https://www.postgresql.org/docs/9.6/static/sql-createfunction.html

"EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER indicates that the function is to be
executed with the privileges of the user that calls it. That is
the default. SECURITY DEFINER specifies that the function is to
be executed with the privileges of the user that created it.

The key word EXTERNAL is allowed for SQL conformance, but it
is optional since, unlike in SQL, this feature applies to all
functions not only external ones.


"



I will look at this in more detail, but, on first reading, I do not
quite see how it helps.


Well you where saying that having a client create a table would result 
in it having the clients permissions instead of the servers. Doing the 
table creation through a function with SECURITY INVOKER would allow you 
to 'shape' the permissions.




Ian Lewis (www.mstarlabs.com )



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[David G. Johnston]

On Wed, Jan 11, 2017 at 5:38 PM, Adrian Klaver 
wrote:

> Because the tables are known, many different functions can access the
>> same tables during a session to manipulate the result set. And, because
>> the tables are global the client can see the results easily based on the
>> then-current table configuration on the server.
>>
>
> So what makes them temporary as they seem to persist between sessions?


​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.

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.

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.

David J.
​

Re: [GENERAL] Means to emulate global temporary table

[Adrian Klaver]

On 01/11/2017 02:07 PM, Ian Lewis wrote:

I am working on porting from an SQL Anywhere server that has support for
general temporary tables. It appears that PostgreSQL does not have such
support.

We use global temporary tables as a means to provide session-local
content to clients based on calculations run on the server.

That is, the client passes functions on the server configuration
information, and the server calculates results and places those results
in known global temporary table(s). The client then obtains the results
from the global temporary table to present to the user.

The user may then manipulate the configuration and we pass that
configuration back to the server through various functions on the
server. The appropriate function then manipulates the global temporary
table results allowing the client to see the new information.

Because the tables are known, many different functions can access the
same tables during a session to manipulate the result set. And, because
the tables are global the client can see the results easily based on the
then-current table configuration on the server.


So what makes them temporary as they seem to persist between sessions?



I do not see a way to emulate this kind of behavior using PostgreSQL
temporary tables. It appears that a script on the server has to create
the temporary table, or the client has to create it before calling the
server putting ownership of the table structure on the client rather
than the server where it belongs in our system.


That can be handled with SECURITY DEFINER:

https://www.postgresql.org/docs/9.6/static/sql-createfunction.html
"EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER indicates that the function is to be executed with 
the privileges of the user that calls it. That is the default. SECURITY 
DEFINER specifies that the function is to be executed with the 
privileges of the user that created it.


The key word EXTERNAL is allowed for SQL conformance, but it is 
optional since, unlike in SQL, this feature applies to all functions not 
only external ones.

"



I can easily see how to create and return a temporary table from a
single function. What I do not see is how we get multiple server-side
functions to manipulate the same temporary table.




Is there a conventional means to do something like this in PostgreSQL?
If so, is there documentation somewhere on how to manage access
to temporary tables across function calls from the client?

Ian Lewis (www.mstarlabs.com )



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Means to emulate global temporary table

[John R Pierce]

On 1/11/2017 2:07 PM, Ian Lewis wrote:
I am working on porting from an SQL Anywhere server that has support 
for general temporary tables. It appears that PostgreSQL does not have 
such support.


postgres temporary tables are either local to a transaction, or to a 
connection/session, and are automatically deleted when the transaction 
or session terminates.


how do these 'general temporary tables' differ from regular tables that 
you create on demand, then delete when you're done with them?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Means to emulate global temporary table

[Ian Lewis]

I am working on porting from an SQL Anywhere server that has support for
general temporary tables. It appears that PostgreSQL does not have such
support.

We use global temporary tables as a means to provide session-local content
to clients based on calculations run on the server.

That is, the client passes functions on the server configuration
information, and the server calculates results and places those results in
known global temporary table(s). The client then obtains the results from
the global temporary table to present to the user.

The user may then manipulate the configuration and we pass that
configuration back to the server through various functions on the server.
The appropriate function then manipulates the global temporary table
results allowing the client to see the new information.

Because the tables are known, many different functions can access the same
tables during a session to manipulate the result set. And, because the
tables are global the client can see the results easily based on the
then-current table configuration on the server.

I do not see a way to emulate this kind of behavior using PostgreSQL
temporary tables. It appears that a script on the server has to create the
temporary table, or the client has to create it before calling the server
putting ownership of the table structure on the client rather than the
server where it belongs in our system.

I can easily see how to create and return a temporary table from a single
function. What I do not see is how we get multiple server-side functions to
manipulate the same temporary table.

Is there a conventional means to do something like this in PostgreSQL? If
so, is there documentation somewhere on how to manage access to temporary
tables across function calls from the client?

Ian Lewis (www.mstarlabs.com)