Quantcast

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

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Michael Dengler-3
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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Heikki Linnakangas-2
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Csaba Nagy
In reply to this post by Michael Dengler-3
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.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Tom Lane-2
In reply to this post by Michael Dengler-3
"Michael Dengler" <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Adam Tauno Williams-2
In reply to this post by Csaba Nagy
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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Michael Dengler-3
In reply to this post by Tom Lane-2
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 7/18/07, Tom Lane <[hidden email]> wrote:
"Michael Dengler" <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Erik Jones

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®
[hidden email]
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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Merlin Moncure-2
In reply to this post by Michael Dengler-3
On 7/18/07, Michael Dengler <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Loading...