Re: Dropping a temporary view?

[Francisco Olarte]

On Wed, 20 Mar 2024 at 21:01, Celia McInnis  wrote:
> 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?

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.

Re: Dropping a temporary view?

[Adrian Klaver]

On 3/20/24 13:00, Celia McInnis wrote:



On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver > 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.kla...@aklaver.com

Re: Dropping a temporary view?

[Celia McInnis]

On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver 
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?

> "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 
> 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
>> > mailto:adrian.kla...@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.kla...@aklaver.com 
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>> --
> Adrian klaveradrian.kla...@aklaver.com
>
>

Re: Dropping a temporary view?

[Adrian Klaver]

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:


"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 
 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
> mailto:adrian.kla...@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.kla...@aklaver.com 
>

-- 
Adrian Klaver

adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Dropping a temporary view?

[David G. Johnston]

Top-posting is frowned upon on these lists.  Please try to reply online or
at worse after the comments you are referencing.

On Wed, Mar 20, 2024, 10:54 Celia McInnis  wrote:

>
>
> 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.
>

If it isn't reproducible it is hard to diagnose. Given the time difference
if it isn't fundamentally a different view then I'd be inclined to suspect
locking issues as a probable factor.

David J.

>

Re: Dropping a temporary view?

[Celia McInnis]

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.

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.

Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver 
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
> > mailto:adrian.kla...@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.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Dropping a temporary view?

[Celia McInnis]

Good, that's what I'd hope. I'm still not sure why it took more than 7
minutes in psql to select the old non-temporary view contents after
dropping the newer temporary view of the same name. There were no delays in
producing the original non-temporary view. If I can reproduce the problem
in psql, I'll re-ask. Meanwhile I'll also change my software to use
different view names when using non-temporary iviews for debugging.

Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 12:33 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, March 20, 2024, Celia McInnis 
> wrote:
>>
>>
>> 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?
>>
>>>
>>>
> The fact that you had and then dropped the temporary view has no
> relationship to how some other unrelated view performs.  That the views
> have the same name is just bad naming/design for this very reason; it harms
> understanding.
>
> David J.
>
>

Re: Dropping a temporary view?

[Adrian Klaver]

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 
mailto:adrian.kla...@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.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Dropping a temporary view?

[Rob Sargent]

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.

Re: Dropping a temporary view?

[Christophe Pettus]

> On Mar 20, 2024, at 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.

There's nothing wrong with temporary views or tables, and the use-case you 
describe is a reasonable one.  The issue comes up 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 
moved to a different position in the search path, you could have some 
surprising behavior.

Re: Dropping a temporary view?

[Celia McInnis]

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.



On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver 
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.kla...@aklaver.com
>
>

Re: Dropping a temporary view?

[David G. Johnston]

On Wednesday, March 20, 2024, Celia McInnis  wrote:
>
>
> 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?
>
>>
>>
The fact that you had and then dropped the temporary view has no
relationship to how some other unrelated view performs.  That the views
have the same name is just bad naming/design for this very reason; it harms
understanding.

David J.

Re: Dropping a temporary view?

[Adrian Klaver]

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.kla...@aklaver.com

Re: Dropping a temporary view?

[Celia McInnis]

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?

Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 11:12 AM Erik Wienhold  wrote:

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

Re: Dropping a temporary view?

[Erik Wienhold]

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

Dropping a temporary view?

[Celia McInnis]

Hi

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;
 ^

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.

Thanks,
Celia McInnis