Обсуждение: referring to a different database from a trigger
Hello, Is there a syntax for querying another database from a trigger in the current database? Thanks for any info, P
On Nov 21, 2008, at 5:26 PM, pw wrote: > Hello, > > Is there a syntax for querying another database > from a trigger in the current database? > > Thanks for any info, > > P Generally we would say DBLink or DBI-Link Ries
On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <pg@rvt.dds.nl> wrote: > > On Nov 21, 2008, at 5:26 PM, pw wrote: > >> Hello, >> >> Is there a syntax for querying another database >> from a trigger in the current database? >> >> Thanks for any info, > > Generally we would say DBLink or DBI-Link Note that there are no built in transactional symantics in such situations. You got to roll your own. And they may not work.
Scott Marlowe wrote: > On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <pg@rvt.dds.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. P
On Fri, Nov 21, 2008 at 4:43 PM, pw <p.willis@telus.net> wrote: > Scott Marlowe wrote: >> >> On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <pg@rvt.dds.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?
Scott Marlowe wrote: > On Fri, Nov 21, 2008 at 4:43 PM, pw <p.willis@telus.net> wrote: >> Scott Marlowe wrote: >>> On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <pg@rvt.dds.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