pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Celia McInnis <celia.mcinnis@gmail.com>
Cc: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Dropping a temporary view?
Date: Wed, 20 Mar 2024 13:36:03 -0700
Message-ID: <8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com> (raw)
In-Reply-To: <CAGD6t7LSN5bL2gObj=CXCawOjHB2LumML-x-fPmQK-xdYVjAig@mail.gmail.com>
References: <CAGD6t7JniiFU5_tu=1Dpa1-E_HjVdWyT8T7RaU6uTZ-7WF9TiQ@mail.gmail.com>
	<p3bniwsob54rr2y436hhjkl6c44k3refzzyjeqmh365fig726a@qq6ht7hzh4j6>
	<CAGD6t7+xR3YpP3QurENxL1p1FAv8zohd_8cg=tNRX10NZGHQZA@mail.gmail.com>
	<5b2ebf67-df3b-4b94-8a27-74d20d28f014@aklaver.com>
	<CAGD6t7+vGjFj=VYWDsnwFg=7j13xEnceJ11aRBvHQis5QdHrpQ@mail.gmail.com>
	<2ce8c3d5-2a54-4c8d-b0bd-bb4f4967b515@aklaver.com>
	<CAGD6t7JD8DNyaCtjtfcyupfWqjByN9O3qshsrS3-0t5RPTn_8g@mail.gmail.com>
	<6d2cb339-7558-4392-9af9-3d6e3de5a595@aklaver.com>
	<CAGD6t7LSN5bL2gObj=CXCawOjHB2LumML-x-fPmQK-xdYVjAig@mail.gmail.com>

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






view thread (16+ messages)  latest in thread

Message-ID: <8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com>
Permalink:  ../8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com/
Also on:    postgresql.org/message-id/8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, celia.mcinnis@gmail.com, ewie@ewie.name
  Subject: Re: Dropping a temporary view?
  In-Reply-To: <8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com>
  References: <CAGD6t7JniiFU5_tu=1Dpa1-E_HjVdWyT8T7RaU6uTZ-7WF9TiQ@mail.gmail.com> <p3bniwsob54rr2y436hhjkl6c44k3refzzyjeqmh365fig726a@qq6ht7hzh4j6> <CAGD6t7+xR3YpP3QurENxL1p1FAv8zohd_8cg=tNRX10NZGHQZA@mail.gmail.com> <5b2ebf67-df3b-4b94-8a27-74d20d28f014@aklaver.com> <CAGD6t7+vGjFj=VYWDsnwFg=7j13xEnceJ11aRBvHQis5QdHrpQ@mail.gmail.com> <2ce8c3d5-2a54-4c8d-b0bd-bb4f4967b515@aklaver.com> <CAGD6t7JD8DNyaCtjtfcyupfWqjByN9O3qshsrS3-0t5RPTn_8g@mail.gmail.com> <6d2cb339-7558-4392-9af9-3d6e3de5a595@aklaver.com> <CAGD6t7LSN5bL2gObj=CXCawOjHB2LumML-x-fPmQK-xdYVjAig@mail.gmail.com> <8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox