Thread: Temporary Views

Hi all,

I'm thinking that temporary views should be pretty trivial to
implement.

* Allow temporary views

This should be as simple as modifying gram.y (to set
ViewStmt->view->istemp) and some logic in RemoveTempRelations() to remove
the view's rule

* Require view using temporary tables to be temporary views

This is the non-trivial part. If you are creating a view on just a
temporary table there's no problem. But what happens when do the
following?

CREATE VIEW abc AS select * from tab1,tab2,temp_tab3 ...

SQL99 avoids this with syntax rule 6 of 11.21 <view definition>
No <table reference> generally contained in the <query expression> shallidentify any declared local temporary table.

There are a few ways it could be implemented:

1) SQL99
2) Views whose <query epression> contains one or more <table references>
to temporary tables are created as temporary views
3) Views whose <query epression> contains one or more <table references>
to temporary tables must be explicitly include the TEMP[ORARY] syntax.

Thoughts?

Gavin

Gavin Sherry wrote:
> Hi all,
> 
> I'm thinking that temporary views should be pretty trivial to
> implement.
> 
> * Allow temporary views
> 
> This should be as simple as modifying gram.y (to set
> ViewStmt->view->istemp) and some logic in RemoveTempRelations() to remove
> the view's rule

Yep, pretty simple.

> * Require view using temporary tables to be temporary views
> 
> This is the non-trivial part. If you are creating a view on just a
> temporary table there's no problem. But what happens when do the
> following?
> 
> CREATE VIEW abc AS select * from tab1,tab2,temp_tab3 ...
> 
> SQL99 avoids this with syntax rule 6 of 11.21 <view definition>
> 
>     No <table reference> generally contained in the <query expression> shall
>     identify any declared local temporary table.
> 
> There are a few ways it could be implemented:
> 
> 1) SQL99
> 2) Views whose <query epression> contains one or more <table references>
> to temporary tables are created as temporary views
> 3) Views whose <query epression> contains one or more <table references>
> to temporary tables must be explicitly include the TEMP[ORARY] syntax.

The idea is that if the temp table goes away, we don't want the view
continuing to exist.  I think if there are any temp tables in the view,
the view _has_ to be specified by the user as temporary, or we throw an
error telling them it has to be temporary.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Gavin Sherry <swm@linuxworld.com.au> writes:
> I'm thinking that temporary views should be pretty trivial to
> implement.

... except not so trivial, per the rest of your note.

Do we actually need any such feature?  Views on temp tables already work
correctly in CVS tip: the implicit DROP CASCADE on temp tables at
backend exit makes such views go 'way too.
        regards, tom lane

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > I'm thinking that temporary views should be pretty trivial to
> > implement.
> 
> ... except not so trivial, per the rest of your note.
> 
> Do we actually need any such feature?  Views on temp tables already work
> correctly in CVS tip: the implicit DROP CASCADE on temp tables at
> backend exit makes such views go 'way too.

Oh.  but RESTRICT is the default.  Seems like the view should go away no
matter what, and if they mix temp and non-temp tables, is it obvious
that the view will disappear if they didn't specify TEMP on view
creation.

I can go either way, but I want to make sure we agree so I can modify
the TODO accordingly.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 2002-08-13 at 11:11, Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > I'm thinking that temporary views should be pretty trivial to
> > implement.
> 
> ... except not so trivial, per the rest of your note.
> 
> Do we actually need any such feature?  Views on temp tables already work
> correctly in CVS tip: the implicit DROP CASCADE on temp tables at
> backend exit makes such views go 'way too.

I was playing with this a while back (when I had initially added CASCADE
to tables).  I believe that in the event of a crash the temp tables are
not removed until their next use.  This means that stale *real* items
may litter the system but the temp table no longer exists in these rare
occurrences.

However, having all temporary items removed during backend startup would
remove this case.

On Tue, 2002-08-13 at 11:18, Bruce Momjian wrote:
> Tom Lane wrote:
> > Gavin Sherry <swm@linuxworld.com.au> writes:
> > > I'm thinking that temporary views should be pretty trivial to
> > > implement.
> > 
> > ... except not so trivial, per the rest of your note.
> > 
> > Do we actually need any such feature?  Views on temp tables already work
> > correctly in CVS tip: the implicit DROP CASCADE on temp tables at
> > backend exit makes such views go 'way too.
> 
> Oh.  but RESTRICT is the default.  Seems like the view should go away no
> matter what, and if they mix temp and non-temp tables, is it obvious
> that the view will disappear if they didn't specify TEMP on view
> creation.

