> Attached is a set of two patches as an attempt to evaluate this approach. > > The first patch provides functions to deparse DDL commands. Currently, > it is restricted to just a simple CREATE TABLE statement, the required > code is extracted from one of the patches posted in the thread [1]. > > The second patch allows replicating simple CREATE TABLE DDL > replication. To do that we used an event trigger and DDL deparsing > facilities. While creating a publication, we register a command end > trigger that deparses the DDL as a JSON blob, and WAL logs it. The > event trigger is automatically removed at the time of drop > publication. The WALSender decodes the WAL and sends it downstream > similar to other DML commands. The subscriber then converts JSON back > to the DDL command string and executes it. In the subscriber, we also > add the newly added rel to pg_subscription_rel so that the DML changes > on the new table can be replicated without having to manually run > "ALTER SUBSCRIPTION ... REFRESH PUBLICATION". Some of the code related > to WAL logging and subscriber-side work is taken from the patch posted > by Zheng in this thread but there are quite a few changes in that as > we don't need schema, role, transaction vs. non-transactional > handling. > > Note that for now, we have hacked Create Publication code such that > when the user specifies the "FOR ALL TABLES" clause, we invoke this > new functionality. So, this will work only for "FOR ALL TABLES" > publications. For example, we need to below to replicate the simple > Create Table command. > > Publisher: > Create Publication pub1 For All Tables; > > Subscriber: > Create Subscription sub1 Connection '...' Publication pub1; > > Publisher: > Create Table t1(c1 int); > > Subscriber: > \d should show t1. > > As we have hacked CreatePublication function for this POC, the > regression tests are not passing but we can easily change it so that > we invoke new functionality with the syntax proposed in this thread or > with some other syntax and we shall do that in the next patch unless > this approach is not worth pursuing. > > This POC is prepared by Ajin Cherian, Hou-San, and me. > > Thoughts? > > [1] - > https://www.postgresql.org/message-id/20150215044814.GL3391%40alvh.no-ip.org
Hi, Thanks for exploring this direction. I read the deparsing thread and your patch. Here is my thought: 1. The main concern on maintainability of the deparsing code still applies if we want to adapt it for DDL replication. 2. The search_path and role still need to be handled, in the deparsing code. And I think it's actually more overhead to qualify every object compared to just logging the search_path and enforcing it on the apply worker. 3. I'm trying to understand if deparsing helps with edge cases like "ALTER TABLE foo ADD COLUMN bar double precision DEFAULT random();". I don't think it helps out of the box. The crux of separating table rewrite and DDL still needs to be solved for such cases. Regards, Zheng
