I was browsing through SQL92 and I noticed this, when discussing the
CREATE VIEW syntax:

"5) Any <table name> that is specified in the <query expression> shall
be different from the <table name> of any <temporary table
declaration>."

(<query expression> is the defintion of the view. This basically says
that you're not allowed to create views on temp tables.)

Currently, PostgreSQL allows this -- when the session ends and the temp
table is dropped, an subsequent queries on the view fail. Is this the
optimal behavior?

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


24 Mar 2002 16:59:32 -0500
Neil Conway <nconway@klamath.dyndns.org>
views on temp tables
Neil Conway wrote:
> I was browsing through SQL92 and I noticed this, when discussing the
> CREATE VIEW syntax:
> 
> "5) Any <table name> that is specified in the <query expression> shall
> be different from the <table name> of any <temporary table
> declaration>."
> 
> (<query expression> is the defintion of the view. This basically says
> that you're not allowed to create views on temp tables.)
> 
> Currently, PostgreSQL allows this -- when the session ends and the temp
> table is dropped, an subsequent queries on the view fail. Is this the
> optimal behavior?

Clearly not optimal.  TODO has:

	* Allow temporary views

My idea would be to make any view temporary that relies on a temp table
--- throw a NOTICE to the user when they create it so they know it is
temporary.  We could allow TEMP on CREATE VIEW but there seems little
reason for that, though we could allow TEMP views on real tables, so I
guess we would need that option too.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 24 Mar 2002 17:35:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: views on temp tables
Neil Conway <nconway@klamath.dyndns.org> writes:
> Currently, PostgreSQL allows this -- when the session ends and the temp
> table is dropped, an subsequent queries on the view fail. Is this the
> optimal behavior?

Well, I think it's better than refusing views on temp tables, as the
spec would have us do.

The "correct" behavior is probably to drop such views on backend exit.
Possibly we should invent the notion of temp views, and disallow
references from non-temp views to temp tables.  That seems like it
might be less likely to cause unpleasant surprises than just silently
dropping views that reference temp tables.

In any case I'd say this is something best tackled in the context of
generalized reference tracking ... which is something we know we need,
but no one's stepped up to make it happen yet.  I don't think this
particular problem is bad enough to warrant a special-purpose
implementation mechanism.

			regards, tom lane

Mon, 25 Mar 2002 03:04:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: views on temp tables 
Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > Currently, PostgreSQL allows this -- when the session ends and the temp
> > table is dropped, an subsequent queries on the view fail. Is this the
> > optimal behavior?
> 
> Well, I think it's better than refusing views on temp tables, as the
> spec would have us do.
> 
> The "correct" behavior is probably to drop such views on backend exit.
> Possibly we should invent the notion of temp views, and disallow
> references from non-temp views to temp tables.  That seems like it
> might be less likely to cause unpleasant surprises than just silently
> dropping views that reference temp tables.

TODO updated with:
	
	* Allow temporary views
	* Require view using temporary tables to be temporary views

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 25 Mar 2002 15:55:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: views on temp tables