Cross database foreign key workaround?

[David Busby]

List,
What are the recommended work arounds for cross database foreign keys?
As I understand it transactions are not atomic with the TCL method. I have
a situation that requires a master database and then a separate database for
every subscriber. Subscribers need read/write to both databases. I chose
separate databases because there are 20+ large tables that would require
uid/gid columns, indexes and where conditions to separate information by
subscriber. I thought that was too much overhead. Should I just use my
application to make changes and ensure references that need to take place
across databases? Or should I add a uid/gid to all necessary tables, create
indexes and update all necessary where clauses? Ideas?

/B


---------------------------(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

[Joe Conway]

Post by David Busby
List,
What are the recommended work arounds for cross database foreign keys?
As I understand it transactions are not atomic with the TCL method. I have
a situation that requires a master database and then a separate database for
every subscriber. Subscribers need read/write to both databases. I chose
separate databases because there are 20+ large tables that would require
uid/gid columns, indexes and where conditions to separate information by
subscriber. I thought that was too much overhead. Should I just use my
application to make changes and ensure references that need to take place
across databases? Or should I add a uid/gid to all necessary tables, create
indexes and update all necessary where clauses? Ideas?

What about using schemas?

Joe




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Martin Marques]

Post by David Busby
List,
What are the recommended work arounds for cross database foreign keys?
As I understand it transactions are not atomic with the TCL method. I have
a situation that requires a master database and then a separate database
for every subscriber. Subscribers need read/write to both databases. I
chose separate databases because there are 20+ large tables that would
require uid/gid columns, indexes and where conditions to separate
information by subscriber. I thought that was too much overhead. Should I
just use my application to make changes and ensure references that need to
take place across databases? Or should I add a uid/gid to all necessary
tables, create indexes and update all necessary where clauses? Ideas?

Use schemas. That's what they are for! (at least thats the main reason we are
using them intensivelly).
--
19:28:01 up 6 days, 5:05, 2 users, load average: 0.36, 0.40, 0.36
-----------------------------------------------------------------
Martín Marqués | ***@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Nagib Abi Fadel]

You can try to use dblink (function returning results
from a remote database)and create some triggers with
it in order to make remote referential integrity.

Or if there's a lot of links between the tables in the
2 databases it may be better to use one database.

Post by David Busby
List,
What are the recommended work arounds for cross
database foreign keys?
As I understand it transactions are not atomic with
the TCL method. I have
a situation that requires a master database and then
a separate database for
every subscriber. Subscribers need read/write to
both databases. I chose
separate databases because there are 20+ large
tables that would require
uid/gid columns, indexes and where conditions to
separate information by
subscriber. I thought that was too much overhead.
Should I just use my
application to make changes and ensure references
that need to take place
across databases? Or should I add a uid/gid to all
necessary tables, create
indexes and update all necessary where clauses?
Ideas?
/B
---------------------------(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

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings