MIME-Version: 1.0
In-Reply-To: 
 <CAF4Au4zmspWiqq=nCFF5Shy-3foaDiwNt+Xk2s7TLa-83D=9Hg@mail.gmail.com>
References: <5685BB86.5010901@2ndquadrant.com>
	<56A84422.2030309@commandprompt.com>
	<20160216143820.GC31273@momjian.us>
	<CAF4Au4zmspWiqq=nCFF5Shy-3foaDiwNt+Xk2s7TLa-83D=9Hg@mail.gmail.com>
Date: Wed, 17 Feb 2016 09:33:56 +0800
Message-ID: 
 <CAMsr+YHMDm0YjjZ63Z65mkHjv9hoRowO2PCozZXKT0cCQtVtZw@mail.gmail.com>
Subject: Re: pglogical - logical replication contrib module
From: Craig Ringer <craig@2ndquadrant.com>
To: obartunov@gmail.com
Cc: Bruce Momjian <bruce@momjian.us>,
 "Joshua D. Drake" <jd@commandprompt.com>,
	Petr Jelinek <petr@2ndquadrant.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary=001a1140225c5d8699052bed3e83
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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

On 17 February 2016 at 00:54, Oleg Bartunov <obartunov@gmail.com> wrote:


>
> DDL support is what it's missed for now.
>
>
TBH, based on experience with DDL replication and deparse in BDR, it's
going to be missing for a while yet too, or at least not comprehensively
present without caveats or exceptions.


Some DDL operations don't translate well to a series of replicatable
actions. The case I hit the most is

   ALTER TABLE mytable ADD COLUMN somecolumn sometype NOT NULL DEFAULT
some_function();

This is executed (simplified) by taking an ACCESS EXCLUSIVE lock, changing
the catalogs but not making the changes visible yet, rewriting the table,
and committing to make the rewritten table and the catalog changes visible.

That won't work well with logical replication. We currently capture DDL
with event triggers and log them to a table for later logical decoding and
replay - that's the "recognised" way. The trouble being that replaying that
statement will result in an unnecessary full table rewrite on the
downstream. Then we have to decode and send stream of changes to a table
called pg_temp_<oid_of_mytable>, truncate the copy of mytable on the
downstream that we just rewrote and apply those rows instead.

Of course all that only works sensibly if you have exactly one upstream and
the downstream copy of the table is treated as (or enforced as) read-only.

Improving this probably needs DDL deparse to be smarter. Rather than just
emitting something that can be reconstructed into the SQL text of the DDL
it needs to emit one or more steps that are semantically the same but allow
us to skip the rewrite. Along the lines of:

* ALTER TABLE mytable ADD COLUMN somecolumn sometype;
* ALTER TABLE mytable ALTER COLUMN somecolumn DEFAULT some_function();
* <wait for rewrite data for mytable>
* ALTER TABLE mytable ALTER COLUMN somecolumn NOT NULL;

Alternately the downstream would need a hook that lets it intercept and
prevent table rewrites caused by ALTER TABLE and similar. So it can instead
just do a truncate and wait for the new rows to come from the master.

Note that all this means the standby has to hold an ACCESS EXCLUSIVE lock
on the table during all of replay. That shouldn't be necessary, all we
really need is an EXCLUSIVE lock since concurrent SELECTs are fine. No idea
how to do that.


Deparse is also just horribly complicated to get right. There are so many
clauses and subclauses and variants of statements. Each of which must be
perfect.


Not everything has a simple and obvious mapping on the downstream side
either. TRUNCATE ... CASCADE is the obvious one. You do a cascade truncate
on the master - do you want that to replicate as a cascaded truncate on the
replica, or a truncate of only those tables that actually got truncated on
the master? If the replica has additional tables with FKs pointing at
tables replica the TRUNCATE would truncate those too if you replicate it as
CASCADE; if you don't the truncate will fail instead. Really, both are
probably wrong as far as the user is concerned, but we can't truncate just
the tables truncated on the master, ignore the FK relationships, and leave
dangling FK references either.


All this means that DDL replication is probably only going to make sense in
scenarios where there's exactly one master and the replica obeys some rules
like "don't create FKs pointing from non-replicated tables to tables
replicated from somewhere else". A concept we currently have no way to
express or enforce like we do persistent-to-UNLOGGED FKs.


Then there's global objects. Something as simple as:

CREATE ROLE fred;

CREATE TABLE blah(...) OWNER fred;

will break replication because we only see the CREATE TABLE, not the CREATE
ROLE. If we instead replayed the CREATE ROLE and there were multiple
connections between different DBs on an upstream and downstream apply would
fail on all but one. But we can't anyway since there's no way to capture
that CREATE ROLE from any DB except the one it was executed in, which might
not even be one of the ones doing replication.

I strongly suspect we'll need logical decoding to be made aware of such
global DDL and decode it from the WAL writes to the system catalogs. Which
will be fun - but at least modifications to the shared catalogs are a lot
simpler than the sort of gymnastics done by ALTER TABLE, etc.


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

