pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFwd: Database.Schema.Table
6+ messages / 4 participants
[nested] [flat]
* Fwd: Database.Schema.Table
@ 2019-07-20 22:24 jmz <mxav1111@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: jmz @ 2019-07-20 22:24 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database.Schema.Table
@ 2019-07-21 02:35 David G. Johnston <david.g.johnston@gmail.com>
parent: jmz <mxav1111@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2019-07-21 02:35 UTC (permalink / raw)
To: jmz <mxav1111@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database.Schema.Table
@ 2019-07-21 04:54 jmz <mxav1111@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: jmz @ 2019-07-21 04:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database.Schema.Table
@ 2019-07-21 05:50 Avin Kavish <avinkavish@gmail.com>
parent: jmz <mxav1111@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Avin Kavish @ 2019-07-21 05:50 UTC (permalink / raw)
To: jmz <mxav1111@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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.
>>
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database.Schema.Table
@ 2019-07-21 14:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Avin Kavish <avinkavish@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2019-07-21 14:36 UTC (permalink / raw)
To: Avin Kavish <avinkavish@gmail.com>; +Cc: jmz <mxav1111@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database.Schema.Table
@ 2019-07-23 18:35 jmz <mxav1111@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: jmz @ 2019-07-23 18:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Avin Kavish <avinkavish@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-07-23 18:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-20 22:24 Fwd: Database.Schema.Table jmz <mxav1111@gmail.com>
2019-07-21 02:35 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-21 04:54 ` jmz <mxav1111@gmail.com>
2019-07-21 05:50 ` Avin Kavish <avinkavish@gmail.com>
2019-07-21 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-07-23 18:35 ` jmz <mxav1111@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox