Re: Dropping a temporary view?

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 <ewie(at)ewie(dot)name> 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
>