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