Thread: Dropping a temporary view?

On 2024-03-20 15:58 +0100, Celia McInnis wrote:
> I am using postresql 16, am trying to use temporary views in a piece of
> software that I am writing, and would like it to be able to drop and
> recreate temporary views. It seems from the documentation that I can only
> use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the same
> columns, so Is there a correct way to drop a temporary view?
> 
> I can create a temporary view, but get a syntax error when I do what I
> thought would drop it. Here is a simple example of what doesn't work:
> 
> tt=# create temporary view tempview as select now() as junk;
> CREATE VIEW
> tt=# select * from tempview;
>              junk
> -------------------------------
>  2024-03-20 14:21:27.441168+00
> (1 row)
> 
> tt=# drop temporary view tempview;
> ERROR:  syntax error at or near "temporary"
> LINE 1: drop temporary view tempview;
>              ^

It's just DROP VIEW for normal and temporary views.

> Also, when I then tried (I formerly had a non-temporary view called
> tempview)
> 
> DROP VIEW tempview;
> DROP VIEW
> 
> postgresql did that successfully, but when I then did
> 
> select * from tempview:
> 
> postgresql hung for a long time (more than 7 minutes) before returning the
> contents of some previous view tempview (a previous (temporary, I guess)
> view by that name that was created by my software when I was not creating a
> temporary view?). I really wasn't expecting this, so if someone can
> explain, that would be great.

The first view must have been a regular (non-temporary) one.  It is then
possible to create a temporary view of the same name that shadows the
original view if pg_temp is searched first, which is the default if you
haven't modified search_path.  But it's not possible to create a second
temporary view of the same name because they live in the same namespace
(pg_temp_N):

    regress=# create view tempview as select 1 a;
    CREATE VIEW
    regress=# select * from tempview;
     a
    ---
     1
    (1 row)
    
    regress=# create temp view tempview as select 2 b;
    CREATE VIEW
    regress=# select * from tempview;
     b
    ---
     2
    (1 row)
    
    regress=# create temp view tempview as select 3 c;
    ERROR:  relation "tempview" already exists
    regress=# select * from tempview;
     b
    ---
     2
    (1 row)
    
    regress=# drop view tempview;
    DROP VIEW
    regress=# select * from tempview;
     a
    ---
     1
    (1 row)

-- 
Erik

On 3/20/24 08:39, Celia McInnis wrote:
> Ok, thanks - so I guess that means that if there is both a temporary and 
> a non temporary view called "tempvie",
> 
> DROP VIEW tempview;
> 
> will remove the 1st tempview found, which with my path is the temporary 
> one. Is there some reason why it then took 7 minutes to select from the 
> non-temporary view tempview after I dropped the temporary view tempview?
> 
> I have sometimes had some very long query times when running query 
> software, and maybe they are resulting from my switching between 
> temporary and non-temporary views of the same name while debugging. If 
> so, is there something I should be doing to clean up any temporary 
> messes I am creating?

What is the purpose of the temp view over the the regular view process?

How do they differ in data?

Is all the above happening in one session?

Have you run EXPLAIN ANALYZE on the select from the regular view?

> 
> Thanks,
> Celia McInnis
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Is there some reason why it then took 7 minutes to select from the non-temporary view tempview after I dropped the temporary view tempview?

> On Mar 20, 2024, at 09:51, Celia McInnis <celia.mcinnis@gmail.com> wrote:
>
> The view is being used in some web query software that multiple people will be accessing and the contents of the view
dependon what the person is querying, so I think that temporary views or tables are a good idea. 

There's nothing wrong with temporary views or tables, and the use-case you describe is a reasonable one.  The issue
comesup when they have the same name as a permanent view or table. 

It's deterministic which one a query will use.  All temporary objects are in the pseudo-schema `pg_temp` (it's a
"pseudo-schema"because it's an alias to the current session's temporary schema, rather than a schema itself).  By
default,pg_temp is absent from search_path, which is treated as if it were the first entry, so temporary tables and
views"mask" the permanent ones.  However, if that temporary object doesn't happen to exist, or if pg_temp is explicitly
movedto a different position in the search path, you could have some surprising behavior. 

