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

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