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
