Re: Using tables in other PostGreSQL database

Let me further explain things. From the perspective of PostgreSQL,
all transactions occur within a single instance of a connection to a
single database.

When that connection is lost, any transactions roll back.
Transactional integrity is therefore only guaranteed during the
duration of a connection to a database. PostgreSQL has no semantics
to enforce any kind of transaction that spans databases or connections
at this time.

So, the ultimate division of your data is to split it up into separate
databases. By doing so you are saying that the data contained in the
new database in no way relates, at least in real time, with the data
in any other database (unless you've got some kind of synchronous
replication going I guess.)

It's why in PostgreSQL, nearly every statement can be issued inside a
transaction as long as it occurs within a database. Hence create
database and create tablespace don't exist inside a transaction, like
most statements.

Now, if by magic, a method to do cross db queries showed up, what
would it have to do to "do the right thing"? Would it need to ensure
transactional integrity? That would make it non-trivial. And if it
didn't ensure such a thing, then it would be a very real gotcha for a
user.

Using schemas ensures transactional integrity now. No need for any
addons or upgrades. No gotchas, no messed up data because half a
transaction got committed and half didn't.

So, the problem here, to me, is that the bosses making the decisions
don't get the fact that by meddling in the DBAs job to tell him how to
do something, they are hamstringing him and their databases for life,
and eventually the load on the DB will break something and they'll
want someone to blame. At that point, tell them to look in the
mirror. Or explain to them now why it's a bad idea and make it stick.