On 19 July 2017 at 16:34, Tatsuo Ishii <is...@sraoss.co.jp> wrote: > Now that we are going to have logical replication in PostgreSQL 10, I > have started thinking how Pgpool-II can deal with it. For example, the > logical replication does not replicate DDLs. Isn't it convenient for > users to do it automatically in Pgpool-II? Or even doing it for > TRUNCATE? > > Or are they against the design philosophy of the logical replication? >
(Disclaimer - Petr Jelinek and Peter Eisentraut were the main ones working on in in-core logical rep, and I haven't had time to play with it much). TL;DR: a pooler can only solve a limited subset of the problem, fairly unreliably, and this is really something that needs further work in core. Not replicating TRUNCATE and schema changes is more an implementation limitation than anything else. It's desirable to get to the point where logical replication can transparently replicate DDL. There are some hurdles for doing so, but it'll be possible to get something in place that's good enough when time/release progress permits. Similarly, with TRUNCATE, AFAIK support just didn't make the cut for pg10. A pooler could well help in the mean time, but you have to consider ordering with care. For example, given "U" upstream, "D" downstream: U: CREATE TABLE x (a integer, b integer); D: CREATE TABLE x (a); -- user script syncs U: INSERT INTO x (a,b) VALUES (1,0); D: [applies INSERT 1,0] U: INSERT INTO x (a,b) VALUES (2,0); U: ALTER TABLE x DROP COLUMN b; D: ALTER TABLE x DROP COLUMN b; -- user script syncs U: INSERT INTO x (a) VALUES (3); D: [ERROR on INSERT of 2,0: no column 'b'] Because the DDL here is transported out of band vs the row data, you can easily create situations where the schema change is applied before the last committed-but-not-yet-replicated data from the upstream that was based on the old schema. To achieve correct ordering, the simplest approach is to record DDL in a table when you perform it on the upstream, and replay it when you see rows in that table appear on the downstream. You know it's safe to replay it now. This is the essence of what BDR and pglogical do with their DDL replication, but they handle DDL in the middle of transactions that also make row data changes by intercepting writes to the queue table and performing the DDL at the exact point in the transaction where it happened on the upstream. I don't think that's possible with the in-core logical replication yet, and certainly not something a pooler can do. To do it externally, you have to take note of when a schema change happened on the upstream and apply it on the downstream at or after the point where the downstream has replayed and confirmed up to the upstream lsn where the schema change happened. Then apply the schema change. A pooler trying to help here must also be very aware of the impact of multi-statements. If you send a single simple query message with a mixture of schema change commands and normal DML, you probably don't want to repeat the DML on downstream nodes or you'll get duplicate rows etc. But ... by unless it embeds transaction control commands, a simple query message executes in a single implicit transaction, so if you extract just the DDL you'll again have ordering issues of upstream vs downstream. There are even a variety of difficulties to overcome with doing it in core: event triggers don't capture ddl command text and have no facility to turn the internal command representation back into SQL command text, nor do we have any way to turn the internal representation back into a parsenode tree for execution on a downstream's standard_ProcessUtility. However, we can capture raw command text with ProcessUtility_hook now that we have byte-ranges for the query-parts of a multi-part query (yay!), and that works well enough if you also capture the active search_path and apply with the same search_path. It can match the wrong object if extra objects with the same name are present earlier on the search_path on the downstream than on the upstream, so it's not ideal, but that's a weird corner case. If we had a hook in the logical apply worker's insert or wal-message routines it'd be possible to write an extension to do this for pg10, but AFAICS we don't. So schema changes in logical replication currently require more care than in physical replication. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
