Re: Dropping a temporary view?

On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> >
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>> --
> Adrian Klaveradrian(dot)klaver(at)aklaver(dot)com
>
>