Date: Sun, 14 Aug 2005 21:12:50 -0600
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
Message-ID: <20050815031250.GA93409@winnie.fuhr.org>
References: <685abb830508081141386a3878@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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