MIME-Version: 1.0
In-Reply-To: 
 <CAMAYy4L=-n=VzyMi6s6tg0PSJcR6mXH2D2K1vk526uLEZkC31A@mail.gmail.com>
References: 
 <CAMAYy4LD9TDO6ohfZwZ7OWOfu3oUZKQxi1Y=qt=36C65DL4jOw@mail.gmail.com>
 <CAG1YDPeH+FBFv9_w1ktPn3JyJNA1F8L_6qaMcRZZQsbBjXtxww@mail.gmail.com>
 <CAMAYy4L=-n=VzyMi6s6tg0PSJcR6mXH2D2K1vk526uLEZkC31A@mail.gmail.com>
From: Rick Otten <rottenwindfish@gmail.com>
Date: Thu, 6 Jul 2017 12:27:27 -0400
Message-ID: 
 <CAMAYy4L9msh+hzPwYPVypVrxDPa4U2A0AV_HmwBzO7rY_ThOhA@mail.gmail.com>
Subject: Re: partitioning materialized views
To: Shaun Thomas <shaun.thomas@2ndquadrant.com>
Cc: "pgsql-performa." <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a114711b0b04d730553a897cf"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a114711b0b04d730553a897cf
Content-Type: text/plain; charset="UTF-8"

>
>
> If you _can't_ do
>> that due to cloud restrictions, you'd actually be better off doing an
>> atomic swap.
>>
>> CREATE MATERIALIZED VIEW y AS ...;
>>
>> BEGIN;
>> ALTER MATERIALIZED VIEW x RENAME TO x_old;
>> ALTER MATERIALIZED VIEW y RENAME TO x;
>> DROP MATERIALIZED VIEW x_old;
>> COMMIT;
>>
>> This is an interesting idea.  Thanks!  I'll ponder that one.
>
>
I don't think the downstream dependencies will let that work without
rebuilding them as well.   The drop fails (without a cascade), and the
other views and matviews that are built off of this all simply point to
x_old.

--001a114711b0b04d730553a897cf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote"><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #c=
cc solid;padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_extra"><div=
 class=3D"gmail_quote"><br><span class=3D""><blockquote class=3D"gmail_quot=
e" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">=
If you _can&#39;t_ do<br>
that due to cloud restrictions, you&#39;d actually be better off doing an<b=
r>
atomic swap.<br>
<br>
CREATE MATERIALIZED VIEW y AS ...;<br>
<br>
BEGIN;<br>
ALTER MATERIALIZED VIEW x RENAME TO x_old;<br>
ALTER MATERIALIZED VIEW y RENAME TO x;<br>
DROP MATERIALIZED VIEW x_old;<br>
COMMIT;<br>
<br></blockquote></span><div>This is an interesting idea.=C2=A0 Thanks!=C2=
=A0 I&#39;ll ponder that one.</div><span class=3D""><div><br></div></span><=
/div></div></div></blockquote></div><br></div><div class=3D"gmail_extra">I =
don&#39;t think the downstream dependencies will let that work without rebu=
ilding them as well. =C2=A0 The drop fails (without a cascade), and the oth=
er views and matviews that are built off of this all simply point to x_old.=
</div><div class=3D"gmail_extra"><br></div></div>

--001a114711b0b04d730553a897cf--