Thread

1. temporary views

   jasiek <jasiek@lamer.pl> — 2001-10-04T15:51:48Z

   Hi
   I have simple question: How to create view on a temporary table?
   I need this, because pl/pgsql function returns data via temporary table.
   
   View created on a temporary table is useful only to the end of session. 
   Next time i create the same temp table i get 
   "Table xxx with oid xxx doesn't exist"
   
   Tomek
   
   
   
   

2. Re: temporary views

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-04T17:43:22Z

   > Hi
   > I have simple question: How to create view on a temporary table?
   > I need this, because pl/pgsql function returns data via temporary table.
   > 
   > View created on a temporary table is useful only to the end of session. 
   > Next time i create the same temp table i get 
   > "Table xxx with oid xxx doesn't exist"
   
   Just name your temporary table the same name in every session.  Why
   bother with a view. 
   
   -- 
     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
   
   

3. Re: temporary views

   jasiek <jasiek@lamer.pl> — 2001-10-05T15:17:56Z

   Bruce Momjian wrote:
   > 
   > > Hi
   > > I have simple question: How to create view on a temporary table?
   > > I need this, because pl/pgsql function returns data via temporary table.
   > >
   > > View created on a temporary table is useful only to the end of session.
   > > Next time i create the same temp table i get
   > > "Table xxx with oid xxx doesn't exist"
   > 
   > Just name your temporary table the same name in every session.  Why
   > bother with a view.
   
   Creating a view makes my life easier. My temporary table has fields 
   like id1,id2,id3,id4 and view translates it using inner joins to
   name1,name2,name3,name4. This temp table has always the same
   name and I don't want to do the translation inside pl/pgsql function.
   
   Tomek
   
   
   

4. Re: temporary views

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-05T20:42:58Z

   > Bruce Momjian wrote:
   > > 
   > > > Hi
   > > > I have simple question: How to create view on a temporary table?
   > > > I need this, because pl/pgsql function returns data via temporary table.
   > > >
   > > > View created on a temporary table is useful only to the end of session.
   > > > Next time i create the same temp table i get
   > > > "Table xxx with oid xxx doesn't exist"
   > > 
   > > Just name your temporary table the same name in every session.  Why
   > > bother with a view.
   > 
   > Creating a view makes my life easier. My temporary table has fields 
   > like id1,id2,id3,id4 and view translates it using inner joins to
   > name1,name2,name3,name4. This temp table has always the same
   > name and I don't want to do the translation inside pl/pgsql function.
   
   OK, basically there is no way to create views reliably on temp tables:
   	
   	creattest=> create temp table x(y int);
   	CREATE
   	test=> create view z on x as select * from x;
   	ERROR:  parser: parse error at or near "on"
   	test=> create view z as select * from x;
   	CREATE
   	test=> select * from z;
   	 y 
   	---
   	(0 rows)
   
   Of course this works, but exiting the session and restarting it gets
   you:
   
   	test=> create temp table x(y int);
   	CREATE
   	test=> select * from z;
   	ERROR:  Relation "x" with OID 16562 no longer exists
   
   Internally, the problem is that the temp table is referenced by oid, not
   table name.  If you create a temp in a later session, it doesn't have
   the same oid as the one in the session where you created the view.
   
   What actually should happen is that the view should go away at the end
   of the session.  However, unlike indexes, we can have several tables
   involved in a view so it is hard to know exactly how to handle this.
   
   Seems like a TODO item, at least.  What we could do is to create views
   as TEMP if they use temp tables and drop the view as soon as the session
   ends .  You of course would have to recreate the view each time but
   because it is a _temp_ view, it could be done reliably by multiple
   backends at the same time.
   
   Added to TODO:
   
   	* Allow views on temporary tables to behave as 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
   
   

