Horton
Menu

Thread

  1. Dropping a temporary view?

    Celia McInnis <celia.mcinnis@gmail.com> — 2024-03-20T14:58:00Z

    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

  2. Re: Dropping a temporary view?

    Erik Wienhold <ewie@ewie.name> — 2024-03-20T15:12:29Z

    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

  3. Re: Dropping a temporary view?

    Celia McInnis <celia.mcinnis@gmail.com> — 2024-03-20T15:39:41Z

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

  4. Re: Dropping a temporary view?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-20T15:46:54Z

    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

  5. Re: Dropping a temporary view?

    David G. Johnston <david.g.johnston@gmail.com> — 2024-03-20T16:33:47Z

    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.

  6. Re: Dropping a temporary view?

    Celia McInnis <celia.mcinnis@gmail.com> — 2024-03-20T16:51:32Z

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

  7. Re: Dropping a temporary view?

    Christophe Pettus <xof@thebuild.com> — 2024-03-20T16:57:11Z

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

  8. Re: Dropping a temporary view?

    Rob Sargent <robjsargent@gmail.com> — 2024-03-20T17:00:50Z

    
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.

  9. Re: Dropping a temporary view?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-20T17:01:37Z

    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

  10. Re: Dropping a temporary view?

    Celia McInnis <celia.mcinnis@gmail.com> — 2024-03-20T17:22:00Z

    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:

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

  11. Re: Dropping a temporary view?

    Celia McInnis <celia.mcinnis@gmail.com> — 2024-03-20T17:54:25Z

    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:

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

  12. Re: Dropping a temporary view?

    David G. Johnston <david.g.johnston@gmail.com> — 2024-03-20T18:14:19Z

    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.

>

  13. Re: Dropping a temporary view?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-20T18:15:22Z

    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

  14. Re: Dropping a temporary view?

    Celia McInnis <celia.mcinnis@gmail.com> — 2024-03-20T20:00:56Z

    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?

> "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 Klaveradrian.klaver@aklaver.com
>
>

  15. Re: Dropping a temporary view?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-20T20:36:03Z

    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

  16. Re: Dropping a temporary view?

    Francisco Olarte <folarte@peoplecall.com> — 2024-03-21T11:41:13Z

    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.