When the backend exits the code that removes temp tables is CASCADE by
default and anything depending on it will disappear.

Rod Taylor wrote:
> On Tue, 2002-08-13 at 11:18, Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Gavin Sherry <swm@linuxworld.com.au> writes:
> > > > I'm thinking that temporary views should be pretty trivial to
> > > > implement.
> > > 
> > > ... except not so trivial, per the rest of your note.
> > > 
> > > Do we actually need any such feature?  Views on temp tables already work
> > > correctly in CVS tip: the implicit DROP CASCADE on temp tables at
> > > backend exit makes such views go 'way too.
> > 
> > Oh.  but RESTRICT is the default.  Seems like the view should go away no
> > matter what, and if they mix temp and non-temp tables, is it obvious
> > that the view will disappear if they didn't specify TEMP on view
> > creation.
> 
> When the backend exits the code that removes temp tables is CASCADE by
> default and anything depending on it will disappear.

Oh, OK, that is interesting.  So that only leaves the issue of not
specifying TEMP in a case of views using mixed temp/non-temp tables.  We
don't specify TEMP when creating an index on a temp table, and it is
auto-destroyed.  I guess it is OK that we don't specify TEMP on a view
creation using a temp table, except that the view can have a mix of temp
and non-temp while an index is just on one table.

I can go either way on this.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can go either way on this.

AFAICS "create temp view" would have some small advantage of keeping the
view's name out of possibly-public permanent namespaces, so the step of
just adding the TEMP option to CREATE VIEW may be worth doing.  The
advantage isn't very big but neither is the amount of work.

Trying to prohibit non-temp views on temp tables strikes me as more work
than it's worth; that TODO item was written before we had dependencies,
and I think it's obsolete.  Basically the point of the TODO was to avoid
having broken views --- and we have solved that problem.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I can go either way on this.
> 
> AFAICS "create temp view" would have some small advantage of keeping the
> view's name out of possibly-public permanent namespaces, so the step of
> just adding the TEMP option to CREATE VIEW may be worth doing.  The
> advantage isn't very big but neither is the amount of work.

What about indexes?  Do indexes on temp tables exist in the temp
namespace?  I would think they should by default, as well as views based
on temp tables.  Certainly no one else should be able to see the temp
index/views.

> Trying to prohibit non-temp views on temp tables strikes me as more work
> than it's worth; that TODO item was written before we had dependencies,
> and I think it's obsolete.  Basically the point of the TODO was to avoid
> having broken views --- and we have solved that problem.

Yes, if it auto-temps because it is based on a temp object, that is fine
by me.  However, based on your comments above, I think it should
auto-temp fully, rather than just auto-destroy.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What about indexes?  Do indexes on temp tables exist in the temp
> namespace?

Yes, a fortiori: any index exists in its table's namespace.  Seems
pretty irrelevant to the point at hand, though.
        regards, tom lane

Rod Taylor <rbt@zort.ca> writes:
> I was playing with this a while back (when I had initially added CASCADE
> to tables).  I believe that in the event of a crash the temp tables are
> not removed until their next use.  This means that stale *real* items
> may litter the system but the temp table no longer exists in these rare
> occurrences.

Huh?  The view goes away at exactly the same time the temp table does.
If you suffer a backend crash then that may be postponed ... but the
view continues to work up till the instant that it's removed.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > What about indexes?  Do indexes on temp tables exist in the temp
> > namespace?
> 
> Yes, a fortiori: any index exists in its table's namespace.  Seems
> pretty irrelevant to the point at hand, though.

Just checking.  So the index exists in the same namespace as the table. 
Makes sense.  Same with sequences, I assume.

