Обсуждение: Re: Re: [COMMITTERS] pgsql: Add ERROR msg for GLOBAL/LOCAL TEMP is not yet implemented
Re: Re: [COMMITTERS] pgsql: Add ERROR msg for GLOBAL/LOCAL TEMP is not yet implemented
От
"Kevin Grittner"
Дата:
> Tom Lane wrote: > "Kevin Grittner" writes: >> Because the current support for temporary tables is relatively >> similar to the standard's description of LOCAL TEMPORARY TABLES, >> but nothing at all like the standard's descri0ption of GLOBAL >> TEMPORARY TABLES. > > Um ... did you read the spec before stating that? Well, I did, but not recently. Clearly I should not have trusted my memory. A fresh review brought it all back to me. When I was reviewing the standard (in 1998) to decide what to implement for the SQL parser in the Wisconsin Courts framework I was designing, I decided to effectively consider any "compound statement" block (delimited by BEGIN/END) in a trigger or stored procedure to be equivalent to a module in terms of features such as temporary tables -- because we didn't have a concept of modules and wanted to borrow features from the standard which were defined in terms of modules. So over the years I muddled what was in the standard with implementation details of our framework. I apologize for the confusion. So there are three types of temporary tables defined in the standard, and the PostgreSQL implementation doesn't look like any of them. The bad thing is that PostgreSQL supports syntax for two of them without matching the standard semantics. :-( CREATE TEMPORARY TABLE (without LOCAL or GLOBAL) would be an extension which could not be confused with standard syntax, so arguably refusing to accept those would be the right thing to do from a standards compliance perspective -- it would be a legitimate PostgreSQL extension that way, but the breakage of working code which would result from suddenly doing that could never be considered acceptable. What to do about all this? Unless we expect to implement modules in the next release or two, perhaps we should address the LOCAL noise-word in the docs, with a note that its usage is non-standard and discouraged since it might conflict with standard usage in future releases. Since we've had an actual patch submitted for implementing GLOBAL temporary tables, perhaps that merits a run-time warning in 9.2 (also supported by a warning in the docs). I think we've dug ourselves into a hole by supporting standard syntax with non-standard semantics. As the saying goes, when you find yourself in that position, the first thing to do is to stop digging. -Kevin
On Sun, Jun 10, 2012 at 11:22 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > So there are three types of temporary tables defined in the standard, > and the PostgreSQL implementation doesn't look like any of them. The > bad thing is that PostgreSQL supports syntax for two of them without > matching the standard semantics. :-( > > CREATE TEMPORARY TABLE (without LOCAL or GLOBAL) would be an > extension which could not be confused with standard syntax, so > arguably refusing to accept those would be the right thing to do from > a standards compliance perspective -- it would be a legitimate > PostgreSQL extension that way, but the breakage of working code which > would result from suddenly doing that could never be considered > acceptable. > > What to do about all this? > > Unless we expect to implement modules in the next release or two, > perhaps we should address the LOCAL noise-word in the docs, with a > note that its usage is non-standard and discouraged since it might > conflict with standard usage in future releases. Since we've had an > actual patch submitted for implementing GLOBAL temporary tables, > perhaps that merits a run-time warning in 9.2 (also supported by a > warning in the docs). We don't actually have a patch for GTT at this point; Noah is at least the second person to threaten to write one, but nobody's actually done it yet to my knowledge. Maybe the right thing to do here is nothing. I think to some degree we are arguing about what color to paint an imaginary bikeshed. If at some point we support GTTs using the syntax CREATE GLOBAL TEMPORARY TABLE, then there is going to be a compatibility break. What we are arguing about is whether to pull that compatibility break forward into 9.2, or wait and let it break in the release where it has to break; and also whether we ought to force a compatibility break CREATE LOCAL TEMPORARY TABLE while we're at it. In the absence of agreement on either point, perhaps we should just leave this well enough alone. Even if we throw hard errors in 9.2, as Simon's patch did, it's not going to substantially ease the pain of migration if GTTs land in 9.3; a lot of people skip releases when upgrading. Warnings are even wimpier in terms of getting people to fix their code. If we were all on the same page about what to do that would be one thing, but given that we're not... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes: > We don't actually have a patch for GTT at this point; Noah is at least > the second person to threaten to write one, but nobody's actually done > it yet to my knowledge. IMO, the main reason that's been let slide for nine years is that there wasn't a particularly strong use-case for temp tables implemented the spec's way. Worse: according to the 2003 thread, there were in fact no major RDBMS players that hewed closely to the spec's semantics (though possibly that's changed by now); which made the "it's standard" argument far too weak to justify doing anything either. Now that there's a realistic use-case in hot standby scenarios, I think we can expect that something will get done within the foreseeable future. At least for the GLOBAL case --- I concur that there's nothing on the horizon suggesting we'll have spec-style LOCAL temp tables. > Maybe the right thing to do here is nothing. I think to some degree > we are arguing about what color to paint an imaginary bikeshed. If at > some point we support GTTs using the syntax CREATE GLOBAL TEMPORARY > TABLE, then there is going to be a compatibility break. If we can foresee that this will happen, warning about it in advance seems like a good idea. See for comparison our handling of the "=>" operator business. > What we are > arguing about is whether to pull that compatibility break forward into > 9.2, or wait and let it break in the release where it has to break; Uh, no, Simon's original patch pulled the compatibility break forward, which was what I objected to. But a WARNING won't break applications, and it does provide some notice, even though I admit that not everybody will be helped. regards, tom lane
On Mon, Jun 11, 2012 at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Maybe the right thing to do here is nothing. I think to some degree >> we are arguing about what color to paint an imaginary bikeshed. If at >> some point we support GTTs using the syntax CREATE GLOBAL TEMPORARY >> TABLE, then there is going to be a compatibility break. > > If we can foresee that this will happen, warning about it in advance > seems like a good idea. See for comparison our handling of the "=>" > operator business. > >> What we are >> arguing about is whether to pull that compatibility break forward into >> 9.2, or wait and let it break in the release where it has to break; > > Uh, no, Simon's original patch pulled the compatibility break forward, > which was what I objected to. But a WARNING won't break applications, > and it does provide some notice, even though I admit that not everybody > will be helped. Well, I'm OK with a warning, as I said upthread. I guess the remaining question is whether to do it only for LOCAL TEMP tables or also for GLOBAL TEMP ones. A survey of what other products do might be of some value. In SQL server, it appears that a local temporary table is exactly what we have today, but a global temporary table is quite different from the way we've been using the term: it's essentially an unlogged table that gets automatically dropped when the creating session, or the last session that's still using it, disconnects. SQL server uses a funny syntax for defining temporary tables: names are prefixed with # or ##, rather than using keywords like GLOBAL TEMP or LOCAL TEMP. http://decipherinfosys.wordpress.com/2007/05/04/temporary-tables-ms-sql-server/ Oracle seems to have only one kind of temporary tables: what we (and apparently the SQL standard) are calling GTTs: permanent tables with session-local content. It seems they also support ON COMMIT { PRESERVE | DELETE } ROWS for GTTs. MySQL has only one kind of temporary tables, which seem to have the same semantics as ours. They don't allow the noise words LOCAL or GLOBAL. DB2 calls all of its temporary tables global, and divides those into "created" global temporary tables and "declared" global temporary tables. Created GTTs are permanent tables with session-local content.Declared GTTs seem to be similar to our temporary tables,except that they avoid needing to catalog them. http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fr0054491.html Sybase ASE, which I include only because it is one of the few systems that actually support the CREATE LOCAL TEMPORARY TABLE syntax, appears to give them the same semantics as our existing temp tables: session local. Sybase ASE also includes two kinds of global temporary tables: non-shared - i.e. permanent tables with session-local contents - and shared - i.e. what we call unlogged tables, except that they don't survive a clean shutdown. http://dcx.sybase.com/1200/en/dbreference/create-local-temporary-table-statement.html http://dcx.sybase.com/1200/en/dbusage/temporary-tables.html All the other search results I can find for local temporary tables appear to be projects which have cloned, or thought about cloning, the SQL server behavior. So I can't find any evidence that any database product in existence uses CREATE LOCAL TEMPORARY TABLE to mean anything other than what CREATE TEMPORARY TABLE does in PostgreSQL, and there's at least one where it means exactly the thing that we do. Given that, I am inclined to think that we should only warn about using GLOBAL TEMP, and not LOCAL TEMP. It seems needlessly hard-headed to warn about using a syntax for which there are no existing, incompatible implementations and for which we have no plans to change the existing semantics. YMMV, of course. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Jun 11, 2012 at 09:18:39PM -0400, Robert Haas wrote: > I guess the > remaining question is whether to do it only for LOCAL TEMP tables or > also for GLOBAL TEMP ones. A survey of what other products do might > be of some value. Thanks for investigating. > Sybase ASE, which I include only because it is one of the few systems > that actually support the CREATE LOCAL TEMPORARY TABLE syntax, appears > to give them the same semantics as our existing temp tables: session > local. Sybase ASE also includes two kinds of global temporary tables: > non-shared - i.e. permanent tables with session-local contents - and > shared - i.e. what we call unlogged tables, except that they don't > survive a clean shutdown. > > http://dcx.sybase.com/1200/en/dbreference/create-local-temporary-table-statement.html > http://dcx.sybase.com/1200/en/dbusage/temporary-tables.html FWIW, that's SQL Anywhere, not ASE. ASE is closer to Microsoft SQL Server in this area. > So I can't find any evidence that any database product in existence > uses CREATE LOCAL TEMPORARY TABLE to mean anything other than what > CREATE TEMPORARY TABLE does in PostgreSQL, and there's at least one > where it means exactly the thing that we do. Given that, I am > inclined to think that we should only warn about using GLOBAL TEMP, > and not LOCAL TEMP. It seems needlessly hard-headed to warn about > using a syntax for which there are no existing, incompatible > implementations and for which we have no plans to change the existing > semantics. YMMV, of course. Oracle Rdb implemented the SQL standard behavior: http://www.oracle.com/technetwork/products/rdb/implementing-procedure-result-sets-091225.html So, one implementation mirrors our current CREATE LOCAL TEMPORARY TABLE semantics and another implements SQL standard semantics. No classic migration source product implements the syntax at all. Given that, I think we should make the decision independent of migration concerns. Our continuing users will be quicker to accept the need to remove GLOBAL than LOCAL; the table is not at all global but is, informally, local. Future users will benefit from a self-consistent system. Though it's difficult to quantify, future users also benefit from a system following the SQL standard. Given that, how about warning on GLOBAL only but having the documentation equally discourage use of both? nm
Noah Misch <noah@leadboat.com> writes: > Given that, how about warning on GLOBAL only but having the documentation > equally discourage use of both? Yeah, that's about what I was thinking, too. Any thoughts on the wording of the WARNING message? regards, tom lane
On Wed, Jun 13, 2012 at 02:56:58PM -0400, Tom Lane wrote: > Noah Misch <noah@leadboat.com> writes: > > Given that, how about warning on GLOBAL only but having the documentation > > equally discourage use of both? > > Yeah, that's about what I was thinking, too. > > Any thoughts on the wording of the WARNING message? My patch used "GLOBAL is deprecated in temporary table creation", which still seems fine to me. Here's an update based on this discussion.
Вложения
Noah Misch <noah@leadboat.com> writes: > On Wed, Jun 13, 2012 at 02:56:58PM -0400, Tom Lane wrote: >> Any thoughts on the wording of the WARNING message? > My patch used "GLOBAL is deprecated in temporary table creation", which still > seems fine to me. Here's an update based on this discussion. Applied with some further wordsmithing on docs and comments. We can still tweak this if anyone objects, of course, but I thought it'd probably save work to get it in before the branch. regards, tom lane
On Wed, Jun 13, 2012 at 05:50:36PM -0400, Tom Lane wrote: > Applied with some further wordsmithing on docs and comments. We can > still tweak this if anyone objects, of course, but I thought it'd > probably save work to get it in before the branch. Thanks. The SQL standard also distinguishes between global and local temporary tables, where a local temporary table is only visiblewithin a specific SQL module, though its definition is still shared across sessions. Since PostgreSQL does not supportSQL modules, this distinction is not relevant in PostgreSQL. That new documentation paragraph describes the standard behavior for DECLARE LOCAL TEMPORARY TABLE. CREATE LOCAL TEMPORARY TABLE produces a table available to all modules but having one instance of its contents per module, per session. With GLOBAL, by contrast, all modules see the same table contents during a given session. nm
Noah Misch <noah@leadboat.com> writes: > On Wed, Jun 13, 2012 at 05:50:36PM -0400, Tom Lane wrote: > The SQL standard also distinguishes between global and local temporary > tables, where a local temporary table is only visible within a specific SQL > module, though its definition is still shared across sessions. Since > PostgreSQL does not support SQL modules, this distinction is not relevant in > PostgreSQL. > That new documentation paragraph describes the standard behavior for DECLARE > LOCAL TEMPORARY TABLE. CREATE LOCAL TEMPORARY TABLE produces a table > available to all modules but having one instance of its contents per module, > per session. With GLOBAL, by contrast, all modules see the same table > contents during a given session. [ reads spec more closely... ] Yeah, you're right. Will fix, thanks. regards, tom lane