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

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@ewie.name> 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

On Wednesday, March 20, 2024, Celia McInnis <celia.mcinnis@gmail.com> 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.

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 <adrian.klaver@aklaver.com>
wrote:

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

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

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.johnston@gmail.com> wrote:

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 <adrian.klaver@aklaver.com>
wrote:

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 <celia.mcinnis@gmail.com> 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.

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

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