Of course, views can represent multiple tables so I think they should go
into the names space with the temp tables.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 2002-08-13 at 12:22, Tom Lane wrote:
> Rod Taylor <rbt@zort.ca> writes:
> > I was playing with this a while back (when I had initially added CASCADE
> > to tables).  I believe that in the event of a crash the temp tables are
> > not removed until their next use.  This means that stale *real* items
> > may litter the system but the temp table no longer exists in these rare
> > occurrences.
> 
> Huh?  The view goes away at exactly the same time the temp table does.
> If you suffer a backend crash then that may be postponed ... but the
> view continues to work up till the instant that it's removed.

After a backend crash the temp tables exist, but are not usable by the
current backend as it is different than the one which originally created
the temp table (the crash causing a restart and everything).

So non-temp items which depend on the no longer usable temp table will
be broken until they are scrubbed, which does not happen until the next
time a temp table is created.


Not that it really matters, but moving a temp-table destruction event
into the startup sequence would solve it.

On Tue, 2002-08-13 at 20:43, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I can go either way on this.
> 
> AFAICS "create temp view" would have some small advantage of keeping the
> view's name out of possibly-public permanent namespaces, so the step of
> just adding the TEMP option to CREATE VIEW may be worth doing.  The
> advantage isn't very big but neither is the amount of work.

Actually I think that having the views on any temp table also temp is
mandatory, or else these views will be broken in all other backends than
the one that created them (or expose other backends temp tables and are
thereby a security risk).

> Trying to prohibit non-temp views on temp tables strikes me as more work
> than it's worth;

