Re: referring to a different database from a trigger

Scott Marlowe wrote:
> On Fri, Nov 21, 2008 at 4:43 PM, pw <p(dot)willis(at)telus(dot)net> wrote:
>> Scott Marlowe wrote:
>>> On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <pg(at)rvt(dot)dds(dot)nl> wrote:
>>>
>>> Note that there are no built in transactional symantics in such
>>> situations. You got to roll your own. And they may not work.
>>>
>>
>> Yeah, that was what I was hoping for.
>>
>> ie:(query between databases)
>>
>> SELECT db_one.table_one.column_name_one, db_two.table_oranges.column_fluff
>> FROM db_one.table_one, db_two.table_oranges WHERE
>> db_one.table_one.some_id=db_two.table_oranges.raisin_id;
>>
>> <sigh> Oh well!, I'll just do it in code I guess....
>>
>> I can see where the difficulties lay in this.
>> It would require a pipeline between the two databases
>> allowing one of them to share tables, difficult if there
>> are common oids pointing to completely different objects.
>> Oids/schemas would need to be aliased in some way to give them
>> pseudo-representation in the destination database.
>
> can you use database schemas to accomplish the same thing? i.e. put
> it all in one database and use schemas to partition them? or do you
> have other requirements that make that a no go?
>

Well, one of the purposes is database security.
One of the secondary databases is off site. That database
will retain trigger and transaction metadata from the first.
This is done to maintain a log of data quality where logins and
transactions are directly associated with a user_id and the
time and purpose of any transaction are logged.

That data allows accounting of WHO logs in, WHEN they logged in,
and for what PURPOSE they accessed the database (ie: case number).
This is done off site so that, if something happens to the database,
we can figure out the circumstances behind that. ie: why was data
entered, who entered it, when was it entered.

The database server system logs records are also entered into a remote
database. This allows user tracking and the server logs can never be
wiped out by accident, failure, or malicious user. That makes security
forensics a lot easier.

The rest is just handled by backups and replication. The systems usually
have an automated pg_dumpall backup that happens every hour.

P