On 3/20/24 10:51, Celia McInnis wrote:
> The view is being used in some web query software that multiple people 
> will be accessing and the contents of the view depend on what the 
> person is querying, so I think that temporary views or tables are a 
> good idea. I change to non-temporary views or tables (in a test 
> version of the software which is not web-crawl-able) when I'm trying 
> to debug things, and I guess I have to be careful to clean those up 
> when I switch back to the temporary tables/views.
>
>
Are multiple people seeing the same dataset, or is each getting their 
own data albeit of the same structure?  What mechanism populates the 
web-page?  I ask because I think you might not need to make database 
structures at all.

On 3/20/24 09:51, Celia McInnis wrote:
> The view is being used in some web query software that multiple people 
> will be accessing and the contents of the view depend on what the person 
> is querying, so I think that temporary views or tables are a good idea. 
> I change to non-temporary views or tables (in a test version of the 
> software which is not web-crawl-able) when I'm trying to debug things, 
> and I guess I have to be careful to clean those up when I switch back to 
> the temporary tables/views.

Why change behavior for the tests? Seems that sort of negates the value 
of the testing.

Have you run EXPLAIN ANALYZE on the problem query?


> 
> 
> 
> On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 3/20/24 08:39, Celia McInnis wrote:
>      > Ok, thanks - so I guess that means that if there is both a
>     temporary and
>      > a non temporary view called "tempvie",
>      >
>      > DROP VIEW tempview;
>      >
>      > will remove the 1st tempview found, which with my path is the
>     temporary
>      > one. Is there some reason why it then took 7 minutes to select
>     from the
>      > non-temporary view tempview after I dropped the temporary view
>     tempview?
>      >
>      > I have sometimes had some very long query times when running query
>      > software, and maybe they are resulting from my switching between
>      > temporary and non-temporary views of the same name while
>     debugging. If
>      > so, is there something I should be doing to clean up any temporary
>      > messes I am creating?
> 
>     What is the purpose of the temp view over the the regular view process?
> 
>     How do they differ in data?
> 
>     Is all the above happening in one session?
> 
>     Have you run EXPLAIN ANALYZE on the select from the regular view?
> 
>      >
>      > Thanks,
>      > Celia McInnis
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian

The only behaviour changed for the debugging was to make the view non-temporary, so that I could verify in psql that the content of the view was what I wanted it to be. Debugging CGI software can be quite difficult, so it's always good to have debugging hooks as a part of the software - I know that I always have a DEBUG flag which, if on, prints out all kinds of stuff into a debug file, and I just had my software set a different name for DEBUG mode's non-temporary view than I was using for the temporary view, as advised by Christophe Pettus.

This indicates you are working in different sessions and therefore creating a regular view to see the same data in all sessions.

Previously this regular view was named the same as the temporary view you create in the production database.

Now you name that regular view a unique name not to conflict with the temporary view name(s).

No, unfortunately I didn't do an explain on the slow query - and it's too late now since the views are removed. However, I never had a delay when waiting for the view to be created in my web software, so, I'll just proceed being more careful and hope that the delay seen was due to some big mess I created.

In your original post you say the delay occurred on a SELECT not a CREATE VIEW after:

"DROP VIEW tempview;
DROP VIEW

postgresql did that successfully, but when I then did

select * from tempview:

"

Where the select would have been on the regular view named tempview.

Thanks,

Celia McInnis

On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 3/20/24 09:51, Celia McInnis wrote:
> The view is being used in some web query software that multiple people
> will be accessing and the contents of the view depend on what the person
> is querying, so I think that temporary views or tables are a good idea.
> I change to non-temporary views or tables (in a test version of the
> software which is not web-crawl-able) when I'm trying to debug things,
> and I guess I have to be careful to clean those up when I switch back to
> the temporary tables/views.

Why change behavior for the tests? Seems that sort of negates the value
of the testing.

Have you run EXPLAIN ANALYZE on the problem query?


