"Toff" <kristoff.six@telenet.be> writes:
> I'm still thinking on the problem of transferring data to a 2nd database.
> I've learnt some things but some are too complex....
>
> Now I'm thinking of executing an external program (written in delphi) with a
> (untrusted) Procedural Language on the event of a trigger.
> Thus something like
> "
> CREATE FUNCTION transfer_to_db2 RETURNS TRIGGER $$
> execute(....../transfer_to_db2.exe);
> $$ LANGUAGE ..........
> "
>
> Is this possible?
> I know i can install a language in pg, but i prefer an extrenal program (so
> i can connect it with pg and nobody else has to worry about pg).
What seems to be generally considered a better idea is to use
LISTEN/NOTIFY.
Thus, you set up a daemon program that registers a LISTEN request
based on some condition name, presumably the name of the service.
Let's say...
LISTEN "DB2_TRANSFER";
Then the "trigger" writes down whatever needs to be done in some
table(s) that the "db2 transfer process" knows about, and does a
NOTIFY any time it is needful:
NOTIFY "DB2_TRANSFER";
This tells the daemon that it has some work to do.
This approach has the merit that you don't have to worry if the
outside you're invoking is only supposed to have one instance at a
time; the daemon handles that, without having to worry about any
impact on the PostgreSQL instance.
Thus, the "trigger" process might look like:
insert into db2_transfers.object_ids values ();
insert into db2_transfers.object_ids values ();
insert into db2_transfers.object_ids values ();
notify "DB2_TRANSFER";
And if several triggers fire, and several items are to be shipped
concurrently, the daemon can draw ALL the data in at once, through one
DB connection, rather than a connection opening for each...
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/lsf.html
Rules of the Evil Overlord #165. "As an equal-opportunity employer, I
will have several hearing-impaired body-guards. That way if I wish to
speak confidentially with someone, I'll just turn my back so the
guards can't read my lips instead of sending all of them out of the
room." <http://www.eviloverlord.com/>