On 3/20/24 13:00, Celia McInnis wrote:
On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx
