Temporary Views

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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073

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

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

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.

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 dependent objects 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

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

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