>
>
>
> On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 3/20/24 08:39, Celia McInnis wrote:
>      > Ok, thanks - so I guess that means that if there is both a
>     temporary and
>      > a non temporary view called "tempvie",
>      >
>      > DROP VIEW tempview;
>      >
>      > will remove the 1st tempview found, which with my path is the
>     temporary
>      > one. Is there some reason why it then took 7 minutes to select
>     from the
>      > non-temporary view tempview after I dropped the temporary view
>     tempview?
>      >
>      > I have sometimes had some very long query times when running query
>      > software, and maybe they are resulting from my switching between
>      > temporary and non-temporary views of the same name while
>     debugging. If
>      > so, is there something I should be doing to clean up any temporary
>      > messes I am creating?
>
>     What is the purpose of the temp view over the the regular view process?
>
>     How do they differ in data?
>
>     Is all the above happening in one session?
>
>     Have you run EXPLAIN ANALYZE on the select from the regular view?
>
>      >
>      > Thanks,
>      > Celia McInnis
>      >
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/20/24 13:00, Celia McInnis wrote:
> 
> 
> On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     __
> 
> 
>     On 3/20/24 10:54 AM, Celia McInnis wrote:
> 
>     Comments below more to sort out the process in my head then anything
>     else.
>>     Hi Adrian
>>
>>     The only behaviour changed for the debugging was to make the view
>>     non-temporary, so that I could verify in psql that the content of
>>     the view was what I wanted it to be. Debugging CGI software can be
>>     quite difficult, so it's always good to have debugging hooks as a
>>     part of the software - I know that I always have a DEBUG flag
>>     which, if on, prints out all kinds of stuff into a debug file, and
>>     I just had my software set a different name for DEBUG mode's
>>     non-temporary view than I was using for the temporary view, as
>>     advised by Christophe Pettus.
> 
>     This indicates you are working in different sessions and therefore
>     creating a regular view to see the same data in all sessions.
> 
>     Previously this regular view was named the same as the temporary
>     view you create in the production database.
> 
>     Now you name that regular view a unique name not to conflict with
>     the temporary view name(s).
> 
>>     No, unfortunately I didn't do an explain on the slow query - and
>>     it's too late now since the views are removed. However, I never
>>     had a delay when waiting for the view to be created in my web
>>     software, so, I'll just proceed being more careful and hope that
>>     the delay seen was due to some big mess I created.
> 
>     In your original post you say the delay occurred on a SELECT not a
>     CREATE VIEW after:
> 
> Correct. But the initial CREATE VIEW was done  as a SELECT from the 
> database, so if the create view was quick, I thought that the select 
> from the view would be equally quick. Is this a faulty assumption?


https://www.postgresql.org/docs/current/sql-createview.html

"CREATE VIEW defines a view of a query. The view is not physically 
materialized. Instead, the query is run every time the view is 
referenced in a query."

In addition the 'canned' query is running against tables(excepting the 
VALUES case) which in turn maybe getting queries(SELECT, INSERT, UPDATE, 
DELETE) from other sources. This means that each SELECT from a view 
could be seeing an entirely different state.

The above is in reference to a regular(temporary or not) view not a:

https://www.postgresql.org/docs/current/sql-creatematerializedview.html

"CREATE MATERIALIZED VIEW defines a materialized view of a query. The 
query is executed and used to populate the view at the time the command 
is issued (unless WITH NO DATA is used) and may be refreshed later using 
REFRESH MATERIALIZED VIEW."




-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 20 Mar 2024 at 21:01, Celia McInnis <celia.mcinnis@gmail.com> wrote:
> Correct. But the initial CREATE VIEW was done  as a SELECT from the database, so if the create view was quick, I
thoughtthat the select from the view would be equally quick. Is this a faulty assumption?
 

It is. Create view does not run the query, select from the view does,

Create view is like compiling a function, it just checks, select from
the view is like running the function.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
s=> \timing
Timing is on.
s=> create temporary view tstview as select pg_sleep(1)::text;
CREATE VIEW
Time: 153.129 ms
s=> select * from tstview;
 pg_sleep
----------

(1 row)

Time: 1009.195 ms (00:01.009)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Francisco Olarte.