global temporary tables
in [PgSql]
|
From: Jim Nasby on 24 Apr 2010 21:51 On Apr 24, 2010, at 8:14 PM, Robert Haas wrote: >> 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. Yeah, I'm thinking that's very probably overkill. And if we were going to go to that level, I think it would be far more useful to provide an interface to allow manual control over statistics first, so that you can give the optimizer custom information. >> 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. Well, the same structure could be imposed underneath a temptablespace. I don't think it matters where you ultimately put it, the goal is just to make sure you can definitively tell that a file was a: temporary and b: what PID it belonged to. That allows for safe cleanup. -- 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:59 On Sat, Apr 24, 2010 at 9:37 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > 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. Eh? I don't see how it's going to do that. ....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 22:22 On Sat, Apr 24, 2010 at 9:59 PM, Robert Haas <robertmhaas(a)gmail.com> wrote: > On Sat, Apr 24, 2010 at 9:37 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> 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. > > Eh? I don't see how it's going to do that. Oh, maybe I do see. If we pass it to smgropen() and stash it in the SMgrRelation, we don't have to keep supplying it later on, maybe? Will investigate further. ....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 22:28 Robert Haas <robertmhaas(a)gmail.com> writes: > Oh, maybe I do see. If we pass it to smgropen() and stash it in the > SMgrRelation, we don't have to keep supplying it later on, maybe? Right. I'm unsure whether we should push it into the RelFileNode struct itself, but even having it in SMgrRelation ought to cut out a few places where it now has to be passed separately. 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: Robert Haas on 24 Apr 2010 22:57
On Sat, Apr 24, 2010 at 10:28 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> Oh, maybe I do see. If we pass it to smgropen() and stash it in the >> SMgrRelation, we don't have to keep supplying it later on, maybe? > > Right. I'm unsure whether we should push it into the RelFileNode > struct itself, but even having it in SMgrRelation ought to cut out > a few places where it now has to be passed separately. Pushing it into the RelFileNode has some advantages in terms of being able to get at the information from everywhere, but one thing that makes me think that's probably not a good decision is that we somtimes WAL-log relfilenodes. And WAL-logging the value of the isTemp flag is a waste, because if we're WAL-logging, it's zero. ....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 |