Re: Design for In-Core Logical Replication - Mailing list pgsql-hackers

On 22 July 2016 at 09:54, Dmitriy Sarafannikov <d.sarafannikov@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
usefulif 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
WHEREold.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