Re: dblink versus schemas. What to use in this case?

Robert,

A couple of possible issues --

Running two different databases (on the same server) implies more use of system resources, but may be slightly more
robust(i.e. one could go down but the other would still work). dblink is certainly slower than refering to a table in a
schema,but it seems to work reasonably well, as least in talking between two databases on the same server (I've not
reallytested it between servers but it would obviously be slower depending on one's network). 

If you want to enforce referential integrity then a schema is the way to go; schemas have permissions so it should be
possibleto lock out unwanted users almost as effectively as if there were two databases. 

I have a database that uses schemas fairly heavily (in a postgres 7.4 installation) and I have had to edit my restore
scripts-- partly because the script is confused by all of the ALTER statements I needed, and partly to get schema
restoredin the corect order. Version 8 may be better but might still need some manual editing of the restore script. I
usedschemas to simplify scripts and maitain references. 

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    KÖPFERL Robert [mailto:robert.koepferl@sonorys.at]
Sent:    Wed 2/23/2005 6:33 AM
To:    pgsql-sql@postgresql.org
Cc:
Subject:    [SQL] dblink versus schemas. What to use in this case?
Hi all,

I have got two database schemas. They're rather independend. Thus they are
in two databases. However there is one function that needs access to the
other database.

As I found out, I have two choices:
*Using schemas and put the schemas tighter together (via interdependencies).
Dumping distinct schemas is possible, however quistionable if a restore will
work with the dependencies.
*Using dblink. Dblink gives me a loose binding of the two databases. Some of
us care about the 'contrib' status of dblink. Speed (connect, query,
disconnect may sloww down) and it's deadlock resolv capabilities. However
the deadlock thingy is just a question of interest.


What should I do?
....to make one fcn of one DB access another DB's tables/fcns....



Thanks

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match

!DSPAM:421c94cc83679760939685!