--001a1140225c5d8699052bed3e83
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">On 1=
7 February 2016 at 00:54, Oleg Bartunov <span dir=3D"ltr">&lt;<a href=3D"ma=
ilto:obartunov@gmail.com" target=3D"_blank">obartunov@gmail.com</a>&gt;</sp=
an> wrote:<div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr">=
<div class=3D"gmail_extra"><div class=3D"gmail_quote"><div><div class=3D"h5=
"><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:=
1px #ccc solid;padding-left:1ex"></blockquote><div><br></div></div></div><d=
iv>DDL support is what it&#39;s missed for now. </div><div><br></div></div>=
</div></div></blockquote><div><br></div><div>TBH, based on experience with =
DDL replication and deparse in BDR, it&#39;s going to be missing for a whil=
e yet too, or at least not comprehensively present without caveats or excep=
tions.</div><div><br></div><div><br></div><div>Some DDL operations don&#39;=
t translate well to a series of replicatable actions. The case I hit the mo=
st is</div><div><br></div><div>=C2=A0 =C2=A0ALTER TABLE mytable ADD COLUMN =
somecolumn sometype NOT NULL DEFAULT some_function();</div><div><br></div><=
div>This is executed (simplified) by taking an ACCESS EXCLUSIVE lock, chang=
ing the catalogs but not making the changes visible yet, rewriting the tabl=
e, and committing to make the rewritten table and the catalog changes visib=
le.</div><div><br></div><div>That won&#39;t work well with logical replicat=
ion. We currently capture DDL with event triggers and log them to a table f=
or later logical decoding and replay - that&#39;s the &quot;recognised&quot=
; way. The trouble being that replaying that statement will result in an un=
necessary full table rewrite on the downstream. Then we have to decode and =
send stream of changes to a table called pg_temp_&lt;oid_of_mytable&gt;, tr=
uncate the copy of mytable on the downstream that we just rewrote and apply=
 those rows instead.</div><div><br></div><div>Of course all that only works=
 sensibly if you have exactly one upstream and the downstream copy of the t=
able is treated as (or enforced as) read-only.</div><div><br></div><div>Imp=
roving this probably needs DDL deparse to be smarter. Rather than just emit=
ting something that can be reconstructed into the SQL text of the DDL it ne=
eds to emit one or more steps that are semantically the same but allow us t=
o skip the rewrite. Along the lines of:</div><div><br></div><div>* ALTER TA=
BLE mytable ADD COLUMN somecolumn sometype;</div><div>* ALTER TABLE mytable=
 ALTER COLUMN somecolumn DEFAULT some_function();</div><div>* &lt;wait for =
rewrite data for mytable&gt;</div><div>* ALTER TABLE mytable ALTER COLUMN s=
omecolumn NOT NULL;</div><div><br></div><div>Alternately the downstream wou=
ld need a hook that lets it intercept and prevent table rewrites caused by =
ALTER TABLE and similar. So it can instead just do a truncate and wait for =
the new rows to come from the master.</div><div><br></div><div>Note that al=
l this means the standby has to hold an ACCESS EXCLUSIVE lock on the table =
during all of replay. That shouldn&#39;t be necessary, all we really need i=
s an EXCLUSIVE lock since concurrent SELECTs are fine. No idea how to do th=
at.</div><div><br></div><div><br></div><div><br></div><div>Deparse is also =
just horribly complicated to get right. There are so many clauses and subcl=
auses and variants of statements. Each of which must be perfect.</div><div>=
<br></div><div><br></div><div><br></div><div>Not everything has a simple an=
d obvious mapping on the downstream side either. TRUNCATE ... CASCADE is th=
e obvious one. You do a cascade truncate on the master - do you want that t=
o replicate as a cascaded truncate on the replica, or a truncate of only th=
ose tables that actually got truncated on the master? If the replica has ad=
ditional tables with FKs pointing at tables replica the TRUNCATE would trun=
cate those too if you replicate it as CASCADE; if you don&#39;t the truncat=
e will fail instead. Really, both are probably wrong as far as the user is =
concerned, but we can&#39;t truncate just the tables truncated on the maste=
r, ignore the FK relationships, and leave dangling FK references either.</d=
iv><div><br></div><div><br></div><div>All this means that DDL replication i=
s probably only going to make sense in scenarios where there&#39;s exactly =
one master and the replica obeys some rules like &quot;don&#39;t create FKs=
 pointing from non-replicated tables to tables replicated from somewhere el=
se&quot;. A concept we currently have no way to express or enforce like we =
do persistent-to-UNLOGGED FKs.</div><div><br></div><div><br></div><div><br>=
</div><div>Then there&#39;s global objects. Something as simple as:</div><d=
iv><br></div><div>CREATE ROLE fred;</div><div><br></div><div>CREATE TABLE b=
lah(...) OWNER fred;</div><div><br></div><div>will break replication becaus=
e we only see the CREATE TABLE, not the CREATE ROLE. If we instead replayed=
 the CREATE ROLE and there were multiple connections between different DBs =
on an upstream and downstream apply would fail on all but one. But we can&#=
39;t anyway since there&#39;s no way to capture that CREATE ROLE from any D=
B except the one it was executed in, which might not even be one of the one=
s doing replication.</div><div><br></div><div>I strongly suspect we&#39;ll =
need logical decoding to be made aware of such global DDL and decode it fro=
m the WAL writes to the system catalogs. Which will be fun - but at least m=
odifications to the shared catalogs are a lot simpler than the sort of gymn=
astics done by ALTER TABLE, etc.</div><div><br></div><div><br></div></div><=
div><br></div>-- <br><div class=3D"gmail_signature"><div dir=3D"ltr"><div><=
div dir=3D"ltr">=C2=A0Craig Ringer=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <a hr=
ef=3D"http://www.2ndQuadrant.com/" target=3D"_blank">http://www.2ndQuadrant=
.com/</a><br>=C2=A0PostgreSQL Development, 24x7 Support, Training &amp; Ser=
vices<br></div></div></div></div>
</div></div>

--001a1140225c5d8699052bed3e83--