global temporary tables
in [PgSql]
|
From: Robert Haas on 24 Apr 2010 18:53 On Sat, Apr 24, 2010 at 3:51 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > 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. Well, yes and no. I think there are definitely some good things that can happen if we can see our way to taking a hammer to pg_class and pg_attribute. If we create, e.g. pg_shared_class and pg_shared_attribute, then we can un-nail the catalogs you just nailed to make the authentication process able to work without selecting a database. We can also enable (without guilt) clustering both those catalogs and the database-specific versions of pg_class, since we no longer have to worry about having multiple copies of the row that can get out of sync with each other. And if we further break off pg_temp_class and pg_temp_attribute, then we can also have our existing flavor of temporary tables without worrying about catalog bloat, which would be great. There may be other applications as well. Having said all that, it doesn't actually allow us to implement global temporary tables, because obviously the catalog entries for a global temporary table have to be permanent. Of course, if we didn't have to worry about catalog bloat, the "clone" approach you're proposing would be somewhat more attractive, but I actually think that the synergy is in the other direction: the perfect place to store the catalog entries and statistics for local temporary tables is - in a global temporary table! Note that while a local temporary table can never inherit from a permanent table, it's entirely sensible to let global temporary tables inherit from permanent tables. Different backends will have different views of the overall contents of the parent table, but that's OK, even desirable. ....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: Robert Haas on 24 Apr 2010 19:01 On Sat, Apr 24, 2010 at 6:29 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > 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. For so long as local temporary tables put their catalog entries in pg_class, we're not going to be able to use them during Hot Standby. See the email I just sent elsewhere on this thread for a long term roadmap to getting out of that pickle. At least under the implementation I'm proposing here, making global temporary tables usable would be an easier nut to crack, because the catalog entries are a non-issue. There is one major problem, though: assigning a scratch relfilenode to the temporary table requires generating an OID, which we currently have no way to allow on the standby. Upthread I also proposed an implementation for unlogged tables (that is, contents don't survive a server bounce) which wouldn't have that problem either, although I haven't fully thought it through and there may very well be other issues. So in short: I don't think anything we're talking about it would make HS use harder, and some of it might make it easier. But probably some additional engineering effort dedicated specifically to solving the problems unique to HS would still be needed. ....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: Jim Nasby on 24 Apr 2010 20:47 On Apr 24, 2010, at 12:31 PM, Tom Lane 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. > > 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? One possibility: rename the existing pg_stats to pg_stats_permanent. Create a global temporary table called pg_stats_temporary. pg_stats becomes a union of the two. I know the backend wouldn't be able to use the view, but hopefully access to statistics goes through a limited set of functions so that teaching them about the two different tables isn't hard. As for cleanup and storage questions; what about having temp objects live in pgsql_tmp? I'm thinking create a directory under pgsql_tmp for a backend PID the first time it creates a temp object (global or local) and create the files in there. That also means that we don't have to come up with different relfilenodes for each backend. On the other hand, some layer (presumably smgr) would need to understand whether a relation was temporary or not. If we do that, cleanup is easy: you can remove any directories that no longer have a running PID. For forensics you probably only want to do that automatically when a backend starts and discovers it already has a directory, though we should also provide an administrator function that will clobber all directories that no longer have backends. -- Jim C. Nasby, Database Architect jim(a)nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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 21:14 On Sat, Apr 24, 2010 at 8:47 PM, Jim Nasby <decibel(a)decibel.org> wrote: > On Apr 24, 2010, at 12:31 PM, Tom Lane 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. >> >> 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? > > One possibility: rename the existing pg_stats to pg_stats_permanent. Create a global temporary table called pg_stats_temporary. pg_stats becomes a union of the two. I know the backend wouldn't be able to use the view, but hopefully access to statistics goes through a limited set of functions so that teaching them about the two different tables isn't hard. Yeah, I don't think that would be too horrible. Part of me feels like you'd want to have the ability to store stats for a global temp table in either one of those tables depending on use-case, but I'm also reluctant to invent a lot of new syntax for a very limited use case. > As for cleanup and storage questions; what about having temp objects live in pgsql_tmp? I'm thinking create a directory under pgsql_tmp for a backend PID the first time it creates a temp object (global or local) and create the files in there. That also means that we don't have to come up with different relfilenodes for each backend. That would impose a couple of implementation restrictions that don't seem necessary. One, it would imply ignoring reltablespace. Two, it would prohibit (or at least complicate) allowing a backend to CLUSTER or REINDEX its own private copy of the rel. > On the other hand, some layer (presumably smgr) would need to understand whether a relation was temporary or not. If we do that, cleanup is easy: you can remove any directories that no longer have a running PID. For forensics you probably only want to do that automatically when a backend starts and discovers it already has a directory, though we should also provide an administrator function that will clobber all directories that no longer have backends. Unfortunately, I don't see much alternative to making smgr know something about the temp-ness of the relation, though I'm hoping to keep the smgr surgery to an absolute minimum. Maybe what we could do is incorporate the backend ID or PID into the file name when the relation is temp. Then we could scan for and nuke such files pretty easily. Otherwise I can't really think how to make it work. ....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 21:37
Robert Haas <robertmhaas(a)gmail.com> writes: > Unfortunately, I don't see much alternative to making smgr know > something about the temp-ness of the relation, though I'm hoping to > keep the smgr surgery to an absolute minimum. Maybe what we could do > is incorporate the backend ID or PID into the file name when the > relation is temp. Then we could scan for and nuke such files pretty > easily. Otherwise I can't really think how to make it work. I think that could be a really good idea, mainly because it makes post-crash cleanup MUCH safer: you can tell with certainty from the filename that it's a leftover temp table. The notion of zapping files just because we don't see them listed in pg_class has always scared the heck out of me. We already know temp-ness at pretty low levels, like bufmgr vs localbuf. Pushing it all the way down to smgr doesn't seem like a leap; in fact I think it would eliminate a separate isTemp parameter in a lot of places. 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 |