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

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