Thread: Fwd: Database.Schema.Table
This is about cross database integration.
Does any one know if database.schema.table syntax would make it to Release 12?
Currently I get error "feature not implemented". Fdw is a very poor implementation of connecting two databases when compared with oracle database links and this feature of database.schema.object and for that matter
hostname.database.schema.table could save lots of time for folks having to manage cross-db integration.
TIA
Max.
On Saturday, July 20, 2019, jmz <mxav1111@gmail.com> wrote:
Does any one know if database.schema.table syntax would make it to Release 12?
No. The lack of an entry in the beta release notes (or relevant documentation) and probably the same error when attempting it on version 12 makes this clear.
There is no pending patch or even, IIRC, recent discussion on making this potentially a thing. My understanding is the current architecture of the product makes implementing such a capability prohibitively expensive in terms of both time and risk.
David J.
Thank you for the clarification David. I really thought this would be easier to implement since rest of the architecture (within DB) doesn't need any (or little) change.
Max.
On Sat, Jul 20, 2019, 19:35 David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, July 20, 2019, jmz <mxav1111@gmail.com> wrote:Does any one know if database.schema.table syntax would make it to Release 12?No. The lack of an entry in the beta release notes (or relevant documentation) and probably the same error when attempting it on version 12 makes this clear.There is no pending patch or even, IIRC, recent discussion on making this potentially a thing. My understanding is the current architecture of the product makes implementing such a capability prohibitively expensive in terms of both time and risk.David J.
If you are referring to cross database queries using that syntax wouldn't that require reworking the transaction system to work across db's to guarantee ACIDity?
On Sun, Jul 21, 2019 at 10:24 AM jmz <mxav1111@gmail.com> wrote:
Thank you for the clarification David. I really thought this would be easier to implement since rest of the architecture (within DB) doesn't need any (or little) change.Max.On Sat, Jul 20, 2019, 19:35 David G. Johnston <david.g.johnston@gmail.com> wrote:On Saturday, July 20, 2019, jmz <mxav1111@gmail.com> wrote:Does any one know if database.schema.table syntax would make it to Release 12?No. The lack of an entry in the beta release notes (or relevant documentation) and probably the same error when attempting it on version 12 makes this clear.There is no pending patch or even, IIRC, recent discussion on making this potentially a thing. My understanding is the current architecture of the product makes implementing such a capability prohibitively expensive in terms of both time and risk.David J.
Avin Kavish <avinkavish@gmail.com> writes:
> On Sun, Jul 21, 2019 at 10:24 AM jmz <mxav1111@gmail.com> wrote:
>> Thank you for the clarification David. I really thought this would be
>> easier to implement since rest of the architecture (within DB) doesn't need
>> any (or little) change.
> If you are referring to cross database queries using that syntax wouldn't
> that require reworking the transaction system to work across db's to
> guarantee ACIDity?
The transaction system wouldn't particularly care, since XIDs are
cluster-wide already. However, there is no provision at all for
cross-database catalog access, and that's where the problems would
start.
As an example, there is an assumption throughout the backend that
table names can be resolved into OIDs at parse time and the OID
is a sufficiently unique identifier from then on. But an OID is
a lookup key for only one database's pg_class catalog. There's
no guarantee that table OIDs are unique across databases, much less
any efficient way to find the referent of an OID that perhaps points
into some other database's pg_class.
Likewise for functions. Likewise for operators, and most other
sorts of named entities. The only things for which OIDs are
effectively cluster-wide are the object types tracked in shared
catalogs (roles, tablespaces, databases).
You could imagine, perhaps, converting *all* the catalogs to be
shared across databases, but that's not going to happen for a
number of good reasons, such as performance, reliability, and
security.
In short, the OP's notion that this would be a minor change is
utterly uninformed. I'd put the odds that it ever happens at
epsilon.
regards, tom lane
Thanks Tom. Explanation is very insightful.
Cursory reference to OID info explains that it is 4 byte unsigned integer. Not sure if size can be increased or else
Can we change OID creation routine to take hostname-(cloud/domain) into consideration? Yes only newly created database can take full advantage of this change but it is reasonable limitation if it is not too much of a dev challenge.
Best,
Max
On Sun, Jul 21, 2019, 07:36 Tom Lane <tgl@sss.pgh.pa.us> wrote:
Avin Kavish <avinkavish@gmail.com> writes:
> On Sun, Jul 21, 2019 at 10:24 AM jmz <mxav1111@gmail.com> wrote:
>> Thank you for the clarification David. I really thought this would be
>> easier to implement since rest of the architecture (within DB) doesn't need
>> any (or little) change.
> If you are referring to cross database queries using that syntax wouldn't
> that require reworking the transaction system to work across db's to
> guarantee ACIDity?
The transaction system wouldn't particularly care, since XIDs are
cluster-wide already. However, there is no provision at all for
cross-database catalog access, and that's where the problems would
start.
As an example, there is an assumption throughout the backend that
table names can be resolved into OIDs at parse time and the OID
is a sufficiently unique identifier from then on. But an OID is
a lookup key for only one database's pg_class catalog. There's
no guarantee that table OIDs are unique across databases, much less
any efficient way to find the referent of an OID that perhaps points
into some other database's pg_class.
Likewise for functions. Likewise for operators, and most other
sorts of named entities. The only things for which OIDs are
effectively cluster-wide are the object types tracked in shared
catalogs (roles, tablespaces, databases).
You could imagine, perhaps, converting *all* the catalogs to be
shared across databases, but that's not going to happen for a
number of good reasons, such as performance, reliability, and
security.
In short, the OP's notion that this would be a minor change is
utterly uninformed. I'd put the odds that it ever happens at
epsilon.
regards, tom lane