Re: [HACKERS] idea: global temp tables
Robert Haas wrote: On Thu, Apr 30, 2009 at 8:36 AM, Peter Eisentraut pete...@gmx.net wrote: The archives for this thread http://archives.postgresql.org//pgsql-hackers/2009-04/threads.php#01329 show a bunch of missing messages. ?Were they being stored in a temporary table? Anywhere, here is what I had meant to say but only got through to a few ... I am getting a lot of messages from this list out of order the last few days. I'll get later messages in a thread and then a day or two later I'll get the messages to which they were replies. When we get to a conclusion, would someone please add a TODO entry? Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com writes: Well I claim it's not just a nice bonus but is the difference between implementing something which falls technically within the standard's rules but fails to actually be useful for the standard's intended purpose. I agree with Kevin's objection that you are unfairly raising the bar for this feature by demanding a performance improvement to go along with a functionality change. The use-case for this feature is to simplify application logic by allowing apps to assume that a temp table exists without having to create it at the start of a session. That's particularly handy in connection-pooling scenarios, for instance. Currently, you have to have some sort of if exists check, and you pay just as much in catalog thrashing as you would if the feature was present without any catalog optimization. It would be great to find a way to avoid the catalog thrashing, but I completely disagree with a point of view that says we can't have this without solving that first. It's an improvement on the current state of affairs anyway. I've been thinking about Alvaro's idea of a separate smgr. If you had a single pg_class entry for all sessions but the smgr knew to store the actual data for it in a session-local file, either in a session-specific tablespace or using the same mechanism the temporary files use to direct data then the backend would basically never know it wasn't a regular table. 1. pg_statistic. 2. How you going to have transaction-safe behavior for things like TRUNCATE, if you don't have an updatable private catalog entry to keep the current relfilenode in? It could still use local buffers but it could use the global relcache, invalidation, locks, etc. Locks would be another big problem: if only smgr knows that different instances of the table are different, then different backends' locks would conflict, which would be Bad. This might not matter for simple read/update, but again TRUNCATE is a counterexample of something that is likely to be needed and should not cause cross-backend conflicts. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Any chance that some of these improvements could be applied to temp tables created with the PostgreSQL-specific syntax while we're at it? You mean the Postgres-specific behavior, no? Trying to support a table without *any* pre-existing catalog entries seems even harder than doing it without changing the pre-existing catalog entries. I can't say that I followed all of Greg's ideas, but it seemed that some of them related to cheaper ways to materialize the body of the temp table, as opposed to updating the system tables. That seemed like it might be orthogonal to the issue of persistent temp table definitions, and perhaps the ideas could help performance of all temp tables, including the PostgreSQL-specific variety. Being out of my depth on the technical issues he was discussing, I can't really do more than pose the question, however -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Wed, Apr 29, 2009 at 6:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@enterprisedb.com writes: Well I claim it's not just a nice bonus but is the difference between implementing something which falls technically within the standard's rules but fails to actually be useful for the standard's intended purpose. I agree with Kevin's objection that you are unfairly raising the bar for this feature by demanding a performance improvement to go along with a functionality change. I think having the feature is making a promise that we can't keep. Having a feature which meets the letter of the rules but fails to actually work as users have a right to expect is going to trap people unaware that they're writing code that works in testing but will never scale. The use-case for this feature is to simplify application logic by allowing apps to assume that a temp table exists without having to create it at the start of a session. That's particularly handy in connection-pooling scenarios, for instance. Currently, you have to have some sort of if exists check, and you pay just as much in catalog thrashing as you would if the feature was present without any catalog optimization. That seems like a trivial little annoyance. Spending effort fixing that that in a way that will only have to be replaced if we ever want to support using temporary tables for heavy oltp load is a waste of effort. It would be great to find a way to avoid the catalog thrashing, but I completely disagree with a point of view that says we can't have this without solving that first. It's an improvement on the current state of affairs anyway. Not if it promises something we can't deliver. My claim is that the whole point of having a persistent catalog definition is *precisely* to avoid the catalog thrashing and that's obvious to users who would be using this feature. This is just like the idea of prepared queries -- nowhere in the standard does it say that prepared queries have to cache the prepared plan and run any faster than non-prepared queries but if we didn't it would be somewhat broken. Now in that case planning queries is relativelyl cheap, but how happy do you think users would be if we supported prepared queries and they built their application around that fact only to discover that every time any session executed a PREPARE it created new records in catalog tables? We would be better off not supporting the command at all so at least users would know not to head down that dead-end path. 1. pg_statistic. 2. How you going to have transaction-safe behavior for things like TRUNCATE hm, truncate is a bit of a special case. I had been thinking that ddl would basically require preventing other backends from using the table at the same time. A lot of people don't realize truncate behaves like ddl but in any case I agree it sure seems like it would be important for temporary tables. I still maintain that this feature is not primarily about programmer convenience, but rather comes with an expectation that the schema definition is being given in advance so that the database doesn't have to incur the costs of issuing the ddl for every session. If we provide the interface but not the expected behaviour it could be worse than not having the interface at all. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner kevin.gritt...@wicourts.gov writes: Any chance that some of these improvements could be applied to temp tables created with the PostgreSQL-specific syntax while we're at it? You mean the Postgres-specific behavior, no? Trying to support a table without *any* pre-existing catalog entries seems even harder than doing it without changing the pre-existing catalog entries. It's something we've thought about before and failed to find a decent solution for. I think there was some discussion of allowing pg_class and other relevant tables to implicitly have session-local child tables that would hold session-local catalog entries (and be stored in the local bufmgr); but AFAIR we never worked all the bugs out of the concept. The need for several tables to be created on disk to materialize a single temp table currently causes performance problems in some contexts. I don't think the updates to the system tables have the same magnitude of performance hit as creating these tables, especially if write barriers are on. True; it's pure supposition that avoiding the catalog thrashing is actually important in context of everything else that has to happen. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Peter Eisentraut pete...@gmx.net wrote: There are actually two orthogonal properties at work here: How the table is visible with respect to modules (LOCAL/GLOBAL) and whether the table disappears at the end of the session (currently yes, proposed new behavior optionally no). Pavel's request and the standard always have the temporary table disappearing at the end of the session; the second question for these is whether the *definition* of the table disappears at the end of the session, or remains in the schema to generate an instance for another session on demand. There was a digression about a possible feature other than what Pavel requested, which is not defined in the standard, which I referred to as permanent work tables, which would be distinguished from normal tables only by the fact that no effort would be made to log the data for recovery, and perhaps the table should be truncated on PostgreSQL startup. If we want to pursue that discussion, it should probably be on a different thread. On the matter of LOCAL/GLOBAL, I think the correct thing to do is to reject LOCAL and accept GLOBAL as equivalent to the default. +1 And then invent a separate setting, say EPHEMERAL/PERSISTENT that controls the behavior that Pavel requested. While I probably wouldn't have chosen the syntax that the standard did, the SQL spec does specify a syntax to do what Pavel requested. I don't think it's so bad that we should ignore it and invent our own alternative. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Wed, Apr 29, 2009 at 4:24 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I understand not everyone has a need for such a thing, but isn't that true of most features in PostgreSQL? Well I think implementing a feature which only works if it's used at low transaction rates as a convenience wrapper for a single command seems like a lot of work for little gain. If you're saying we can implement the standard's global temporary tables in a way that performs better than current temporary tables, that's cool. That would be a nice bonus in addition to the application programmer convenience and having another tick-mark on the standards compliance charts. Well I claim it's not just a nice bonus but is the difference between implementing something which falls technically within the standard's rules but fails to actually be useful for the standard's intended purpose. I claim there's an implied expectation that by predefining these schema definitions you eliminate the overhead of DDL creating and dropping tables on the fly. That you can basically explicitly code up algorithms which might be too complex or detailed for an SQL query which get executed as high transaction rate DML using temporary storage just as our SQL engine uses it in materialize nodes and sort nodes. Just to give a real-world example, think of web pages that do paging of moderately complex query results. You often want to calculate the total number of matches and then also return a subset of those matches. Currently the only practical way to do it is to execute the query twice.Creating a temporary table for this purpose would transform your simple read-only DML into a complex DDL operation you can't expose to the masses without a lot of precautions and extra consequences. Do you think that's feasible? If not, the feature would be useful to some with the same performance that temporary tables currently provide. I've been thinking about Alvaro's idea of a separate smgr. If you had a single pg_class entry for all sessions but the smgr knew to store the actual data for it in a session-local file, either in a session-specific tablespace or using the same mechanism the temporary files use to direct data then the backend would basically never know it wasn't a regular table. It could still use local buffers but it could use the global relcache, invalidation, locks, etc. I think we would hav eto take a session-level access lock as soon as we put any data in our local store. And each DDL operation would have to be visited to see whether it needs special behaviour for locally stored tables. I suspect most of them will only be able to be handled if there are no active sessions using the table so they'll basically be no-ops except for the catalog changes. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Tuesday 28 April 2009 19:38:25 Tom Lane wrote: One thing I just noticed is that the spec does not consider GLOBAL/LOCAL to be optional --- per spec you *must* write one or the other in front of TEMPORARY. So we could adopt the view that omitting this keyword implies our current non-spec behavior (which is far too useful to give up, spec compliance or no) while writing one or the other selects the spec behavior. However, if we're going to do that then we should start throwing warnings for use of the keywords, preferably before the release in which they actually start doing something different. There are actually two orthogonal properties at work here: How the table is visible with respect to modules (LOCAL/GLOBAL) and whether the table disappears at the end of the session (currently yes, proposed new behavior optionally no). We should have two separate settings for these. On the matter of LOCAL/GLOBAL, I think the correct thing to do is to reject LOCAL and accept GLOBAL as equivalent to the default. And then invent a separate setting, say EPHEMERAL/PERSISTENT that controls the behavior that Pavel requested. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Dne 28.04.09 16:59, Alvaro Herrera napsal(a): Pavel Stehule escribió: Maybe we could make this work by fiddling with a different smgr -- on it, smgr_sync would be a noop, as would smgr_immedsync, and we could kludge something up to truncate relations during recovery. Maybe set path like db oid/pg_temp/session id/table oid do the work for this kind of table. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Peter Eisentraut pete...@gmx.net wrote: On the matter of LOCAL/GLOBAL, I think the correct thing to do is to reject LOCAL and accept GLOBAL as equivalent to the default. Oops. Read that too quickly before my first reply. I think that we should, as Tom said, warn on *both* for 8.4, and treat GLOBAL per the standard in 8.5. I don't know whether we can do anything useful with LOCAL for 8.5, or whether it should be rejected at that point. Sorry for my confusion about what you were saying. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
The archives for this thread http://archives.postgresql.org//pgsql-hackers/2009-04/threads.php#01329 show a bunch of missing messages. Were they being stored in a temporary table? Anywhere, here is what I had meant to say but only got through to a few ... On Tuesday 28 April 2009 19:38:25 Tom Lane wrote: One thing I just noticed is that the spec does not consider GLOBAL/LOCAL to be optional --- per spec you *must* write one or the other in front of TEMPORARY. So we could adopt the view that omitting this keyword implies our current non-spec behavior (which is far too useful to give up, spec compliance or no) while writing one or the other selects the spec behavior. However, if we're going to do that then we should start throwing warnings for use of the keywords, preferably before the release in which they actually start doing something different. There are actually two orthogonal properties at work here: How the table is visible with respect to modules (LOCAL/GLOBAL) and whether the table disappears at the end of the session (currently yes, proposed new behavior optionally no). We should have two separate settings for these. On the matter of LOCAL/GLOBAL, I think the correct thing to do is to reject LOCAL and accept GLOBAL as equivalent to the default. And then invent a separate setting, say EPHEMERAL/PERSISTENT that controls the behavior that Pavel requested. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
James Mansion ja...@mansionfamily.plus.com wrote: Wouldn't it be cleaner just to defer creation of real files to support the structures associated with a temp table until it i snecessary to spill the data from the backend's RAM? This data doesn't need to be in shared memory and the tables and data aren't visible to any other session, so can't they run out of RAM most of the time (or all the time if the data in them is short lived)? See this thread for a discussion of the idea: http://archives.postgresql.org/pgsql-hackers/2008-11/msg00342.php -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Thu, Apr 30, 2009 at 8:36 AM, Peter Eisentraut pete...@gmx.net wrote: The archives for this thread http://archives.postgresql.org//pgsql-hackers/2009-04/threads.php#01329 show a bunch of missing messages. Were they being stored in a temporary table? Anywhere, here is what I had meant to say but only got through to a few ... I am getting a lot of messages from this list out of order the last few days. I'll get later messages in a thread and then a day or two later I'll get the messages to which they were replies. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner wrote: contexts. I don't think the updates to the system tables have the same magnitude of performance hit as creating these tables, especially if write barriers are on. Wouldn't it be cleaner just to defer creation of real files to support the structures associated with a temp table until it i snecessary to spill the data from the backend's RAM? This data doesn't need to be in shared memory and the tables and data aren't visible to any other session, so can't they run out of RAM most of the time (or all the time if the data in them is short lived)? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com wrote: creating/deleting a few dozen rows in the system catalogs shouldn't really be something that autovacuum can't deal with. I don't see why it's limited to a few dozen rows. Moderately busy web sites these days count their traffic in hundreds of page views per second. Sure. We're there. And many of those hits run ten to twenty queries. We'd be insane to get a new connection for each one rather than use a connection pool; and this overhead only occurs once per referenced table per connection. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com wrote: On Wed, Apr 29, 2009 at 6:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: I agree with Kevin's objection that you are unfairly raising the bar for this feature by demanding a performance improvement to go along with a functionality change. I think having the feature is making a promise that we can't keep. Having a feature which meets the letter of the rules but fails to actually work as users have a right to expect is going to trap people unaware that they're writing code that works in testing but will never scale. But the scaling issues are there already with temp tables. This actually makes it better, not worse, because the table can be materialized once per session, not once per request. The use-case for this feature is to simplify application logic by allowing apps to assume that a temp table exists without having to create it at the start of a session. That's particularly handy in connection-pooling scenarios, for instance. Currently, you have to have some sort of if exists check, and you pay just as much in catalog thrashing as you would if the feature was present without any catalog optimization. That seems like a trivial little annoyance. And a potentially large performance booster. It would be great to find a way to avoid the catalog thrashing, but I completely disagree with a point of view that says we can't have this without solving that first. It's an improvement on the current state of affairs anyway. Not if it promises something we can't deliver. My claim is that the whole point of having a persistent catalog definition is *precisely* to avoid the catalog thrashing and that's obvious to users who would be using this feature. I've re-read the spec on this several times now, and I can't see where that is implied. I still maintain that this feature is not primarily about programmer convenience, but rather comes with an expectation that the schema definition is being given in advance so that the database doesn't have to incur the costs of issuing the ddl for every session. I think many would be satisfied not to have those costs on every *request* on the connection. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
2009/4/29 Tom Lane t...@sss.pgh.pa.us:
Greg Stark st...@enterprisedb.com writes:
Well I claim it's not just a nice bonus but is the difference between
implementing something which falls technically within the standard's
rules but fails to actually be useful for the standard's intended
purpose.
I agree with Kevin's objection that you are unfairly raising the bar
for this feature by demanding a performance improvement to go along
with a functionality change. The use-case for this feature is to
simplify application logic by allowing apps to assume that a temp
table exists without having to create it at the start of a session.
That's particularly handy in connection-pooling scenarios, for instance.
Currently, you have to have some sort of if exists check, and you
pay just as much in catalog thrashing as you would if the feature
was present without any catalog optimization.
exactly
It would be great to find a way to avoid the catalog thrashing,
but I completely disagree with a point of view that says we can't
have this without solving that first. It's an improvement on the
current state of affairs anyway.
I've been thinking about Alvaro's idea of a separate smgr. If you had
a single pg_class entry for all sessions but the smgr knew to store
the actual data for it in a session-local file, either in a
session-specific tablespace or using the same mechanism the temporary
files use to direct data then the backend would basically never know
it wasn't a regular table.
1. pg_statistic.
2. How you going to have transaction-safe behavior for things like
TRUNCATE, if you don't have an updatable private catalog entry to keep
the current relfilenode in?
It could still use local buffers but it could use the global relcache,
invalidation, locks, etc.
Locks would be another big problem: if only smgr knows that different
instances of the table are different, then different backends' locks
would conflict, which would be Bad. This might not matter for simple
read/update, but again TRUNCATE is a counterexample of something that
is likely to be needed and should not cause cross-backend conflicts.
I though about some techniques for elimination changes in pg_class and
pg_statistic. Teoreticly, we could to overwrite some columns (or
complete rows) from these tables via stored values in memory. My last
(and not sucessfull) prototype was based on some alchymy over
syscache. It was wrong way.
Maybe we could do some like
int get_relpages(oid)
{
tuple = read_tuple_pg_class(oid);
if is_global(tuple)
{
tuple2 = find_global(oid);
if (tuple2 == NULL)
{
store_global(tuple);
return relpages(tuple);
}
else
return relpages(tuple2);
}
else
return relpages(tuple);
}
But question?
about MVCC?
Is necessary to use MVCC on pg_statistic and some columns from pg_proc?
regards
Pavel Stehule
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Pavel Stehule escribió: But question? about MVCC? Is necessary to use MVCC on pg_statistic and some columns from pg_proc? Right now, we use MVCC everywhere because we have no choice. But there are some things that would be better off not using MVCC; and indeed we use a hack to make things that way in certain, very limited cases (see callers of heap_update_inplace). Years ago I proposed a patch (search for pg_class_nt and/or pg_ntclass) that created a catalog for non-MVCC attributes, with an eye on extending it to more attributes. The use case for those other attributes had fatal flaws so it went nowhere, but perhaps we can give the idea another whirl now. For example I think it would be possible to implement read-only partitions that don't need vacuuming using that idea. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com wrote: On Wed, Apr 29, 2009 at 7:57 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: But the scaling issues are there already with temp tables. This actually makes it better, not worse, because the table can be materialized once per session, not once per request. Currently you have to issue CREATE TABLE and associated DDL manually. That makes it clear to the user that they're executing DDL and should expect it to behave like DDL. I don't understand what you mean by a cost once per request. You only have to create the temporary table on the first request. If you can't tell which is the first request you only have to test whether it exists which doesn't incur the consequences that ddl incurs. True. I got myself thinking that without this feature people would be dropping the table after each use, which doesn't have to be the case. What we're talking about means that when someone issues SELECT * FROM cache they're going to -- unexpected -- be a) turning their virtual transaction id into a real transaction id b) creating a new entry in pg_catalog and its indexes c) wal logging the new pg_catalog entry (including having to fsync at commit time) d) acquiring an exclusive lock on the new entry. Only if they are making the first reference to the table in that session, and it's only unexpected if they don't know that such a reference to a global temp table can cause the table to materialize. Surely you will grant that someone referencing such a table should know what it is? There have been posts by people who were bitten by expecting that they could create temporary work tables for short frequently run queries who didn't realize that would mean pg_class would have to be vacuumed every few minutes and that it would slow down every index lookup for table names. Like I said, I have run into performance problems with temp table creation, especially when write barriers were configured on due to battery failure or OS misconfiguration, and the cost turned out to be almost entirely in the creation of the disk files which support the temp table -- base, toast, indexes, etc. Unless you can fix the big problems, worrying about the stuff we do optimize well will be a drop in the bucket. I don't see it as friendly to make that the implicit behaviour for innocent looking dml operations. Perhaps a note in the documentation of global temporary tables could set appropriate expectations? It seems that your whole objection to adding the requested feature hinges on anticipation of particular user expectations. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com writes: I don't understand what you mean by a cost once per request. You only have to create the temporary table on the first request. If you can't tell which is the first request you only have to test whether it exists which doesn't incur the consequences that ddl incurs. This is all based on utterly-unproven assumptions about relative costs. In particular, ISTM an additional network round trip or two associated with testing for/creating a temp table could easily swamp any costs associated with catalog entry creation. Even if it doesn't, creating/deleting a few dozen rows in the system catalogs shouldn't really be something that autovacuum can't deal with. If it were, we'd be hearing a lot more complaints about the *existing* temp table feature being unusable. (And yes, I know it's come up once or twice, but not all that often.) I'm all for eliminating catalog overheads, if we can find a way to do that. I don't think that you get to veto implementation of the feature until we can find a way to optimize it better. The question is not about whether having the optimization would be better than not having it --- it's about whether having the unoptimized feature is better than having no feature at all (which means people have to implement the same behavior by hand, and they'll *still* not get the optimization). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Tue, Apr 28, 2009 at 6:18 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: I was thinking in terms of throwing a warning in 8.4 and implementing new behavior in 8.5. If we're on that time frame with it, I guess it's not too early to suggest what we would implement in 8.5. I would suggest that it is clear that what Pavel is requesting is fully compliant with the spec's definition of global temporary tables. I think there are implicit assumptions that the spec is making about the performance implications of using these temporary tables. It's offering a tool that can reasonably be used in place of views and CTEs in otherwise pure DML. I didn't follow precisely what Pavel was describing but IMHO anything which does any DDL, even implicitly, would be make the feature impractical in many cases where it really ought to work. Anything which causes pg_class to bloat or require special vacuum strategies is just not going to scale. The whole point of having the schema declared in advance and then having each procedure execution have access to a private (or non-private) data store following that predefined schema is to avoid having to execute any catalog changes with all the locking and catalog i/o that DDL requires. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com wrote: I've been thinking about Alvaro's idea of a separate smgr. If you had a single pg_class entry for all sessions but the smgr knew to store the actual data for it in a session-local file, either in a session-specific tablespace or using the same mechanism the temporary files use to direct data then the backend would basically never know it wasn't a regular table. It could still use local buffers but it could use the global relcache, invalidation, locks, etc. I think we would have to take a session-level access lock as soon as we put any data in our local store. And each DDL operation would have to be visited to see whether it needs special behaviour for locally stored tables. I suspect most of them will only be able to be handled if there are no active sessions using the table so they'll basically be no-ops except for the catalog changes. Any chance that some of these improvements could be applied to temp tables created with the PostgreSQL-specific syntax while we're at it? The need for several tables to be created on disk to materialize a single temp table currently causes performance problems in some contexts. I don't think the updates to the system tables have the same magnitude of performance hit as creating these tables, especially if write barriers are on. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Greg Stark st...@enterprisedb.com wrote: The whole point of having the schema declared in advance and then having each procedure execution have access to a private (or non-private) data store following that predefined schema is to avoid having to execute any catalog changes with all the locking and catalog i/o that DDL requires. Global temporary tables are not shared between connections. Look back to Pavel's original post; he's looking for a convenience -- a way to have a temporary table materialized for a connection on reference, INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES. It is not meant to be better in performance than creating a temporary table explicitly on the fly and adding all these things, it's meant to provide a consistent definition which can be materialized and used on demand. Nothing more; nothing less. I understand not everyone has a need for such a thing, but isn't that true of most features in PostgreSQL? If you're saying we can implement the standard's global temporary tables in a way that performs better than current temporary tables, that's cool. That would be a nice bonus in addition to the application programmer convenience and having another tick-mark on the standards compliance charts. Do you think that's feasible? If not, the feature would be useful to some with the same performance that temporary tables currently provide. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Wed, Apr 29, 2009 at 8:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: This is all based on utterly-unproven assumptions about relative costs. In particular, ISTM an additional network round trip or two associated with testing for/creating a temp table could easily swamp any costs associated with catalog entry creation. Even if it doesn't, creating/deleting a few dozen rows in the system catalogs shouldn't really be something that autovacuum can't deal with. I don't see why it's limited to a few dozen rows. Moderately busy web sites these days count their traffic in hundreds of page views per second. If it were, we'd be hearing a lot more complaints about the *existing* temp table feature being unusable. (And yes, I know it's come up once or twice, but not all that often.) Well my point is that currently you have to type CREATE TEMPORARY TABLE somewhere which at least gives you a clue that maybe you're doing something significant. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Wed, Apr 29, 2009 at 7:57 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: But the scaling issues are there already with temp tables. This actually makes it better, not worse, because the table can be materialized once per session, not once per request. Currently you have to issue CREATE TABLE and associated DDL manually. That makes it clear to the user that they're executing DDL and should expect it to behave like DDL. I don't understand what you mean by a cost once per request. You only have to create the temporary table on the first request. If you can't tell which is the first request you only have to test whether it exists which doesn't incur the consequences that ddl incurs. What we're talking about means that when someone issues SELECT * FROM cache they're going to -- unexpected -- be a) turning their virtual transaction id into a real transaction id b) creating a new entry in pg_catalog and its indexes c) wal logging the new pg_catalog entry (including having to fsync at commit time) d) acquiring an exclusive lock on the new entry. There have been posts by people who were bitten by expecting that they could create temporary work tables for short frequently run queries who didn't realize that would mean pg_class would have to be vacuumed every few minutes and that it would slow down every index lookup for table names. I don't see it as friendly to make that the implicit behaviour for innocent looking dml operations. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Pavel Stehule escribió: Hello I am thinking about global temp tables. One possible solution is creating global temporary table like normal table and in planner stage check using this table. When some global temporary table is detected, then real temporary table is created and used in execution plan. It's like: CREATE GLOBAL TEMP TABLE foo(a varchar); -- create global empty table foo SELECT * FROM foo; a) is relevant temp table for foo, use it a) when not, then CREATE TEMP TABLE pg_temp_1.foo(LIKE foo INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); b) transform origin query to SELECT * FROM pg_temp_1.foo; Ideas? Notes? Objections? Maybe we could make this work by fiddling with a different smgr -- on it, smgr_sync would be a noop, as would smgr_immedsync, and we could kludge something up to truncate relations during recovery. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Alvaro Herrera alvhe...@commandprompt.com writes: Maybe we could make this work by fiddling with a different smgr -- on it, smgr_sync would be a noop, as would smgr_immedsync, and we could kludge something up to truncate relations during recovery. Interesting thought but I think it falls down on pg_statistic. One comment I've got is that we have already concluded that the spec's GLOBAL/LOCAL TEMP TABLE distinction is not related to cross-session persistence of the table definitions, but rather to module visibility which is a concept we have not got (yet). Ergo, we should not use the phrase global temp table for these things. Not sure what to suggest instead. Perhaps call them session tables instead of temp tables? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Perhaps call them session tables instead of temp tables? regards, tom lane Or transient table ... Maybe we can define when such table lose data But in real - there is no need in this feature - databases are made to hold data, not to lose. If an application requires mechanism to store transient session-data, it should create its own session-objects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Tom Lane t...@sss.pgh.pa.us wrote: we have already concluded that the spec's GLOBAL/LOCAL TEMP TABLE distinction is not related to cross-session persistence of the table definitions How do you reconcile that conclusion with the following, from ISO/IEC 9075-2:2003 (E), 4.14 Tables: The definition of a global temporary table or a created local temporary table appears in a schema. In SQL language, the name and the scope of the name of a global temporary table or a created local temporary table are indistinguishable from those of a persistent base table. However, because global temporary table contents are distinct within SQL-sessions, and created local temporary tables are distinct within SQL-client modules within SQL-sessions, the effective schema name of the schema in which the global temporary table or the created local temporary table is instantiated is an implementation-dependent schema name that may be thought of as having been effectively derived from the schema name of the schema in which the global temporary table or created local temporary table is defined and the implementation-dependent SQL- session identifier associated with the SQL-session. There is a distinction between the definition, which appears in a schema and for which the name and the scope ... are indistinguishable from those of a persistent base table, versus the effective schema in which an instance is materialized, which is session and/or module dependent. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Tom Lane t...@sss.pgh.pa.us wrote: it could be considered either a global or a local temp table per spec (without any module support you can't really say which it is). That seems bogus -- without modules it is clearly not LOCAL. What Pavel is requesting exactly matches the spec's definition of a global temporary table, but it does make me uneasy that after accepting the standard syntax, and behaving differently from it (including making no distinction between GLOBAL and LOCAL declarations) we would suddenly go to compliance on GLOBAL declarations but leave LOCAL as is. Maybe too messy to try to improve. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: we have already concluded that the spec's GLOBAL/LOCAL TEMP TABLE distinction is not related to cross-session persistence of the table definitions How do you reconcile that conclusion with the following, from ISO/IEC 9075-2:2003 (E), 4.14 Tables: The point is that what we call temp tables are not either global or local temp tables by the spec's definition. If we invent something that behaves as Pavel suggests, then it could be considered either a global or a local temp table per spec (without any module support you can't really say which it is). We're stuck in a terminological problem anyway, but it will get a whole lot worse if we fail to acknowledge that there's more than one property involved here. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Tom Lane t...@sss.pgh.pa.us wrote:
GLOBAL/LOCAL TEMP TABLE distinction is not related to cross-session
persistence of the table definitions
On a re-read, I think I see your point -- it is the DECLARE LOCAL TEMP
TABLE versus CREATE { GLOBAL | LOCAL } TEMP TABLE which determines
whether the table definition is persisted. Both forms of CREATE TEMP
TABLE should persist the definition if you go by the standard, so you
don't want to muddy the waters by complying on one and not the other?
-Kevin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner kevin.gritt...@wicourts.gov writes: ... Both forms of CREATE TEMP TABLE should persist the definition if you go by the standard, so you don't want to muddy the waters by complying on one and not the other? Right. This goes back to our old principle of trying not to use spec-defined syntax for not-per-spec behavior. We are already behind the eight ball as far as temp tables go, but let's not make it worse by blindly picking some spec-defined syntax without a plan for where we go from here. (I'm assuming that it's reasonably likely that we will want a spec-compatible module feature someday. We'll really have painted ourselves into a corner if we don't think about the issue now.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: it could be considered either a global or a local temp table per spec (without any module support you can't really say which it is). That seems bogus -- without modules it is clearly not LOCAL. You could just as easily say it's not GLOBAL. What Pavel is requesting exactly matches the spec's definition of a global temporary table, but it does make me uneasy that after accepting the standard syntax, and behaving differently from it (including making no distinction between GLOBAL and LOCAL declarations) we would suddenly go to compliance on GLOBAL declarations but leave LOCAL as is. Right. What I'm suggesting is that before we mess with this we should have a road map on whether we are going to try to get to spec compliance in this area, and if so how. One thing I just noticed is that the spec does not consider GLOBAL/LOCAL to be optional --- per spec you *must* write one or the other in front of TEMPORARY. So we could adopt the view that omitting this keyword implies our current non-spec behavior (which is far too useful to give up, spec compliance or no) while writing one or the other selects the spec behavior. However, if we're going to do that then we should start throwing warnings for use of the keywords, preferably before the release in which they actually start doing something different. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Tom Lane t...@sss.pgh.pa.us wrote: One thing I just noticed is that the spec does not consider GLOBAL/LOCAL to be optional --- per spec you *must* write one or the other in front of TEMPORARY. Agreed. So we could adopt the view that omitting this keyword implies our current non-spec behavior (which is far too useful to give up, spec compliance or no) while writing one or the other selects the spec behavior. +1 (+1) However, if we're going to do that then we should start throwing warnings for use of the keywords, preferably before the release in which they actually start doing something different. We might actually want to have a major release which rejects the standard syntax before the release where we implement standard behavior for it. (After, of course, a major release which issues the warning.) When we get to the point of breaking existing code (which is inevitable if we move to compliance here), it's better to break in a clear and predictable way Of course, that would mean that implementation would be three releases away (warn, disable syntax, reenable syntax with standard semantics). -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: However, if we're going to do that then we should start throwing warnings for use of the keywords, preferably before the release in which they actually start doing something different. We might actually want to have a major release which rejects the standard syntax before the release where we implement standard behavior for it. (After, of course, a major release which issues the warning.) When we get to the point of breaking existing code (which is inevitable if we move to compliance here), it's better to break in a clear and predictable way I was thinking in terms of throwing a warning in 8.4 and implementing new behavior in 8.5. An extra release only helps if you assume everyone adopts that release at some point. The number of questions we see about multi-version jumps should disabuse people of the notion that everyone does it that way ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Tom Lane t...@sss.pgh.pa.us wrote: I was thinking in terms of throwing a warning in 8.4 and implementing new behavior in 8.5. If we're on that time frame with it, I guess it's not too early to suggest what we would implement in 8.5. I would suggest that it is clear that what Pavel is requesting is fully compliant with the spec's definition of global temporary tables. Since the essence of global in this context is that they are not constrained by modules, the absence module support doesn't seem to create any possible compatibility issue when and if modules are added. I would suggest (with somewhat less confidence) that both created and declared local temporary tables might make sense in the context of whatever procedural languages are supported. In PL/pgSQL, for example, the declaration for a declared local tempoary table would be allowed inside the PL's BEGIN block, in the area where local variables are allowed. Such a table would be visible only within the context of the block (meaning we would probably need to munge the name somehow to support recursion or other functions with a duplicate table name). The temporary table would be materialized at the point where it is declared, and dropped at the END of the block. I not clear on whether a created local temporary table should retain its contents from one invocation of a function to the next. I'm inclined to think it shouldn't -- that the scope for a materialized instance is the same as a declared local tempoarary table; the CREATE just ensures a consistent definition wherever used. Or perhaps it's just a bad idea to attempt to use the LOCAL syntax outside of a proper module at all. The GLOBAL option seems clear; LOCAL seems a bit muddy to me. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Pavel Stehule pavel.steh...@gmail.com wrote: I am thinking about global temp tables. These would have some value to us. In case anyone doesn't know, this is a feature in the SQL standard. You have a permanent definition of the schema, but the table is materialized as a temporary table on reference by any connection. I can't speak to the practicality of the proposed implementation techniques. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Kevin Grittner wrote: Pavel Stehule pavel.steh...@gmail.com wrote: I am thinking about global temp tables. These would have some value to us. In case anyone doesn't know, this is a feature in the SQL standard. You have a permanent definition of the schema, but the table is materialized as a temporary table on reference by any connection. I can't speak to the practicality of the proposed implementation techniques. Using a global table to achieve schema-persistent temp tables seems like a horrid hack - what would you do if the table used a type other than a standard built-in type? Or perhaps Pavel doesn't really mean global as the term is used in Postgres (c.f. the pg_database table)? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Andrew Dunstan and...@dunslane.net wrote: Or perhaps Pavel doesn't really mean global as the term is used in Postgres (c.f. the pg_database table)? I'd bet that he doesn't. He's taking terminology from the standard, where it means not limited to one SQL-client module. It just means it is available as long as you are using the connection. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Apr 27, 2009, at 4:44 PM, Pavel Stehule wrote: Hello I am thinking about global temp tables. One possible solution is creating global temporary table like normal table and in planner stage check using this table. When some global temporary table is detected, then real temporary table is created and used in execution plan. It's like: CREATE GLOBAL TEMP TABLE foo(a varchar); -- create global empty table foo SELECT * FROM foo; a) is relevant temp table for foo, use it a) when not, then CREATE TEMP TABLE pg_temp_1.foo(LIKE foo INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); b) transform origin query to SELECT * FROM pg_temp_1.foo; Ideas? Notes? Objections? When will postgresql offer global temporary tables with data which are shared among sessions? Such tables are great for transient data such as web session data where writing to the WAL is a waste. (On DB startup, the tables would simply be empty.) We're currently stuck with the memcached plugin which makes it impossible to use database constructs such as foreign keys against the temporary data. Cheers, M - -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.10 (Darwin) iEYEARECAAYFAkn2JHQACgkQqVAj6JpR7t4YRgCdGj8JPJY61PPaK79jnPFXu8c7 vjIAn2F1lA0Nr/2EHVPcYQohWqGjWElK =3zYu - -END PGP SIGNATURE- -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.10 (Darwin) iEYEARECAAYFAkn2JIIACgkQqVAj6JpR7t6IOgCdE0le+MAlcwCYNqEt+w9jt/Y3 Z/sAni8Jm3ndYZSI1pIQLBVtKnBnJ8Ee =VXWF -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
Hi, Le 27 avr. 09 à 23:32, A.M. a écrit : When will postgresql offer global temporary tables with data which are shared among sessions? Such tables are great for transient data such as web session data where writing to the WAL is a waste. (On DB startup, the tables would simply be empty.) We're currently stuck with the memcached plugin which makes it impossible to use database constructs such as foreign keys against the temporary data. If using 8.3 you can SET LOCAL synchronous_commit TO off; for web session management transactions, it'll skip the WAL fsync'ing, which is already a good start. HTH, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
A.M. age...@themactionfaction.com wrote: When will postgresql offer global temporary tables with data which are shared among sessions? Well, that would certainly be far different from what the standard calls a temporary table of any flavor. In the standard all temporary tables are restricted to a single connection, and the scope is: GLOBAL: Schema always present. Once materialized, present for as long as the connection exists. CREATED LOCAL: Schema always present. Once materialized, visible only within a particular module. DECLARED LOCAL: No permanent schema. Materialized when declared in a compound statement (standard BEGIN/END; not related to transaction boundaries), and automatically dropped on exit from the compound statement. Current PostgreSQL temporary tables are sort of a hybrid between GLOBAL and DECLARED LOCAL temporary tables from the standard. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote: Hi, Le 27 avr. 09 à 23:32, A.M. a écrit : When will postgresql offer global temporary tables with data which are shared among sessions? Such tables are great for transient data such as web session data where writing to the WAL is a waste. (On DB startup, the tables would simply be empty.) We're currently stuck with the memcached plugin which makes it impossible to use database constructs such as foreign keys against the temporary data. If using 8.3 you can SET LOCAL synchronous_commit TO off; for web session management transactions, it'll skip the WAL fsync'ing, which is already a good start. That's pretty close, but it's not table specific and wouldn't let us to reliably mix transient data changes with real data changes. Cheers, M -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.10 (Darwin) iEYEARECAAYFAkn2KNcACgkQqVAj6JpR7t4OrQCgpU9K3FzG2LWWyM245vUaop1G ZMIAn379RDewxKUmCsZsWLo8KdWAYGIs =kHl5 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
A.M. age...@themactionfaction.com wrote: On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote: Le 27 avr. 09 à 23:32, A.M. a écrit : When will postgresql offer global temporary tables with data which are shared among sessions? Such tables are great for transient data such as web session data where writing to the WAL is a waste. (On DB startup, the tables would simply be empty.) We're currently stuck with the memcached plugin which makes it impossible to use database constructs such as foreign keys against the temporary data. If using 8.3 you can SET LOCAL synchronous_commit TO off; for web session management transactions, it'll skip the WAL fsync'ing, which is already a good start. That's pretty close, but it's not table specific and wouldn't let us to reliably mix transient data changes with real data changes. Yeah, we have a dozen or so tables we use with the pattern you describe; so the feature you describe would also have some value for us. To avoid confusion, we don't refer to these as temporary tables, but rather as permanent work tables. Again, I can't comment on practical issues regarding implementation; but it would be a nice feature to add some day. The tricky bit would be to figure out how to ensure that it got cleaned up properly, especially if the PostgreSQL went down or client processes wend down before tidying up. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
On Apr 27, 2009, at 6:01 PM, Kevin Grittner wrote: A.M. age...@themactionfaction.com wrote: On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote: Le 27 avr. 09 à 23:32, A.M. a écrit : When will postgresql offer global temporary tables with data which are shared among sessions? Such tables are great for transient data such as web session data where writing to the WAL is a waste. (On DB startup, the tables would simply be empty.) We're currently stuck with the memcached plugin which makes it impossible to use database constructs such as foreign keys against the temporary data. If using 8.3 you can SET LOCAL synchronous_commit TO off; for web session management transactions, it'll skip the WAL fsync'ing, which is already a good start. That's pretty close, but it's not table specific and wouldn't let us to reliably mix transient data changes with real data changes. Yeah, we have a dozen or so tables we use with the pattern you describe; so the feature you describe would also have some value for us. To avoid confusion, we don't refer to these as temporary tables, but rather as permanent work tables. Again, I can't comment on practical issues regarding implementation; but it would be a nice feature to add some day. The tricky bit would be to figure out how to ensure that it got cleaned up properly, especially if the PostgreSQL went down or client processes wend down before tidying up. Actually, for our usage, that's the easiest part- truncate all the permanent work tables whenever the db starts. That's really the only sane thing to do anyway. That's what I mean by transient data- if it's there, that's great, if not, I can re-generate it (cache) or I don't care because, if the database goes down, then the data is useless on restart anyway. Cheers, M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
2009/4/27 Andrew Dunstan and...@dunslane.net: Kevin Grittner wrote: Pavel Stehule pavel.steh...@gmail.com wrote: I am thinking about global temp tables. These would have some value to us. In case anyone doesn't know, this is a feature in the SQL standard. You have a permanent definition of the schema, but the table is materialized as a temporary table on reference by any connection. I can't speak to the practicality of the proposed implementation techniques. Using a global table to achieve schema-persistent temp tables seems like a horrid hack - what would you do if the table used a type other than a standard built-in type? Where is a problem? - there is normal dependency between types and relation. Of course, ALTER TABLE have to be little bit different - a) should be done, when no table is used, b) should be done only on all temporary tables. But this technique do minimal changes in pg internal structure. The core of problem is structure of pg_class table, that contains possibly shared and not shared fields between global tables. So implementation needs a) significant change of pg_class table OR b) using some transparent table overloading One year ago I though about some memory tables for it. But it is too different and now, when VACUUM should be effective I thing, it is needless. Or perhaps Pavel doesn't really mean global as the term is used in Postgres (c.f. the pg_database table)? no, I though global tables in sense of SQL standard. What do you thing are shared tables (in pg terminology) regards Pavel Stehule cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: global temp tables
2009/4/28 Kevin Grittner kevin.gritt...@wicourts.gov: A.M. age...@themactionfaction.com wrote: On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote: Le 27 avr. 09 à 23:32, A.M. a écrit : When will postgresql offer global temporary tables with data which are shared among sessions? Such tables are great for transient data such as web session data where writing to the WAL is a waste. (On DB startup, the tables would simply be empty.) We're currently stuck with the memcached plugin which makes it impossible to use database constructs such as foreign keys against the temporary data. If using 8.3 you can SET LOCAL synchronous_commit TO off; for web session management transactions, it'll skip the WAL fsync'ing, which is already a good start. That's pretty close, but it's not table specific and wouldn't let us to reliably mix transient data changes with real data changes. Yeah, we have a dozen or so tables we use with the pattern you describe; so the feature you describe would also have some value for us. To avoid confusion, we don't refer to these as temporary tables, but rather as permanent work tables. Again, I can't comment on practical issues regarding implementation; but it would be a nice feature to add some day. The tricky bit would be to figure out how to ensure that it got cleaned up properly, especially if the PostgreSQL went down or client processes wend down before tidying up. For me, GLOBAL TEMP TABLES should significant to increase comfort for developers. That is main reason. reagards Pavel Stehule -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