What I would expect (if I had not read this thread and did not know
anything about PG's view implementation) would be that if view on temp
table was not defined temp itself, it would be automatically recompiled
on first use after the temp table was created in current session. So
forcing it to be explicitly declared TEMP would save me from that
mistake. 

I'd expect automatic recompilation of view to be done sometime in future
via saving view definition text, so that 'select * from t' would still
return all columns after "alter table t add column k"

> that TODO item was written before we had dependencies, and I think
> it's obsolete.  Basically the point of the TODO was to avoid
> having broken views --- and we have solved that problem.

We may have broken views again when "alter table drop column" gets done
.

----------
Hannu

> > that TODO item was written before we had dependencies, and I think
> > it's obsolete.  Basically the point of the TODO was to avoid
> > having broken views --- and we have solved that problem.
> 
> We may have broken views again when "alter table drop column" gets done

Any view depending on a column which is dropped should also be removed
via the dependency code.  Views won't break but you can't drop a column
that is used in a view without specifying cascade.

This is a case where create or replace view is useful.  Change the view
definition to no longer be dependent on the object you wish to drop.

On Wed, 2002-08-14 at 00:10, Rod Taylor wrote:
> > > that TODO item was written before we had dependencies, and I think
> > > it's obsolete.  Basically the point of the TODO was to avoid
> > > having broken views --- and we have solved that problem.
> > 
> > We may have broken views again when "alter table drop column" gets done
> 
> Any view depending on a column which is dropped should also be removed
> via the dependency code.  Views won't break but you can't drop a column
> that is used in a view without specifying cascade.
> 
> This is a case where create or replace view is useful.  Change the view
> definition to no longer be dependent on the object you wish to drop.

in case of a 'SELECT *' view it could just be an (automatic) recompile.

the same in case column type gets changed.

---------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> We may have broken views again when "alter table drop column" gets done

It is done, and we do not have broken views.

regression=# create table t (f1 int, f2 int, f3 int);
CREATE TABLE
regression=# create view v as select f1,f2 from t;
CREATE VIEW
regression=# alter table t drop column f3;
ALTER TABLE
regression=# alter table t drop column f2;
NOTICE:  rule _RETURN on view v depends on table t column f2
NOTICE:  view v depends on rule _RETURN on view v
ERROR:  Cannot drop table t column f2 because other objects depend on it       Use DROP ... CASCADE to drop the
dependentobjects too
 
regression=#
        regards, tom lane

On Tue, 2002-08-13 at 21:50, Hannu Krosing wrote:
> On Tue, 2002-08-13 at 20:43, Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > I can go either way on this.
> > 
> > AFAICS "create temp view" would have some small advantage of keeping the
> > view's name out of possibly-public permanent namespaces, so the step of
> > just adding the TEMP option to CREATE VIEW may be worth doing.  The
> > advantage isn't very big but neither is the amount of work.
> 
> Actually I think that having the views on any temp table also temp is
> mandatory, or else these views will be broken in all other backends than
> the one that created them (or expose other backends temp tables and are
> thereby a security risk).

It seems to be a broken view not security risk in 7.2.1

backend 1:

hannu=# create temp table tmp_t1( i int);
CREATE
hannu=# create view tmp_v1 as select * from tmp_t1;
CREATE
hannu=# select * from tmp_v1;i 
---
(0 rows)


backend 2:

hannu=# select * from tmp_v1;
ERROR:  Relation "tmp_t1" does not exist
hannu=# create temp table tmp_t1( i int);
CREATE
hannu=# select * from tmp_v1;
ERROR:  Relation "tmp_t1" with OID 34281 no longer exists

----------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> It seems to be a broken view not security risk in 7.2.1

The implementation of temp tables has changed completely in CVS tip,
so experiments with 7.2 aren't very relevant.  In CVS tip I believe
you *could* read the contents of someone else's temp table, assuming
you had permissions to read the view.  However, you'd not be guaranteed
to get up-to-date information, since the guy who actually owns the temp
table would be using his local-buffer manager for access to it; there
might be many pages that you'd see stale information from because the
only up-to-date copy is in local memory of the owning backend.

I see some potential for confusion here, but not really any
crash-the-database scenarios.  I also do not see a security risk:
you did grant the other guy read permission on your view, after all.
        regards, tom lane

Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > It seems to be a broken view not security risk in 7.2.1
> 
> The implementation of temp tables has changed completely in CVS tip,
> so experiments with 7.2 aren't very relevant.  In CVS tip I believe
> you *could* read the contents of someone else's temp table, assuming
> you had permissions to read the view.  However, you'd not be guaranteed
> to get up-to-date information, since the guy who actually owns the temp
> table would be using his local-buffer manager for access to it; there
> might be many pages that you'd see stale information from because the
> only up-to-date copy is in local memory of the owning backend.
> 
> I see some potential for confusion here, but not really any
> crash-the-database scenarios.  I also do not see a security risk:
> you did grant the other guy read permission on your view, after all.

Does every other user see the view name on his temp table?  Can two
people create a view on a temp table at the same time?  It seems not:test=> create temp table x1(x int);CREATE
TABLEtest=>create view x2 as select * from x1;ERROR:  Relation 'x2' already exist
 

Seems this should work.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Does every other user see the view name

If he looks in the schema where the view is created, sure --- it's no
different from any other non-temp table or view.

> ... on his temp table?

Um, are you thinking that a view V created to refer to user 1's temp
table T would refer to some other user's temp table T if accessed by
that other user?  That's not how it works.  The reference is absolute,
ie, by OID.

> Can two
> people create a view on a temp table at the same time?

Not the same name in the same schema, but this has nothing to do with
what the views refer to; that's just a plain old name collision.

CREATE TEMP VIEW would be handy for avoiding name collisions if you want
to use the same temp view name in different clients.  But it'd only hide
the *name* of the view (by putting it in your private temp schema); it
has nothing to do with access semantics.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Does every other user see the view name
> 
> If he looks in the schema where the view is created, sure --- it's no
> different from any other non-temp table or view.
> 
> > ... on his temp table?
> 
> Um, are you thinking that a view V created to refer to user 1's temp
> table T would refer to some other user's temp table T if accessed by
> that other user?  That's not how it works.  The reference is absolute,
> ie, by OID.
> 
> > Can two
> > people create a view on a temp table at the same time?
> 
> Not the same name in the same schema, but this has nothing to do with
> what the views refer to; that's just a plain old name collision.
> 
> CREATE TEMP VIEW would be handy for avoiding name collisions if you want
> to use the same temp view name in different clients.  But it'd only hide
> the *name* of the view (by putting it in your private temp schema); it
> has nothing to do with access semantics.

Yes, I realize that, but when I create an index on a temp table, I can
create it even though someone else tries to do the same in another
session.  If these views on temp tables go away on session exit, and
can't be reliably accessed by other users, they should be in the temp
schema and therefore invisible to other users and to prevent name
conflicts.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, I realize that, but when I create an index on a temp table, I can
> create it even though someone else tries to do the same in another
> session.  If these views on temp tables go away on session exit, and
> can't be reliably accessed by other users, they should be in the temp
> schema and therefore invisible to other users and to prevent name
> conflicts.

I think we should provide the *facility* for temp views; that doesn't
equate to feeling that we must have an enforcement mechanism to prevent
you from using a non-temp view on a temp table.  The enforcement
mechanism would be notably more work to write and would slow down the
creation of views (at least non-temp ones), in order to achieve what?
Not much that I can see.  Admittedly, it's a bit silly to use a
non-temp view with a temp table, but I don't think the system needs to
go out of its way to prevent silliness.

(Come to think of it, it might not be completely silly to do, either.
Suppose you want to use a temp table, but some legacy bit of code
insists on accessing the table using a fully-qualified schema name.
You could create a view foo.bar that references temp table baz, and
thereby bypass the fact that you don't know a schema name for baz.
A bit far-fetched I agree, since if the legacy code is in your client
app you can probably fix it instead; but maybe there are more legitimate
uses.)
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, I realize that, but when I create an index on a temp table, I can
> > create it even though someone else tries to do the same in another
> > session.  If these views on temp tables go away on session exit, and
> > can't be reliably accessed by other users, they should be in the temp
> > schema and therefore invisible to other users and to prevent name
> > conflicts.
> 
> I think we should provide the *facility* for temp views; that doesn't
> equate to feeling that we must have an enforcement mechanism to prevent
> you from using a non-temp view on a temp table.  The enforcement
> mechanism would be notably more work to write and would slow down the
> creation of views (at least non-temp ones), in order to achieve what?
> Not much that I can see.  Admittedly, it's a bit silly to use a
> non-temp view with a temp table, but I don't think the system needs to
> go out of its way to prevent silliness.

My feeling is that either the view is temporary, fully, or it isn't.  I
don't see having it in the public namespace _and_ removing it on session
exit as defensible.  I would like to update the TODO list to say:
Place views on temporary tables in temporary namespaces

It may be difficult, but I clearly think it is a bug if we don't do it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> My feeling is that either the view is temporary, fully, or it isn't.  I
> don't see having it in the public namespace _and_ removing it on session
> exit as defensible.

I disagree ... (and who said this was necessarily the public namespace,
anyway?  Perhaps the view is in a private, but not temp namespace.)

But I doubt we'll convince each other.  Can we hear some other opinions?
        regards, tom lane

On Wed, 2002-08-14 at 04:12, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, I realize that, but when I create an index on a temp table, I can
> > create it even though someone else tries to do the same in another
> > session.  If these views on temp tables go away on session exit, and
> > can't be reliably accessed by other users, they should be in the temp
> > schema and therefore invisible to other users and to prevent name
> > conflicts.
> 
> I think we should provide the *facility* for temp views; that doesn't
> equate to feeling that we must have an enforcement mechanism to prevent
> you from using a non-temp view on a temp table.  The enforcement
> mechanism would be notably more work to write and would slow down the
> creation of views (at least non-temp ones),

AFAIC we already have pg_depends, how much more work would it be to
check that none of the tables a new view depends on are temp tables ?

Both the table definitions and pg_depend entries should be still in
cache.

It should only be noticably slower in case we have to rollback the view
creation for non-temp view on temp table case. I could live with error
handling being a bit slow.

> in order to achieve what?
> Not much that I can see.  Admittedly, it's a bit silly to use a
> non-temp view with a temp table, but I don't think the system needs to
> go out of its way to prevent silliness.

It would be a pity to lose lots of views on connection close just
because one of then happend to reference a temp table. If the view is in
fact temporary (in the temporal sense ;) then it better be declared as
such. 

Giving the least amount of surprise to users is always a good policy.

> (Come to think of it, it might not be completely silly to do, either.
> Suppose you want to use a temp table, but some legacy bit of code
> insists on accessing the table using a fully-qualified schema name.
> You could create a view foo.bar that references temp table baz, and
> thereby bypass the fact that you don't know a schema name for baz.

This seems to be a workaround for the fact that we store temp tables in
their own schema.

BTW, what does SQL standard say about using TEMP schemas for TEMP
tables?

> A bit far-fetched I agree, since if the legacy code is in your client
> app you can probably fix it instead; but maybe there are more legitimate
> uses.)

You mean a scenario where the legacy client code creates a temp table
but dont know how to access it ?

Perhaps we should have SYNONYMS/ALIASES for such cases. They should act
like symlinks.

-------------
Hannu

On Tue, 13 Aug 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > My feeling is that either the view is temporary, fully, or it isn't.  I
> > don't see having it in the public namespace _and_ removing it on session
> > exit as defensible.
> 
> I disagree ... (and who said this was necessarily the public namespace,
> anyway?  Perhaps the view is in a private, but not temp namespace.)
> 
> But I doubt we'll convince each other.  Can we hear some other opinions?

SQL99 is pretty clear about temporary tables, at least.

From SQL99 4.16 'Tables' pp. 40,41

---

A global temporary table is a named table defined by a <table definition>
that specifies GLOBAL TEMPORARY. A created local temporary table is a
named table defined by a <table definition> that specifies LOCAL
TEMPORARY. Global and created local temporary tables are effectively
materialized only when referenced in an SQL-session. Every SQL-client
module in every SQL-session that references a created local temporary
table causes a distinct instance of that created local temporary table to
be materialized. That is, the contents of a global temporary table or a
created local temporary table cannot be shared between SQL-sessions.

In addition, the contents of a created local temporary table cannot be
shared between SQL-client modules of a single SQL-session. The definition
of a global temporary table or a created local temporary table appears in
a schema. In SQL language, the name and the scope of the name of a global
temporary table or a created local temporary table are indistinguishable
from those of a persistent base table. However, because global temporary
table contents are distinct within SQL-sessions, and created local
temporary tables are distinct within SQL-client modules within
SQL-sessions, the effective <schema name> of the schema in which the
global temporary table or the created local temporary table is
instantiated is an implementation-dependent <schemaname> that may be
thought of as having been effectively derived from the <schema name> of
the schema in which the global temporary table or created local temporary
table is defined and the implementation-dependent SQL-session identifier
associated with the SQL-session.

In addition, the effective <schema name> of the schema in which the
created local temporary table is instantiated may be thought of as being
further qualified by a unique implementation-dependent name associated
with the SQL-client module in which the created local temporary table is
referenced.

---

So surely SQL sessions should not be able to see each other's temporary
tables the the views dependent upon them.

Gavin

You want to hear something funny?  When I see the standards quoted, I
jump to the end to find out what the person says it really means.  I
find reading those standards painful.  I am glad others are reading
them.

---------------------------------------------------------------------------

Gavin Sherry wrote:
> SQL99 is pretty clear about temporary tables, at least.
> 
> >From SQL99 4.16 'Tables' pp. 40,41
> 
> ---
> 
> A global temporary table is a named table defined by a <table definition>
> that specifies GLOBAL TEMPORARY. A created local temporary table is a
> named table defined by a <table definition> that specifies LOCAL
> TEMPORARY. Global and created local temporary tables are effectively
> materialized only when referenced in an SQL-session. Every SQL-client
> module in every SQL-session that references a created local temporary
> table causes a distinct instance of that created local temporary table to
> be materialized. That is, the contents of a global temporary table or a
> created local temporary table cannot be shared between SQL-sessions.
> 
> In addition, the contents of a created local temporary table cannot be
> shared between SQL-client modules of a single SQL-session. The definition
> of a global temporary table or a created local temporary table appears in
> a schema. In SQL language, the name and the scope of the name of a global
> temporary table or a created local temporary table are indistinguishable
> from those of a persistent base table. However, because global temporary
> table contents are distinct within SQL-sessions, and created local
> temporary tables are distinct within SQL-client modules within
> SQL-sessions, the effective <schema name> of the schema in which the
> global temporary table or the created local temporary table is
> instantiated is an implementation-dependent <schemaname> that may be
> thought of as having been effectively derived from the <schema name> of
> the schema in which the global temporary table or created local temporary
> table is defined and the implementation-dependent SQL-session identifier
> associated with the SQL-session.
> 
> In addition, the effective <schema name> of the schema in which the
> created local temporary table is instantiated may be thought of as being
> further qualified by a unique implementation-dependent name associated
> with the SQL-client module in which the created local temporary table is
> referenced.
> 
> ---
> 
> So surely SQL sessions should not be able to see each other's temporary
> tables the the views dependent upon them.
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 13 Aug 2002, Bruce Momjian wrote:

> 
> You want to hear something funny?  When I see the standards quoted, I
> jump to the end to find out what the person says it really means.  I
> find reading those standards painful.  I am glad others are reading
> them.

HAH!

I did a degree in the history of English literature and French
philosophy. I pretty used to reading verbose, obtuse, convoluted
literature.

Gavin

Gavin Sherry wrote:
> On Tue, 13 Aug 2002, Bruce Momjian wrote:
> 
> > 
> > You want to hear something funny?  When I see the standards quoted, I
> > jump to the end to find out what the person says it really means.  I
> > find reading those standards painful.  I am glad others are reading
> > them.
> 
> HAH!
> 
> I did a degree in the history of English literature and French
> philosophy. I pretty used to reading verbose, obtuse, convoluted
> literature.

I can't handle Shakespeare nor poetry because it is too confusing.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

"Sander Steffann" <sander@steffann.nl> writes:
> Now I am thinking about it, referential integrity also behaves funny with
> temp tables. The following is allowed:

>> create temp table a (x int primary key);
>> create table b (y int references a(x));

It is?

regression=# create temp table a (x int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a'
CREATE TABLE
regression=# create table b (y int references a(x));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  ALTER TABLE / ADD CONSTRAINT: Unable to reference temporary table from permanent table constraint

Now I realize that this existing special-case argues against the
position I'm taking.  But let's argue from actual facts, not
misstatements...
        regards, tom lane

Gavin Sherry <swm@linuxworld.com.au> writes:
> SQL99 is pretty clear about temporary tables, at least.

It is ... and in fact the spec's notion of a temp table has nearly
nothing to do with ours.  They contemplate a temp table as an abstract
table schema, if you will, that gets instantiated locally within a
session upon first use.  There is no ability in the spec for two
sessions to create unrelated temp tables of the same name --- their temp
tables of the same name must share the same, predefined schema.

I wasn't around when PG's temp table concept was created, but I think
it's considerably superior to the spec's concept.

I'm willing to compare the spec's notions for guidance, but we must not
take it as gospel when we're deciding how temp objects should behave.
Their concept of temp-ness is different and very much more limited.
        regards, tom lane

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > SQL99 is pretty clear about temporary tables, at least.
> 
> It is ... and in fact the spec's notion of a temp table has nearly
> nothing to do with ours.  They contemplate a temp table as an abstract
> table schema, if you will, that gets instantiated locally within a
> session upon first use.  There is no ability in the spec for two
> sessions to create unrelated temp tables of the same name --- their temp
> tables of the same name must share the same, predefined schema.
> 
> I wasn't around when PG's temp table concept was created, but I think
> it's considerably superior to the spec's concept.

That was me.  I think you were around, though.

> I'm willing to compare the spec's notions for guidance, but we must not
> take it as gospel when we're deciding how temp objects should behave.
> Their concept of temp-ness is different and very much more limited.

Well, again, looking at desired practice, I can't see how it can be
argued that having a view on a temp table _not_ exist the temporary
namespace can be defended, and everyone else seems to think it should,
so, added to TODO:
 * Have views on temporary tables exist in the temporary namespace 

TODO updated to remove mention of temporary views.  We sort of now have
temporary views, but not completely so I can't mark those items as done;
I just removed them:
 * Allow temporary views * Require view using temporary tables to be temporary views

They don't behave 100% as temporary because of the namespace conflict.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> so, added to TODO:
>   * Have views on temporary tables exist in the temporary namespace 
> TODO updated to remove mention of temporary views.

That's *clearly* backwards.  Ignoring our little argument, I think there
is no denying that temp views as such are useful --- for example,
consider a temp view created on permanent tables to define a
session-local shorthand for a complex query.

If you want a restriction, make it* Invent temporary views* Restrict permanent views from referring to temporary
tables
I agree with the first goal and disagree with the second --- but I can
see your point of view on the second.  OTOH I can't see any reason to
hardwire tempness-of-views to tempness-of-referenced-tables, because
that disallows the obviously useful case of temp view on permanent
table.

> I just removed them:
>   * Allow temporary views
>   * Require view using temporary tables to be temporary views

It was right the first time.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > so, added to TODO:
> >   * Have views on temporary tables exist in the temporary namespace 
> > TODO updated to remove mention of temporary views.
> 
> That's *clearly* backwards.  Ignoring our little argument, I think there
> is no denying that temp views as such are useful --- for example,
> consider a temp view created on permanent tables to define a
> session-local shorthand for a complex query.

Oh, yes, good point.

> If you want a restriction, make it
>     * Invent temporary views
>     * Restrict permanent views from referring to temporary tables
> I agree with the first goal and disagree with the second --- but I can
> see your point of view on the second.  OTOH I can't see any reason to
> hardwire tempness-of-views to tempness-of-referenced-tables, because
> that disallows the obviously useful case of temp view on permanent
> table.
> 
> > I just removed them:
> >   * Allow temporary views
> >   * Require view using temporary tables to be temporary views
> 
> It was right the first time.

Well, my real quandary is "what do we have now when we create a view
referencing a temporary table?"  We have the session-drop of temporary
views,  but not the visibility restrictions of temporary views, so I
can't put "Invent temporary views".  In fact, given that indexes
auto-temp themselves, I think views should too, but fully, not just in
drop behavior.

Right now, I can do this in two sesssions at the same time:test=> create temp table xxy(x int);CREATE TABLEtest=>
createindex xxa on xxy(x);  CREATE INDEX
 

The temp-ness tracks to dependent objects, in visibility too.

In fact, we can't create a temporary index on a permanent table in our
current sources. It just auto-temps because it is on a temp table. 
Views should do the same.

We can add the ability to do explicit temp views on permanent tables,
but no one has asked for that yet, as no one has asked for temp indexes;
temp indexes just happen on their own because it they use temp tables.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > so, added to TODO:
> >   * Have views on temporary tables exist in the temporary namespace 
> > TODO updated to remove mention of temporary views.
> 
> That's *clearly* backwards.  Ignoring our little argument, I think there
> is no denying that temp views as such are useful --- for example,
> consider a temp view created on permanent tables to define a
> session-local shorthand for a complex query.

Yes, if someone wants that, we can add such a capability to the TODO
list, it is just that no one has asked yet.  What they have asked for is
proper handling of views on temporary tables, and we have only the drop
part of that, not the visiblity part, and frankly, without the
visibility part, it is pretty useless.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Wed, 2002-08-14 at 08:32, Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > SQL99 is pretty clear about temporary tables, at least.
> 
> It is ... and in fact the spec's notion of a temp table has nearly
> nothing to do with ours.  They contemplate a temp table as an abstract
> table schema, if you will, that gets instantiated locally within a
> session upon first use.  There is no ability in the spec for two
> sessions to create unrelated temp tables of the same name --- their temp
> tables of the same name must share the same, predefined schema.

What I see is that the (local) TEMP table should be indistinguishable
from persistent table at the SQL level, but visible only in the module
that creates it. So it seems to say that TEMP tables should not
_visibly_ be in separate schemas from the rest of tables, which again
seems to imply that you should be able to reference them with
<schema>.<table> .

> I wasn't around when PG's temp table concept was created, but I think
> it's considerably superior to the spec's concept.

It seems much like the spec's concept of LOCAL TEMP, with the exception
that they are visible everywhere, not only in defining "module"
(whatever a module is ;)

> I'm willing to compare the spec's notions for guidance, but we must not
> take it as gospel when we're deciding how temp objects should behave.

I just brought it up for your hypothetical case when temp tables needed
to be referenced by full <schema>.<table> syntax. I don't think it will
really be an issue.

> Their concept of temp-ness is different and very much more limited.

Not sure about the "more limited" part. I really can't see good reason
for having two tables with the same name but different structure.

Not needing to define the temp table in each and every session may seem
desirable for some people.

Perhaps we need to get some description of differences from the spec
into our docs. Currently there seems to be _no_ mention of temp tables
at all, at least in Users Guide.

----------
Hannu