5. Re: temporary views

   Alex Pilosov <alex@pilosoft.com> — 2001-10-05T21:01:33Z

   On Fri, 5 Oct 2001, Bruce Momjian wrote:
   
   > 
   > What actually should happen is that the view should go away at the end
   > of the session.  However, unlike indexes, we can have several tables
   > involved in a view so it is hard to know exactly how to handle this.
   > 
   > Seems like a TODO item, at least.  What we could do is to create views
   > as TEMP if they use temp tables and drop the view as soon as the session
   > ends .  You of course would have to recreate the view each time but
   > because it is a _temp_ view, it could be done reliably by multiple
   > backends at the same time.
   
   
   Didn't someone suggest dropping saving of parsed plans with OIDs
   altogether, and saving the underlying query instead? The point was that
   parser and planner are fast enough to make it unnecessary to save plans. 
   
   I don't remember what was disposition of that idea....
   
   -alex
   
   
   
   

6. Re: temporary views

   Peter Eisentraut <peter_e@gmx.net> — 2001-10-07T17:54:06Z

   Bruce Momjian writes:
   
   > Seems like a TODO item, at least.  What we could do is to create views
   > as TEMP if they use temp tables and drop the view as soon as the session
   > ends .  You of course would have to recreate the view each time but
   > because it is a _temp_ view, it could be done reliably by multiple
   > backends at the same time.
   
   SQL requires that no table in a view declaration be a temporary table.
   
   > Added to TODO:
   >
   > 	* Allow views on temporary tables to behave as temporary views
   
   I don't think this is a good idea.  Especially since our temp tables are
   allowed to shadow persistent tables, it would not be obvious whether the
   view you're creating with any given statement will become persistent or
   temporary.
   
   I think an explicit CREATE TEMPORARY VIEW command would be fair and safe,
   but until that is done we should probably concentrate on the SQL standard
   behaviour.
   
   -- 
   Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter
   
   
   

7. Re: temporary views

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-10-08T02:53:11Z

   Peter Eisentraut <peter_e@gmx.net> writes:
   > Bruce Momjian writes:
   >> * Allow views on temporary tables to behave as temporary views
   
   > I don't think this is a good idea.  Especially since our temp tables are
   > allowed to shadow persistent tables, it would not be obvious whether the
   > view you're creating with any given statement will become persistent or
   > temporary.
   
   > I think an explicit CREATE TEMPORARY VIEW command would be fair and safe,
   > but until that is done we should probably concentrate on the SQL standard
   > behaviour.
   
   I agree with Peter on this --- CREATE TEMP VIEW seems like a fine idea,
   but allowing views to be implicitly made temporary sounds like a good
   way to shoot yourself in the foot.  ISTM a plain CREATE VIEW should
   always create a permanent object, and therefore should error out if it
   refers to any temp tables.
   
   However, there are some interesting implications here for the recurring
   issue of how plpgsql functions ought to interact with temp tables.
   We've generally thought of the current behavior as a simple shortcoming
   of plpgsql's query caching logic, and no doubt it is --- but what
   *should* the behavior be?  Can a long-lived function validly refer to
   short-lived tables?  If so, what should the semantics be, exactly?
   
   			regards, tom lane
   
   

8. Re: temporary views

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-08T03:23:19Z

   > Peter Eisentraut <peter_e@gmx.net> writes:
   > > Bruce Momjian writes:
   > >> * Allow views on temporary tables to behave as temporary views
   > 
   > > I don't think this is a good idea.  Especially since our temp tables are
   > > allowed to shadow persistent tables, it would not be obvious whether the
   > > view you're creating with any given statement will become persistent or
   > > temporary.
   > 
   > > I think an explicit CREATE TEMPORARY VIEW command would be fair and safe,
   > > but until that is done we should probably concentrate on the SQL standard
   > > behaviour.
   > 
   > I agree with Peter on this --- CREATE TEMP VIEW seems like a fine idea,
   > but allowing views to be implicitly made temporary sounds like a good
   > way to shoot yourself in the foot.  ISTM a plain CREATE VIEW should
   > always create a permanent object, and therefore should error out if it
   > refers to any temp tables.
   
   We can handle the temp views thing two ways, either allow views to map
   to temp tables by name, or allow temp views to map to temp tables that
   exist at creation time and drop the views on session exit.  The original
   poster clearly wanted the first behavior, but I agree with Peter that
   the second has fewer surprises for the user and is more standard.
   
   I have updated the TODO item to:
   
   	* Allow temporary views
   
   > However, there are some interesting implications here for the recurring
   > issue of how plpgsql functions ought to interact with temp tables.
   > We've generally thought of the current behavior as a simple shortcoming
   > of plpgsql's query caching logic, and no doubt it is --- but what
   > *should* the behavior be?  Can a long-lived function validly refer to
   > short-lived tables?  If so, what should the semantics be, exactly?
   
   It would be interesting of plpgsql could try for an table match by oid
   first, and if that fails, try a match by table name and match only if a
   temp table is hit.  So basically the only table-name matching that would
   happen would be hits on temp tables.
   
   -- 
     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
   
   

