From: Michael Fuhr <mike@fuhr.org>
To: Haris Saybasili <haris.saybasili@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger between to different database
Date: Sun, 14 Aug 2005 21:12:50 -0600
Message-ID: <20050815031250.GA93409@winnie.fuhr.org> (raw)
In-Reply-To: <685abb830508081141386a3878@mail.gmail.com>
References: <685abb830508081141386a3878@mail.gmail.com>
On Mon, Aug 08, 2005 at 09:41:22PM +0300, Haris Saybasili wrote:
>
> I have two databasese: database1 and database2. I want to set a
> trigger on a table of database1 which will insert some values to a
> table of database2.
You could use contrib/dblink but you wouldn't have transaction
semantics. For example, suppose you insert a record into database1
and the trigger inserts a record into database2. If you then roll
back the transaction in database1, there's no simple way to roll
back the changes in database2.
Another possibility would be to use LISTEN/NOTIFY, which does work
with transactions (NOTIFY messages aren't sent until and unless the
transaction commits). But this has problems too: you have to find
out what record(s) to insert, and what should happen if the insert
in database2 fails? Should the changes in database1 be rolled back
somehow, even though they've already been committed?
Do you really need separate databases? The problems described above
would go away if you used multiple schemas in the same database
instead of multiple databases.
--
Michael Fuhr
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, haris.saybasili@gmail.com
Subject: Re: trigger between to different database
In-Reply-To: <20050815031250.GA93409@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox