global temporary tables
in [PgSql]
|
From: Robert Haas on 24 Apr 2010 14:32 On Sat, Apr 24, 2010 at 1:31 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> At least AIUI, the use case for this feature is that you want to avoid >> creating "the same" temporary table over and over again. > > The context that I've seen it come up in is that people don't want to > clutter their functions with create-it-if-it-doesn't-exist logic, > which you have to have given the current behavior of temp tables. > Any performance gain from reduced catalog churn would be gravy. I think there's a significant contingent on this mailing list who feel that that gravy would be rather tasty and would like very much to enjoy some of it along with their temporary table tetrazzini. > Aside from the DROP problem, I think this implementation proposal > has one other big shortcoming: what are you going to do about > table statistics? In many cases, you really *have* to do an ANALYZE > once you've populated a temp table, if you want to get decent plans > for it. Where will you put those stats? For a first cut, I had thought about ignoring the problem. Now, that may sound stupid, because now if two different backends have very different distributions of data in the table and both do an ANALYZE, one set of statistics will clobber the other set of statistics. On the flip side, for some usage patterns, it might be actually work out to a win. Maybe the data I'm putting in here today is a great deal like the data I put in here yesterday, and planning it with yesterday's statistics doesn't cost enough to be worth a re-ANALYZE. If we don't want to do that, I suppose one option is to create a pg_statistic-like table in the backend's temporary tablespace and put them there; or we could put them into a backend-local hash table. The current setup of pg_statistic is actually somewhat weak for a number of things we might want to do: for example, it might be interesting to gather statistics for the subset of a table for which a particular partial index is predOK. When such an index is available for a particular query, we could use the statistics for that subset of the table instead of the overall statistics for the table, and get better estimates. Or we could even let the user specify predicates which will cause the table to have a different statistical distribution than the table as a whole, and gather statistics for the subset that matches the predicate. One approach would be to make the starelid column able to reference something other than a relation OID, although I don't think that actually helps with the global temp table problem because if we use the real pg_statistic to store the data then we have to arrange to clean it up. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 24 Apr 2010 14:41 > > For a first cut, I had thought about ignoring the problem. Â Now, that > may sound stupid, because now if two different backends have very > different distributions of data in the table and both do an ANALYZE, > one set of statistics will clobber the other set of statistics. Â On > the flip side, for some usage patterns, it might be actually work out > to a win. Â Maybe the data I'm putting in here today is a great deal > like the data I put in here yesterday, and planning it with > yesterday's statistics doesn't cost enough to be worth a re-ANALYZE. > Both variant can be. First time - statistic can be taken from some "original" (can be empty). After ANALYZE the statistic can be individual. Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 24 Apr 2010 14:51 On Sat, Apr 24, 2010 at 1:38 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > [ forgot to respond to this part ] > > Robert Haas <robertmhaas(a)gmail.com> writes: >> ... I don't see the problem with DROP. >> Under the proposed design, it's approximately equivalent to dropping a >> table that someone else has truncated. You just wait for the >> necessary lock and then do it. > > And do *what*? You can remove the catalog entries, but how are you > going to make the physical storage of other backends' versions go away? > (To say nothing of making them flush their local buffers for it.) > If you do remove the catalog entries, won't you be cutting the knees > out from under whatever end-of-session cleanup processing might exist > in those other backends? Well, if I knew for sure what the best way was to solve all of these problems, I'd be posting a finished patch rather than a request for comment on a design. It's not obvious to me that there's a terribly thorny problem in the area you're concerned about, but your concern is making me worry that I'm missing something. Why would the end-of-session processing need the catalog entries? It seems like whatever backend-local data structure we're using to record the relfilenode mappings would be sufficent to nuke the backend storage, and what else needs doing? > The idea of the global table as a template that individual sessions > clone working tables from would avoid most of these problems. You > rejected it on the grounds that ALTER would be too hard; but if you're > blowing off ALTER anyway, that argument seems pretty unimpressive. I don't think that avoiding the catalog churn is something to be dismissed lightly, but I also think that cloning the table is likely to be significantly more difficult from an implementation point of view. Under the implementation I'm proposing, we don't need much that is fundamentally all that new. Global temporary tables can be treated like our existing temp tables for purposes of XLog and bufmgr, but they don't get forced into a temporary namespace. The relation mapping infrastructure provides a pretty good start for using a relfilenode that isn't stored in pg_class. I've already gone through the exercise of finding all the places where we check rd_istemp and changing them to use macros instead (RelationNeedsWAL, IsBackendLocal, etc.) and it's not bad. There's a related project which I think can also leverage much of this same infrastructure: unlogged tables. We've talked about this before, but in short the idea is that an unlogged table behaves like a regular table in all respects except that we never write WAL for it; and we truncate it at shutdown and at startup. Therefore, it doesn't show up on standby servers, and its contents are not persistent across restarts, but performance is improved. It's suitable for things like "the table of currently logged in users", where you don't mind forcing everyone to log in again if the database crashes. (It might even be possible to allow writes to unlogged tables on standby servers, though I'm not feeling that ambitious ATM.) So: - local temp tables exist in a temp namespace, use local buffers, and skip WAL - global temp tables exist in a non-temp namespace, use local buffers, and skip WAL - unlogged tables exist in a non-temp namespace, use shared buffers, and skip WAL - normal tables exist in a non-temp namespace, use shared buffers, and write WAL Thoughts? ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 24 Apr 2010 15:51 Robert Haas <robertmhaas(a)gmail.com> writes: > ... Why would the > end-of-session processing need the catalog entries? It seems like > whatever backend-local data structure we're using to record the > relfilenode mappings would be sufficent to nuke the backend storage, > and what else needs doing? Well, if you're intending to have a separate data structure and code path for cleaning up this type of temp table, then maybe you don't need to touch any catalog entries. I'm concerned though about how far the effects will propagate --- things like TRUNCATE, VACUUM FULL, REINDEX will probably have issues with this. Right now they think in terms of writing a new pg_class entry in order to reassociate tables with new relfilenodes. Have you thought much about the previously proposed design, ie keeping catalog entries for temp tables in backend-local temporary catalogs? That would certainly be a lot of work, but I think in the end it might fit in better. This design feels like it's being driven by "hey, we can abuse the relmapper to sort of do what we want", and not by what we really want. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 24 Apr 2010 18:29
On Fri, 2010-04-23 at 22:52 -0400, Robert Haas wrote: > Thoughts? Only a requirement: that we design this in a way that will allow temp tables to be used during Hot Standby. I make not other comment. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |