On 22 July 2016 at 09:54, Dmitriy Sarafannikov <d.sarafanni...@bk.ru> wrote: > >><programlisting> >>CREATE PUBLICATION mypub; >>ALTER PUBLICATION mypub ADD TABLE users, departments; > >>CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar >>user=repuser</quote> PUBLICATION mypub; > >> The above will start the replication process which synchronizes the >> initial table contents of <literal>users</literal> and >> <literal>departments</literal> tables and then starts replicating >> incremental changes to those tables. > > Hi, hackers. > > it is very good to have logical replication in core. Also i have some > proposal.
Thanks for your input. > What if we would have ability to execute custom trigger functions on events > on particular table? Also it would be useful if would have ability to ignore > some tables in publication or replicatie with some WHERE condition. Both of those requirements are eventual goals for this. The initial commits of Logical Replication for 10.0 won't include those features, keeping the scope tight so we make sure we get this into 10.0, but we want to design it with those things in mind and we may be lucky enough to get it in the first release. > For example, we want replicate table "users" as is (maybe with some WHERE > conditions), but on events on table "departments" we want execute trigger > function departments_event_handler(). > ALTER SUBSCRIPTION mysub ADD TABLE users REPLICATE TO LOCAL TABLE my_users > INSERT WHERE new.id_user > 1000 UPDATE WHERE old.id_user < 1000; -- we don't > want replicate deletes, for example. > ALTER SUBSCRIPTION mysub ADD TABLE departments ON INSERT WHEN (new.id_user > > 1000) EXECUTE PROCEDURE departments_event_handler(); -- just like trigger Filtering data at both the sender and receiver has been requested, so I guess it makes sense to have a WHERE clause on both the Publication and the Subscription, but I will think more on that. Transforming data at the receiver/Apply side we had envisaged would be done using standard triggers. So we had envisaged you would add the trigger onto the table using the existing trigger syntax, so that when an INSERT was applied the trigger would execute, using the standard mechanisms. So I don't think we need to add trigger style syntax onto the Subscription, but it is an option to consider, I guess. (Note to self: make sure we get the dependencies right here so when we drop objects they are automatically removed from Subscriptions, whilst allowing Subscriptions to be dropped without causing tables to be dropped (and Publications also)). > And we don't want handle events on third table which was added to publication. Not sure what you mean for that part. Could you explain a little more? (I'm on leave for some days, so I don't respond quickly I will respond eventually) -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers