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
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)---------------------------
Show quoted text
TIP 4: Don't 'kill -9' the postmaster
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:
Show quoted text
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 TaylorThere 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 triggerHi 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