On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver <adrian.kla...@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.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> > >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >> -- > Adrian klaveradrian.kla...@aklaver.com > >
