replicating DROP commands across servers

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: replicating DROP commands across servers
Date: 2014-03-28 14:14:10
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

As you probably know, I've studying how to implement a system to allow
replicating DDL commands to remote servers. I have a fairly good grasp
of handling CREATE commands; we have another thread discussing that
involving a JSON output for such commands. That hasn't been carried to
completion yet; I will finish that up for 9.5. My intention is to have
that code handle ALTER commands as well: I have tried some experiments
and it turns out that it's a bit more complex than I would like, but it
seems doable, now that I've figured out exactly how. (Of course, the
ALTER bits can also be used for auditing and business rule enforcing,
which are the other use-cases for this feature.)

In this thread I focus on DROP. We already added support for dropped
objects in 9.3 in the form of the pg_event_trigger_dropped_objects()
function, which returns the set of objects dropped by any command.
That's quite handy for the auditing purpose it was supposed to serve,
but it's insufficient for replication because it's pretty hard to craft
a command for object deletion on the remote node.

So my current plan involves receiving the set of objects in the remote
node, then adding them to an ObjectAddresses array, then call
performMultipleDeletions() on that. As it turns out, this works quite
well -- except when it doesn't.

The problem is this: at the local node we have the object type and
identity. We can transmit this quite fine from this node to the other
node, and then in the other node parse this back into a objname list and
pass that to get_object_address(); but only for objects that have a
simple representation, such as schemas and relations. For procedures,
constraints, triggers and other types, it's a lot of work to parse the
string into the objname/objargs lists (the identities for such objects
as "constraint_name on schema.tablename" or "schema.funcname(arg1, arg2)"
and so on. Of course, it is *possible* to parse this, but it seems the
wrong thing to do when we could just obtain the right input in the first

My proposal therefore is to add some more columns to
pg_event_trigger_dropped_objects(): more precisely, objname and objargs,
which would carry exactly what get_object_address() would require to
re-construct an ObjectAddress for the object being dropped at the remote

Thoughts, objections?

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Training & Services


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2014-03-28 14:36:54 Re: replicating DROP commands across servers
Previous Message Michael Paquier 2014-03-28 12:57:03 Re: Doing better at HINTing an appropriate column within errorMissingColumn()