Re: Follow-up on replication hooks for PostgreSQL

On 7/10/08, Robert Hodges <robert(dot)hodges(at)continuent(dot)com> wrote:
> This is a quick update on a promise I made early in June to suggest
> requirements as well as ways to add replication hooks that would support
> logical replication, as opposed to the physical replication work currently
> underway based on NTT's code.
>
> Well, June was a pretty busy month, so it has taken a while to get back to
> this. However, we are now beginning to examine options for PostgreSQL
> logical replication. To make a long story short we are willing to commit
> resources to this problem or fund other people to do it for us. If you are
> interested please contact me directly. Meanwhile, we are quite serious
> about this problem and intend to work on helpful additions to PostgreSQL in
> this area. I will post more as we make progress.

Well, I'm not exactly sure what you are planning. It's OK to do
draft design privately, but before actually starting coding, the
design should be discussed in -hackers.

And I'm not exactly sure what you mean in "logical replication"?
Way to log DDL statements? Do you want to log DML also?

FWIW, here's very draft design for functionality that could be used
to make current Slony-I/Londiste-like solutions to replicate DDL also.

1. CREATE DDL TRIGGER statement that allows to call function
for all DDL statements.

Only filtering that makes sense here is filtering by area:
tables/functions/views/etc.

It must be possible to do AFTER trigger. Whether BEFORE
trigger for DDL make sense or not, I'm not sure.

2. When function is called, following information is given:

- Object type the event was for (table/view/function)
- Array of object names.
- SQL statement as text.

The trigger function can filter further based on object names
whether it does want to log the event or not.

Trying to make the trigger run on only subset of events is complex,
and parsing the SQL to pieces for trigger to understand it better
is also complex and neither is needed. Unless there are some common
situation where such simple design fails to work, I would not make
the scheme more complex.

Also the design should be based on assumption that the target side
is exactly in sync. Eg. DROP CASCADE should be replicated as DROP CASCADE.
We should not make scheme more complex to survive cases where target
is not in sync. That way madness lies. The effect should be like
same SQL statements are applied to target by hand, no more, no less.

--
marko