Message-ID: <5900F679.8000100@postgrespro.ru>
Date: Wed, 26 Apr 2017 22:35:21 +0300
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Doug Doole <ddoole@salesforce.com>,
 Andres Freund <andres@anarazel.de>
CC: Serge Rielau <serge@rielau.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Cached plans and statement generalization
References: <f0bfcdd6-dba3-e9a8-2108-146e1f880839@postgrespro.ru>
 <CBF37923-CDA5-48EF-9E9E-4A338D7988ED@rielau.com>
 <1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru>
 <e85343e6-5ab5-447c-9995-5cce992fedf3@rielau.com>
 <58FFB391.5080101@postgrespro.ru>
 <C0064A85-7CE9-44E6-B14F-98E541B328FD@rielau.com>
 <58FFB5F0.2020900@postgrespro.ru>
 <CAP6UvaMjiumn5-Lre_w11j=ZAOB9auO1x=-meTKsWRfcsQ7Pcw@mail.gmail.com>
 <20170425214746.3mngxgbo7yndfgax@alap3.anarazel.de>
 <CAP6UvaNvFYprWdghZ4=MFUFLczzj1br9CX+vAQFz8wAGd14yug@mail.gmail.com>
 <20170425223448.amnhdludwondarph@alap3.anarazel.de>
 <CAP6UvaO9bS8+U-Upt-1Fkn7tgUyNW_BiCFbh9a4LgcHqh84kQw@mail.gmail.com>
 <CAP6UvaOHL2mzKT4nX=5XcsY8mRqV0vjzXc+4dJsYx+uMsAQ6Ow@mail.gmail.com>
In-Reply-To: 
 <CAP6UvaOHL2mzKT4nX=5XcsY8mRqV0vjzXc+4dJsYx+uMsAQ6Ow@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------090305080000060005020101"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------090305080000060005020101
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 04/26/2017 08:08 PM, Doug Doole wrote:
>
>     A naive option would be to invalidate anything that depends on table or view *.FOOBAR. You could probably make it a bit smarter by also requiring that schema A appear in the path.
>
>
> This has been rumbling around in my head. I wonder if you could solve this problem by registering dependencies on objects which don't yet exist. Consider:
>
> CREATE TABLE C.T1(...);
> CREATE TABLE C.T2(...);
> SET search_path='A,B,C,D';
> SELECT * FROM C.T1, T2;
>
> For T1, you'd register a hard dependency on C.T1 and no virtual dependencies since the table is explicitly qualified.
>
> For T2, you'd register a hard dependency on C.T2 since that is the table that was selected for the query. You'd also register virtual dependencies on A.T2 and B.T2 since if either of those tables (or views) are created you need to recompile the 
> statement. (Note that no virtual dependency is created on D.T2() since that table would never be selected by the compiler.)
>
> The catch is that virtual dependencies would have to be recorded and searched as strings, not OIDs since the objects don't exist. Virtual dependencies only have to be checked during CREATE processing though, so that might not be too bad.
>
> But this is getting off topic - I just wanted to capture the idea while it was rumbling around.

I think that it will be enough to handle modification of search path and invalidate prepared statements cache in this case.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--------------090305080000060005020101
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 04/26/2017 08:08 PM, Doug Doole
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAP6UvaOHL2mzKT4nX=5XcsY8mRqV0vjzXc+4dJsYx+uMsAQ6Ow@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_quote">
          <blockquote class="gmail_quote" style="margin:0 0 0
            .8ex;border-left:1px #ccc solid;padding-left:1ex">
            <div dir="ltr">
              <div class="gmail_quote">
                <div>A naive option would be to invalidate anything that
                  depends on table or view *.FOOBAR. You could probably
                  make it a bit smarter by also requiring that schema A
                  appear in the path.</div>
              </div>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>This has been rumbling around in my head. I wonder if you
            could solve this problem by registering dependencies on
            objects which don't yet exist. Consider:</div>
          <div><br>
          </div>
          <div>CREATE TABLE C.T1(...);</div>
          <div>CREATE TABLE C.T2(...);</div>
          <div>SET search_path='A,B,C,D';</div>
          <div>SELECT * FROM C.T1, T2;</div>
          <div><br>
          </div>
          <div>For T1, you'd register a hard dependency on C.T1 and no
            virtual dependencies since the table is explicitly
            qualified.</div>
          <div><br>
          </div>
          <div>For T2, you'd register a hard dependency on C.T2 since
            that is the table that was selected for the query. You'd
            also register virtual dependencies on A.T2 and B.T2 since if
            either of those tables (or views) are created you need to
            recompile the statement. (Note that no virtual dependency is
            created on D.T2() since that table would never be selected
            by the compiler.)</div>
          <div><br>
          </div>
          <div>The catch is that virtual dependencies would have to be
            recorded and searched as strings, not OIDs since the objects
            don't exist. Virtual dependencies only have to be checked
            during CREATE processing though, so that might not be too
            bad.</div>
          <div><br>
          </div>
          <div>But this is getting off topic - I just wanted to capture
            the idea while it was rumbling around.</div>
        </div>
      </div>
    </blockquote>
    <br>
    I think that it will be enough to handle modification of search path
    and invalidate prepared statements cache in this case.<br>
    <br>
    <pre class="moz-signature" cols="256">-- 
Konstantin Knizhnik
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company</pre>
  </body>
</html>

--------------090305080000060005020101--