Обсуждение: [GENERAL] Means to emulate global temporary table
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)
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
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 <http://www.mstarlabs.com>) -- Adrian Klaver adrian.klaver@aklaver.com
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.
On 01/11/2017 05:30 PM, Ian Lewis wrote: Ccing list > On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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 > <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 <http://www.mstarlabs.com>) -- Adrian Klaver adrian.klaver@aklaver.com
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)
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.
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.
On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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/doc
s/9.6/static/sql-createfunctio n.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)
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
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.
On Wed, Jan 11, 2017 at 5:36 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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)
> On Jan 11, 2017, at 7:02 PM, David G. Johnston <david.g.johnston@gmail.com> 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 withoutthe 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 tozero. Cheers, Steve
On Wed, 11 Jan 2017 15:23:10 -0800, John R Pierce
<pierce@hogranch.com> 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
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
Hi
2017-01-12 10:06 GMT+01:00 Karsten Hilbert <Karsten.Hilbert@gmx.net>:
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.
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
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
On 01/12/2017 12:37 AM, George Neuner wrote:
> On Wed, 11 Jan 2017 15:23:10 -0800, John R Pierce
> <pierce@hogranch.com> 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.klaver@aklaver.com