Re: Command Triggers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> it. Dimitri says that he wants it so that we can add support for
> CREATE TABLE, ALTER TABLE, and DROP TABLE to Slony, Bucardo, and
> Londiste. My fear is that it won't turn out to be adequate to that
> task, because there won't actually be enough information in the CREATE
> TABLE statement to do the same thing on all servers. In particular,
> you won't have the index or constraint names, and you might not have
> the schema or tablespace information either.

In my experience of managing lots of trigger based replications (more
than 100 nodes in half a dozen different projects), what I can tell from
the field is that I don't care about index and constraint names. Being
able to replicate the same CREATE TABLE statement that the provider just
executed on the subscriber is perfectly fine for my use cases.

Again, that's a caveat of the first implementation, you can't have sub
commands support without forcing them through ProcessUtility and that's
a much more invasive patch. Maybe we will need that later.

Also it's quite easy to add support for the CREATE INDEX command,
including index name support, and ALTER TABLE is already on the go. So
we can document how to organize your DDL scripts for them to just work
with the replication system. And you can even implement a command
trigger that enforces respecting the limits (RAISE EXCEPTION when the
CREATE TABLE command is embedding primary key creation rather than using
a separate command for that).

As for the schema, you can easily get the current search_path setting
from the command trigger and force it to the same value on the
subscriber before replaying the commands (hint: add current search_path
to the event you're queuing for replay).

select setting from pg_settings where name = 'search_path';

I appreciate that some use cases won't be possible to implement with the
first version of this patch, really, but I believe we have enough use
cases that are possible to implement with it that it's worth providing
the feature.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support