Thread: temporary views
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
> 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, Pennsylvania19026
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
> 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);CREATEtest=>create view z on x as select * from x;ERROR: parser: parse error at or near "on"test=> create view z asselect * from x;CREATEtest=> 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);CREATEtest=> 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, Pennsylvania19026
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
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
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
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026