Thread: update tables in remote db using trigger

Hi all,

I would like to do something like the following in a trigger:

update tablename on otherdbhost set field1 = new.field1 where keyfield =
new.keyfield;

Is this a pipe dream using Postgres or is there some way to perform such
a feat?

Thanks.

Marc Wrubleski

 Hi Rod,

This sounds nifty, but I'm wondering about the speed of such an operation.
Our design calls for a system that can do this one operation (over and
above all the other DB queries) 28 times per second.

What sort of performance are you getting out of this setup. Could it
handle 28 calls per second without bringing the system to it's knees?

Marc

Rod Taylor wrote:

> Simplest way I know of is to write a C function which fires off a
> shell script which in turn executes psql with appropriate parameters.
> The C function can pass it individual variables, or the entire SQL
> string.
>
> The reason for the shell script is that way the database, port, etc.
> are easy to change and don't require a re-compile / reload.
>
> One thing you definately cannot do is a C function which creates a new
> database connection on it's own.  Doesn't like that at all!
>
> I've implemented a form of 'su' for postgres doing that -- makes a
> second connection back into the database.  A middle man shell script
> ensures that only a few specific requests can be made (one is actually
> creating a system user with very specific parameters).
> --
> Rod Taylor
>
> There are always four sides to every story: your side, their side, the
> truth, and what really happened.
> ----- Original Message -----
> From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
> To: <pgsql-general@postgresql.org>
> Sent: Friday, April 06, 2001 7:22 PM
> Subject: [GENERAL] update tables in remote db using trigger
>
> > Hi all,
> >
> > I would like to do something like the following in a trigger:
> >
> > update tablename on otherdbhost set field1 = new.field1 where
> keyfield =
> > new.keyfield;
> >
> > Is this a pipe dream using Postgres or is there some way to perform
> such
> > a feat?
> >
> > Thanks.
> >
> > Marc Wrubleski
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >

Simplest way I know of is to write a C function which fires off a
shell script which in turn executes psql with appropriate parameters.
The C function can pass it individual variables, or the entire SQL
string.

The reason for the shell script is that way the database, port, etc.
are easy to change and don't require a re-compile / reload.

One thing you definately cannot do is a C function which creates a new
database connection on it's own.  Doesn't like that at all!

I've implemented a form of 'su' for postgres doing that -- makes a
second connection back into the database.  A middle man shell script
ensures that only a few specific requests can be made (one is actually
creating a system user with very specific parameters).
--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, April 06, 2001 7:22 PM
Subject: [GENERAL] update tables in remote db using trigger


> Hi all,
>
> I would like to do something like the following in a trigger:
>
> update tablename on otherdbhost set field1 = new.field1 where
keyfield =
> new.keyfield;
>
> Is this a pipe dream using Postgres or is there some way to perform
such
> a feat?
>
> Thanks.
>
> Marc Wrubleski
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Marc Wrubleski wrote:
>  Hi Rod,
>
> This sounds nifty, but I'm wondering about the speed of such an operation.
> Our design calls for a system that can do this one operation (over and
> above all the other DB queries) 28 times per second.
>
> What sort of performance are you getting out of this setup. Could it
> handle 28 calls per second without bringing the system to it's knees?

    PL/TclU  can  do  it  directly. It's the unsafe big sister of
    PL/Tcl and you can load libpgtcl to pg_connect and pg_exec to
    remote databases.

    Remember that those remote updates will not be rolled back in
    the case of a later rollback in your local database. There is
    no way of 2-phase commit here.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com