Thread: How to use a trigger to write rows to a remote server

Hi All,

I am trying to find out how to use a trigger function on a table to copy any inserted row to a remote PG server.

ie:

Row X is inserted into TableX in DB1 on server1....TableX trigger function fires and contacts DB2 on server2 and inserts the row into TableY on server2.

I've looked around and can't see to find this. Essentially I need to know how to write to a remote DB server from within a trigger function.

This is not replication, I'm not interested in a full blown trigger based replication solution.

Any Help is greatly appreciated!

Thanks

Mike

Michael Dengler wrote:
> I am trying to find out how to use a trigger function on a table to copy
> any
> inserted row to a remote PG server.

Have a look at contrib/dblink.

You'll have to think what you want to happen in error scenarios. For
example, if the connection is down, or it brakes just after inserting
the row to the other db, but before committing. Or if the insert on the
other server succeeds, but the local transaction aborts.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

On Wed, 2007-07-18 at 15:36, Michael Dengler wrote:
> Row X is inserted into TableX in DB1 on server1....TableX trigger
> function fires and contacts DB2 on server2 and inserts the row into
> TableY on server2.

This kind of problem is usually solved more robustly by inserting the
"change" into a local table and let the remote server (or some external
program) poll that periodically, and make the necessary changes to the
remote server. This method does not have the problems Heikki mentions in
his reply with disconnections and transaction rollbacks, as the external
program/remote server will only see committed transactions and it can
apply the accumulated changes after connection is recovered in case of
failure, without blocking the activity on the "master".

This is also covered in a few past posts on the postgres lists (I guess
you should look in the "general" list for that), in particular you could
be interested in the possibility of notifications if you want your
poller to be notified immediately when a change occurs.

Cheers,
Csaba.

"Michael Dengler" <michael.dengler@gmail.com> writes:
> I am trying to find out how to use a trigger function on a table to copy any
> inserted row to a remote PG server.
> ...
> This is not replication, I'm not interested in a full blown trigger based
> replication solution.

To be blunt, you're nuts.  You *are* building a trigger based
replication system, and the fact that you think you can cut corners
just shows how little you know about the problems involved.

Use Slony, or some other solution that someone else has already gotten
the bugs out of.

            regards, tom lane

On Wed, 2007-07-18 at 16:02 +0200, Csaba Nagy wrote:
> On Wed, 2007-07-18 at 15:36, Michael Dengler wrote:
> > Row X is inserted into TableX in DB1 on server1....TableX trigger
> > function fires and contacts DB2 on server2 and inserts the row into
> > TableY on server2.
> This kind of problem is usually solved more robustly by inserting the
> "change" into a local table and let the remote server (or some external

If you don't want to build your own push/pull system [actually hard to
do well] then use something like xmlBlaster or some other MOM.  You get
logging, transactions, and other features thrown in.

http://www.xmlblaster.org/xmlBlaster/doc/requirements/contrib.replication.html

--
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org

Hmm..I was hoping to avoid personal insults....

Anyway, Nuts or not...what I am attempting is to simply have row from one table inserted into another servers DB I don't see it as replication because:

a) The destination table will have a trigger that modifies the arriving data to fit its table scheme.
b) It is not critical that the data be synchronous (ie a lost row on the destination DB is not a big deal)
c) I see as more of a provision of data to the destination DB NOT A REPLICATION OF DATA.

Essentially the remote server just wants to know when some record arrives at the source server and wants to know some of the info contained in the new record.

And yes it may be that I know little about the myriad of problems involved with replication...but I do know how to carry on a civil, adult conversation....maybe we can have a knowledge exchange.

Cheers

Mike

On Jul 18, 2007, at 11:30 AM, Michael Dengler wrote:

> Hmm..I was hoping to avoid personal insults....
>
> Anyway, Nuts or not...what I am attempting is to simply have row
> from one table inserted into another servers DB I don't see it as
> replication because:
>
> a) The destination table will have a trigger that modifies the
> arriving data to fit its table scheme.
> b) It is not critical that the data be synchronous (ie a lost row
> on the destination DB is not a big deal)
> c) I see as more of a provision of data to the destination DB NOT A
> REPLICATION OF DATA.
>
> Essentially the remote server just wants to know when some record
> arrives at the source server and wants to know some of the info
> contained in the new record.
>
> And yes it may be that I know little about the myriad of problems
> involved with replication...but I do know how to carry on a civil,
> adult conversation....maybe we can have a knowledge exchange.
>
> Cheers
>
> Mike

Mike,

If all you need is for your trigger to make a simple query on another
db then you can use dblink or an untrusted version of one of the
available procedural languages such as plperlu or plpythonu.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On 7/18/07, Michael Dengler <michael.dengler@gmail.com> wrote:
> Hmm..I was hoping to avoid personal insults....
>
> Anyway, Nuts or not...what I am attempting is to simply have row from one
> table inserted into another servers DB I don't see it as replication
> because:

I think you took Tom's comments the wrong way.  He is suggesting you
are nuts to attempt trigger based data transfer to remote server when
there are two clearly better options, slony and dblink.  You took as a
personal insult which was just some frank (and frankly good) advice...

Slony is in fact the _solution_ to the problem of transferring data
between servers with triggers.  If your tables are well designed and
you are reasonably proficient with stored procedures, and you
requirements of transfer are very specific and not extremely time
sensitive, a poll based system over dblink is also a good solution.

> a) The destination table will have a trigger that modifies the arriving data
> to fit its table scheme.
> b) It is not critical that the data be synchronous (ie a lost row on the
> destination DB is not a big deal)
> c) I see as more of a provision of data to the destination DB NOT A
> REPLICATION OF DATA.

based on this you may want to rig dblink/poll.  3rd option is pitr
shipping to warm standby, depending on your requirements.

> Essentially the remote server just wants to know when some record arrives at
> the source server and wants to know some of the info contained in the new
> record.
>
> And yes it may be that I know little about the myriad of problems involved
> with replication...but I do know how to carry on a civil, adult
> conversation....maybe we can have a knowledge exchange.

now that's a bit dramatic :-)

merlin