Re: [COMMITTERS] pgsql: Add ERROR msg for GLOBAL/LOCAL TEMP is not yet implemented

> 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(dot)Grittner(at)wicourts(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)leadboat(dot)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(at)leadboat(dot)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(at)leadboat(dot)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 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.

nm

Noah Misch <noah(at)leadboat(dot)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