Re: Using tables in other PostGreSQL database

On Sat, Mar 29, 2008 at 4:53 PM, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> Scott Marlowe wrote:
>
> > How do you do cross db access within a transaction?
> > You can't, and it's not likely that any code will be put in place to
> do this.
>
> But wasn't such code announced in 8.1? From
> http://www.postgresql.org/about/news.422 :
> "Two-Phase Commit (2PC): Long in demand for WAN applications and
> heterogeneous data centers using PostgreSQL, this feature allows
> ACID-compliant transactions across widely separated servers."

Yeah, I mentioned 2pc in a later post. No, there are no semantics in
postgresql that let it be represented by a simple cross db reference.

Also, 2PC is subject to unresolved transactions (or something like that).

> > Even in Oracle you don't have cross db queries.
>
> On the contrary you do. You can refer to objects in another database by
> OBJECT_NAME(at)DBLINK_NAME, very useful to mix local and remote data in no
> time. DBLINK_NAME represents a connection to another database.
> What you don't have is OTHERDB.OBJECT_NAME to refer to a different
> database within the same instance, because there is only one database
> in an Oracle instance.

What you are talking about are cross schema references, not cross db.
Oracle instances can have > 1 database, it's just not that common. I
know this because we had an internal instance at the last company I
worked at that had 2 databases in it, each with their own schemas. Or
maybe they somehow had two instances of oracle running on the same
box. I'm no oracle expert, I'm just reporting what I saw with my own
eye.

> > You use schemas there.
>
> Also there are no real schemas in Oracle, or they're strictly tied from
> db users, and that's again quite different from how it's done in PG.

Just because schemas in oracle are tied to user accounts doesn't make
them less schema-ish than postgresql's schemas. They're still
schemas. Create a user get a schema. Issue "alter session set
currnet_schema=abc and you change schemas, just like set search_path
does for postgresql, but with only one schema instead of multiples to
search through.

But my point remains. You don't use multiple dbs in oracle to do
this, and you shouldn't in PostgreSQL either.