9. Re: temporary views

   jasiek <jasiek@lamer.pl> — 2001-10-08T08:37:48Z

   Bruce Momjian wrote:
   > 
   > We can handle the temp views thing two ways, either allow views to map
   > to temp tables by name, or allow temp views to map to temp tables that
   > exist at creation time and drop the views on session exit.  The original
   > poster clearly wanted the first behavior, but I agree with Peter that
   > the second has fewer surprises for the user and is more standard.
   I think, that referring tables by names would be enough. I found another
   problem connected to this. There is something like this in documetation:
   "Existing permanent tables with the same name are not visible 
   (in this session) while the temporary table exists." Why not to overlap
   permanent table? Currently it doesn't work:
   
   create table x (a integer);
   create view y as select * from x;
   select * from y;
   OK
   create temp table x as select * from x;
   select * from y;
   ERROR:  Relation "x" with OID 364752 no longer exists
   
    
   > I have updated the TODO item to:
   > 
   >         * Allow temporary views
   Nice to hear it.
   
   > It would be interesting of plpgsql could try for an table match by oid
   > first, and if that fails, try a match by table name and match only if a
   > temp table is hit.  So basically the only table-name matching that would
   > happen would be hits on temp tables.
   But why only plpgsql? Would it be difficult to add it to SQL
   implementation
   of PostgreSQL?
   
   Tomek
   
   
   
   

10. Re: temporary views

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-08T16:05:16Z

    > Bruce Momjian wrote:
    > > 
    > > We can handle the temp views thing two ways, either allow views to map
    > > to temp tables by name, or allow temp views to map to temp tables that
    > > exist at creation time and drop the views on session exit.  The original
    > > poster clearly wanted the first behavior, but I agree with Peter that
    > > the second has fewer surprises for the user and is more standard.
    > I think, that referring tables by names would be enough. I found another
    > problem connected to this. There is something like this in documetation:
    > "Existing permanent tables with the same name are not visible 
    > (in this session) while the temporary table exists." Why not to overlap
    > permanent table? Currently it doesn't work:
    > 
    > create table x (a integer);
    > create view y as select * from x;
    > select * from y;
    > OK
    > create temp table x as select * from x;
    > select * from y;
    > ERROR:  Relation "x" with OID 364752 no longer exists
    
    Yes, we could add code that tried the temp table first, and if it didn't
    match the oid, fall back to the permanent table.  Of course, it would
    break the temp table overlap rules.
    
    Of course, there is the question of whether it is worth doing this.  If
    you create the view after the temp table is created it would properly
    map to the temp table.  If you have created a temp table that masks the
    real table, maybe you want the view to fail.
    
    Temp tables masking real tables is already pretty powerful and mapping
    some fallback rules on top of this seems a little too powerful and perhaps
    a little too confusing.
    
    > > It would be interesting of plpgsql could try for an table match by oid
    > > first, and if that fails, try a match by table name and match only if a
    > > temp table is hit.  So basically the only table-name matching that would
    > > happen would be hits on temp tables.
    > But why only plpgsql? Would it be difficult to add it to SQL
    > implementation
    > of PostgreSQL?
    
    Yes, it would be done there 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